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
CodePudding user response：
When you use an hyperlink to named range, 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))
CodePudding user response：
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: