0

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?

3
  • Welcome to Web Applications Stack Exchange. The importrange() function does what it says in the tin. It is unclear what the problem is. Are you sure you are quoting both importrange() parameters correctly? Make sure you use "b!A1" instead of b!A1. Please show the actual formulas you are using instead of paraphrasing them. Commented May 3, 2023 at 7:31
  • @doubleunary Yes, I used "b!A1". I thought it would also copy the link, but it didn't.
    – soupless
    Commented May 3, 2023 at 7:32
  • The question remains unclear. See What is the XY problem? Commented May 3, 2023 at 7:52

2 Answers 2

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

File X - Sheet b

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

File Y

File Y - Authorization dialog

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.

File Y - Authorized

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
3
  • Welcome to Web Applications Stack Exchange. Your answer is good and thorough, but you may want to edit it for accuracy. If a cell contains a date with specific formatting applied through cell number format, IMPORTRANGE will return the serial numberimportrange() will observe date formats and import them as is. Those formats can be overridden in the destination spreadsheet, but that's a different thing. importrange() will also happily import links and those links will be clickable. Commented May 3, 2023 at 21:42
  • Edited the "TL;DR:" for clarity. Improved accuracy in "Second" regarding number formats and links. Commented May 6, 2023 at 7:44
  • This is probably the nearest answer I can accept, since what I meant was that b!A1 should be a hyperlink which, when clicked, should redirect to a!A1 and when b!A1 is imported using IMPORTRANGE in Y, it should still be clickable going to X, specifically at a!A1. Although its probably too late to provide another edit to add the context.
    – soupless
    Commented May 6, 2023 at 13:27
0

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.

2
  • Sorry for the late reply. Even if a!A1 is converted to a link like https://docs.google.com/spreadsheets/d/<link>/, it still doesn't work.
    – soupless
    Commented May 3, 2023 at 9:51
  • Your importrange() formula is in ss Y tab c. Why would an http link in ss X tab a change what the formula does? Please edit your question and explain what you are trying to do. Consider sharing a publicly editable sample spreadsheet. There is a blank sheet maker that lets you share safely. Commented May 3, 2023 at 13:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.