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 identifies a tab in the spreadsheet. You can get it from the web browser address bar when the pertinent tab is active.
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.
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).
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
query() or some such function, and that any format applied to the cell in the target spreadsheet will override the format that was imported.
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.
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.
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.
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.
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 : protocol.
/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.
Google Docs Editors Help