4

My google sheet has a cell on sheet1 that contains a link to a cell on sheet2. In my function, I am able to get the link url, but cannot figure out how to get a range from the rangeId:

var link = generatorSheet.getRange(currRow, 2)
var linkUrl = link.getRichTextValue().getLinkUrl()

Logger.log(linkUrl)  // linkUrl = "rangeid=1843553975"

I've tried using getRangeByName and various other functions but keep getting a null value back, not a Range object.

Thanks in advance!

Edit: My overall goal in this is to iterate over each row in sheet1, where each cell in column 2 links to a cell in sheet2. I need to take the value from the cell in sheet2 and copy it into sheet3. In sheet1, there's a check box in column 1 of each row, so that's what I'm using to determine whether or not the linked to value will be copied. I'll have a button to kick off my function and populate sheet3, and it has to assume these links are already in place - they were done by hand prior

Nicole Coppola
  • 41
  • 2
  • I do not think it is supported by GAS. You may consider to use A1 notation (`range=B2:D4`) instead. – idfurw Nov 26 '21 at 01:29
  • What is the contents of `generatorSheet.getRange(currRow,2)`? – Cooper Nov 26 '21 at 01:55
  • This is an issue relative to the problem https://issuetracker.google.com/issues/129841094 – Mike Steelson Nov 26 '21 at 02:06
  • Is there a way to get A1 notation from the range id? Or can I retrieve the link from a different function? The contents of the cell that generatorSheet.getRange(currRow,2) is pointing to is text with a link. When I click on the cell, it says it is linked to 'sheet2!A1', but I'm not sure how to get that in the code. If I hard code that into this `SpreadsheetApp.getActiveSpreadsheet().getRangeByName('sheet2!A1');` I get a range object for the linked to cell, which is what I want. But it's getting the 'sheet2!A1' info within the function that I'm not sure how to do. – Nicole Coppola Nov 26 '21 at 05:23
  • For more context, I'm creating a tool for a friend who already has a spreadsheet with a column of links to different sheets. I'd prefer not to have to tell her to change all her links if it can be avoided. – Nicole Coppola Nov 26 '21 at 05:25
  • Unfortunately there's no way to retrieve the cell reference programmatically if you only know the `rangeid`. – Iamblichus Nov 26 '21 at 08:49
  • What is the purpose of being able to retrieve rangeid? – Nicole Coppola Nov 26 '21 at 14:38
  • It's not that there is a purpose, but thats the actual link. `#rangeid=1843553975` The web browser uses JavaScript to highlight the range. But this id isn't documented anywhere in the apps script documentation, AFAIK. – TheMaster Nov 26 '21 at 20:08
  • @MikeSteelson I don't think she has any namedranges. The `rangeid` is created when a user creates a link to the range of cells. – TheMaster Nov 28 '21 at 10:06
  • 1
    @TheMaster : Thanks for your advices. However, I only know 2 ways to link to cell (not sheet or webpage): first by formula and I can retrieve sheet and A1 notation of range, or second way by Ctrl + K to a named range and I get the rangeid. Is there a third way i don't know and give the rangeid when searching for the range? (sorry for my english, I am French) ... edit : ok I found the way to link by user interface to a range. Thx. – Mike Steelson Nov 28 '21 at 10:25

2 Answers2

2

When you create an hyperlink to a range using the user interface, you are facing this issue. I think you may have to change the way of designing the hyperlink and try to define it by the formula

=hyperlink("#gid=123456789&range=A2","go to ...")

and then you will retrieve the range by

Logger.log(linkUrl.match(/(?<=range=).*/g))
Mike Steelson
  • 14,650
  • 2
  • 5
  • 20
1

For documentation purposes,

This is a url hash fragment:

#rangeid=1843553975

The id seems to be created, when inserting a link to a range using the user interface. This is distinctly different from a namedRange When clicked, it's appended to the url in the browser, i.e.,https://docs.google.com/spreadsheets/id/edit#rangeid=1843553975. Once appended, through onpopstate javascript event, the range linked to the id is highlighted in the browser/app.

NamedRanges has a similar workflow. It also provides a rangeid=<10 digit ID>. But, it also has a name attached to it. But even in this case, the rangeid is not retrievable, though Sheets API provides a obfuscated range id.

There was a feature request made to Google, but it was made obsolete, because of lack of response on the part of the requestor:

You may create a new similar issue there with a link to this answer. Once created, link the issue here.

Other related trackers:

TheMaster
  • 45,448
  • 6
  • 62
  • 85