I am trying to insert a hyperlink to a cell in a fashion that can be replicated using '=MATCH()" function. However, I can't seem to figure out a method to link a cell in Google sheets without using the GID.
When I right-click and "Get link to this cell" I get a URL with "#gid=1933185132" in the end. However this has no structure and I can't use it with a MATCH formula and autofill this like I normally do in Excel.
https://docs.google.com/spreadsheets/d/sheetkey/edit#gid=1933185132
However if this is has a cell reference like so
https://docs.google.com/spreadsheets/d/sheetkey/edit#Sheet1!C12
I can easily recreate it for the MATCH function.
Question: Is there an alternate way to link cell like I have shown above? If not Can I use a formula to extract the GID of "Sheet1!C12"?
I have searched the google forums and stack overflow to the best of my extent and the only solutions I saw seemed to use scripts with "var sheet" something which I cant make sense of having 0 knowledge of coding.
It should be a very straightforward thing to do, but I am not able to find a way out. Any insight into the issue is appreciated. Thank you very much.
Another way of inserting hyperlinks in Google Sheets is to use the HYPERLINK formula. URL – this is the URL that you want to insert as the hyperlink. You need to specify the full URL and need to enclose it within double quotes. LINK_LABEL – this is the text that you would see in the cell.
To create a hyperlink in Google Sheets, start by highlighting the text you want to turn into a link. Then, click on the "HYPERLINK" button in the toolbar and enter the web address you want to link to. When you're done, click "OK" and the text will turn into a link.
Creating a Dynamic Hyperlink URL in Google SheetsGo to Sheet2 and right-click on 'Sheet2! A1' and select “Get link to this cell”. When you paste the copied link in any blank cell, you will get a URL like this.
You can create a link like this:
=hyperlink("#gid=1166414895range=A1", "link to A1")
Each tab has a unique key, called gid, you'll find it in the link:
#gid
will never change. Tab name may be changed and the formula will break, using gid
is safer.A1
is a part you need to find using match
, address
functions to get dynamic links.I could not find a documentation on this topic, and could not find a method using tab names.
Depending on your browser, your clipboard will now contain either the full URL:
https://docs.google.com/spreadsheets/d/xxxxxxxxxx/edit#rangeid=nnnnnnnnnn
Or the clipboard will have just the range ID fragment:
#rangeid=nnnnnnnnnn
If you have only the fragment, you'll need to append it to the URL of the document to create a complete URL for the range.
There may be some other, simpler way to get the range ID, but I've not noticed one yet. See related question, answer to a similar question.
PS: After you've copied the URL for the named range, you may delete the link that was created by following the steps above.
Use in a formula.
Simple range:
=HYPERLINK(getLinkByRange("Sheet1","A1"), "Link to A1")
Named range:
=HYPERLINK(getLinkByNamedRange("NamedRange"), "Link to named range")
The code, insert into the script editor (Tools > Script Editor):
function getLinkByRange(sheetName, rangeA1, fileId)
{
// file + sheet
var file = getDafaultFile_(fileId);
var sheet = file.getSheetByName(sheetName);
return getCombinedLink_(rangeA1, sheet.getSheetId(), fileId, file)
}
function getLinkByNamedRange(name, fileId)
{
// file + range + sheet
var file = getDafaultFile_(fileId);
var range = file.getRangeByName(name);
var sheet = range.getSheet();
return getCombinedLink_(range.getA1Notation(), sheet.getSheetId(), fileId, file)
}
function getDafaultFile_(fileId)
{
// get file
var file;
if (fileId) { file = SpreadsheetApp.openById(fileId); }
else file = SpreadsheetApp.getActive();
return file;
}
function getCombinedLink_(rangeA1, sheetId, fileId, file)
{
var externalPart = '';
if (fileId) { externalPart = file.getUrl(); }
return externalPart + '#gid=' + sheetId + 'range=' + rangeA1;
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With