title: Google Sheet custom function - update cell references when the sheet is modified/the formula is copied to new cells
body: <p>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.</p>
  <p>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.</p>
  <p>Is there a way to update the script or otherwise induce Sheets to smartly update the cell reference?</p>
  <p><strong>Custom function script:</strong></p>
  <pre><code>function GETLINK(input){
  
  return SpreadsheetApp.getActiveSheet().getRange(input).getRichTextValue().getLinkUrl();
  
  }
  </code></pre>
  <p><strong>Starting sheet example:</strong></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>A</th>
  <th>B</th>
  <th>C</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Index</td>
  <td>Cells with hyperlinks</td>
  <td>Formula</td>
  </tr>
  <tr>
  <td>1</td>
  <td>Link Text 1</td>
  <td>=GETLINK(&quot;C1&quot;)</td>
  </tr>
  <tr>
  <td>2</td>
  <td>Link Text 2</td>
  <td></td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>If I click and drag the corner of C2, I wanto see this output:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>A</th>
  <th>B</th>
  <th>C</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Index</td>
  <td>Cells with hyperlinks</td>
  <td>Formula</td>
  </tr>
  <tr>
  <td>1</td>
  <td>Link Text 1</td>
  <td>=GETLINK(&quot;C1&quot;)</td>
  </tr>
  <tr>
  <td>2</td>
  <td>Link Text 2</td>
  <td>=GETLINK(&quot;<strong>C2</strong>&quot;)</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>But instead I get this:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>A</th>
  <th>B</th>
  <th>C</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Index</td>
  <td>Cells with hyperlinks</td>
  <td>Formula</td>
  </tr>
  <tr>
  <td>1</td>
  <td>Link Text 1</td>
  <td>=GETLINK(&quot;C1&quot;)</td>
  </tr>
  <tr>
  <td>2</td>
  <td>Link Text 2</td>
  <td>=GETLINK(&quot;<strong>C1</strong>&quot;)</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>^ 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.</p>
  <p>Thank you!</p>