What's the workaround to copy the link for a cell when using IMPORTRANGE?

Question

I want to know what the correct way is to use IMPORTRANGE while it also gets the correct link for another spreadsheet. What ends up happening is that the link directs to the current file instead of the intended file. Maybe this description is quite vague, so here is a sample repro:

1. File X has two sheets a and b, and b!A1 is set to =a!A1.
2. File Y has a sheet c, and c!A1 is set to =IMPORTRANGE(<X>, "b!A1").

The expected output is c!A1 should have a hyperlink to b!A1 in X, but the actual output is that Y tries to find for b!A1 which might be a false positive, or it ends up raising an Invalid range exception.

---

Is there a way to do this, either the correct way or at least a workaround?

Answer 1

TL;DR:

From the question:

The expected output is c!A1 should have a hyperlink to b!A1 in X

IMPORTRANGE returns one more more values from another spreadsheet file. It does not automatically create clickable links that would point to a cell, but that can be done by importing cells that contain such links.

To create a link to a specific cell, right-click a cell and choose View more cell actions > Get link to this cell, and paste the link to a cell as is or in a HYPERLINK formula. Once you have a clickable link in a cell, you can import that cell to another spreadsheet, and the link will be clickable in the other spreadsheet as well.

The first parameter to HYPERLINK is a text string, so you can also craft a URL programmatically through text string manipulation. To do this, add a URI fragment to the spreadsheet edit URL. The fragment looks like #gid=580942856&range=A1. The gid identifies a tab in the spreadsheet. You can get it from the web browser address bar when the pertinent tab is active.

Overview

The use of IMPORTRANGE is straightforward; nothing is obscure here, but there might be wrong expectations and misunderstandings that are understandable as the Google Sheets help articles are very brief, and most of the best resources from a didactic point of view are hard to find and to remember for those that not use this function frequently.

First

IMPORTRANGE is a spreadsheet function that returns an array of values. The array might be 1 x 1 (single cell), or n x m (a grid having multiple rows and columns).

Second

Cell formats such as text colors, fill colors and borders cannot be copied with IMPORTRANGE. Some formats do get copied, such as number and date formats and clickable hyperlinks. For example, if a cell contains a date with specific formatting applied through cell number formatting, IMPORTRANGE will copy the date in that format. Note that the format will get lost if you wrap the IMPORTRANGE function in if(), iferror(), vlookup(), query() or some such function, and that any format applied to the cell in the target spreadsheet will override the format that was imported.

Third

IMPORTRANGE requires two parameters.

• The first parameter is the source spreadsheet URL or key. The URL parameters are ignored.
• The second parameter is the range address. If the sheet name is omitted, IMPORTRANGE will get the data from the default sheet. The default sheet is the first sheet from left to right as they are displayed in the spreadsheet as tabs, or from top to bottom as they are displayed in the sheets dropdown menu. The first sheet might be hidden.

Fourth

IMPORTRANGE requires authorization to access the source spreadsheet. A user grants authorization. This user should have access to the source sheet. If the user loses access to the source sheet, IMPORTRANGE cannot retrieve the data from the source spreadsheet.

Fitfh

Most functions are calculated on the client side. This means that the device of the user does the spreadsheet formula calculations at the keyboard app. This implies that if you are using spreadsheet chains, the user at the keyboard might not get the expected results if they have not opened all the spreadsheets in the chain.

Epilogue

You have to do your best to understand how Google Sheets works. Remember that it's a service having two major components, a server, and a client application. This provides advantages but also restrictions. Google Sheets has three client apps, one for each of the three supported platforms: Web, Android, and iPhone and iPad.

The most complete is the app for the Web. The Android and iPhone and iPad apps might be good enough for specific tasks but will hardly be found helpful by power users.

Learn spreadsheet terminology. Learn about data structures. Learn about the basics of how web applications work (this includes learning about web browsers, including web browser developer tools).

Learn the spreadsheet data types, number format, settings, decimal and parameter separators.

Design your solution based on Google Sheets spreadsheets as simply as possible. Avoid making IMPORTRANGE chains across spreadsheets, as that will make the spreadsheet unreliable for serious business.

Addendum

Demo

File X

Value a!A1: foo.
Formula b!A1: =a!A1.

File Y

A1 formula: =IMPORTRANGE("https://docs.google.com/spreadsheets/d/1q9_R_M8W9j8HiqONKvrnUyRzNpw_FBv17u1THmQdQhk/edit#gid=0","b!A1").

Authorization dialog message

You need to connect these spreadsheets. The first time the destination spreadsheet pulls data from a new source spreadsheet, permission is needed to be granted.

Link to cell

https://docs.google.com/spreadsheets/d/1q9_R_M8W9j8HiqONKvrnUyRzNpw_FBv17u1THmQdQhk/edit#gid=580942856&range=A1

• https : protocol.
• docs.google.com: domain.
• /spreadsheets/d/1q9_R_M8W9j8HiqONKvrnUyRzNpw_FBv17u1THmQdQhk/edit: resource path
  • 1q9_R_M8W9j8HiqONKvrnUyRzNpw_FBv17u1THmQdQhk : key / file id.
• #gid=580942856&range=A1: URI fragment.
  • gid=580942856: sheet parameter.
  • range=A1: cell/range address parameter.

References

Google Docs Editors Help

• IMPORTRANGE (function).
• Work with data > Using arrays in Google Sheets.

Answer 2

The importrange() function copies values. What it does not do is somehow automatically create hyperlinks that point somewhere. It only copies the values in the source cells. If the values happen to contain hyperlinks, they get copied fine.

=a!A1 is not a hyperlink but a formula that includes a cell reference. To import the text of that formula, put =formulatext(A1) in spreadsheet X cell b!B1. Then import "b!A1:B1" to spreadsheet Y.

To put an actual hyperlink in cell b!A1, select the cell and choose Insert > Link, or use a hyperlink() formula. Then import "b!A1" to spreadsheet Y.