Google Sheet custom function - update cell references when the sheet is modified/the formula is copied to new cells

Question

I have a custom formula, code below, to extract the URL of cells containing hyperlinks. I need to do this for a whole bunch of cells, so I need to ensure the reference cell is correct.

However, I have to explicitly type out and manually modify the cell reference every time I use the corner drag function to expand the formula to other cells, as well as every time a row is added or deleted, etc - the reference doesn't smartly update the way it does for built-in functions.

Is there a way to update the script or otherwise induce Sheets to smartly update the cell reference?

Custom function script:

function GETLINK(input){

return SpreadsheetApp.getActiveSheet().getRange(input).getRichTextValue().getLinkUrl();

}

Starting sheet example:

A	B	C
Index	Cells with hyperlinks	Formula
1	Link Text 1	=GETLINK("C1")
2	Link Text 2

If I click and drag the corner of C2, I wanto see this output:

A	B	C
Index	Cells with hyperlinks	Formula
1	Link Text 1	=GETLINK("C1")
2	Link Text 2	=GETLINK("C2")

But instead I get this:

A	B	C
Index	Cells with hyperlinks	Formula
1	Link Text 1	=GETLINK("C1")
2	Link Text 2	=GETLINK("C1")

^ the same kind of non-updating happens if I happen to insert some rows/cells - the references just stay absolute and don't intelligently update.

Thank you!

Nikko J. Nikko J. · Accepted Answer · 2021-07-28T20:52:10

Use this instead:

=GETLINK(CELL("address", C1))

Then add this function in your script to remove the $ returned by the CELL() function:

function GETLINK(input){
  var rangeName = input.replace(/[^a-zA-Z0-9 ]/g, "");
  return SpreadsheetApp.getActiveSheet().getRange(rangeName).getRichTextValue().getLinkUrl();
}

References:

Google Sheet custom function - update cell references when the sheet is modified/the formula is copied to new cells

1 Answers