Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to insert hyperlink to a cell in Google sheet using formula? [duplicate]

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.

like image 701
UK97 Avatar asked Jan 06 '18 10:01

UK97


People also ask

Can you add a link to a cell with a formula in Google Sheets?

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.

How do you hyperlink in Google Sheets formula?

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.

How do I create a dynamic link in Google Sheets?

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.


1 Answers

Range

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:

enter image description here

  • #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.

Named Range

  1. Define a named range in the document.
  2. In a cell select "Insert link".
  3. In the link dialog box, select "Named ranges in this spreadsheet."
  4. Select the name of the range created in step 1.
  5. Click the "Apply" button.
  6. Move mouse pointer over the new link. A pop-up containing a link like "#rangeid=nnnnnnnnnn" will appear.
  7. Right click on the link in the pop-up to open your browser's contextual menu.
  8. Select the "Copy Link Address" or just "Copy" function.

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.

Custom functions

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;   
}
like image 97
Max Makhrov Avatar answered Sep 17 '22 21:09

Max Makhrov