title: How to expand Range of Array in Sheets based on column height
body: <p>I have a custom formula that gets the redirect of a link. It takes an array as an argument. Its based on a Google script found here. <a href="https://i.stack.imgur.com/Yvi18.png" rel="nofollow noreferrer">https://developers.google.com/apps-script/guides/sheets/functions</a></p>
  <p>The problem is that the source list gets updated and the range changes. If the range isn't exact, it breaks. Here is the code to the custom function:</p>
  <pre><code>function DOUBLE(input) {
    return input.map(getRedirects);
  }
  
  function getRedirects(input) {
      var urlKey = input;
      var cache = CacheService.getScriptCache();
      var result = cache.get(urlKey);
  
      if (!result) {
          var params = {
              'followRedirects': false,
              'muteHttpExceptions': true
          };
          var res = UrlFetchApp.fetch(input, params);
          var finalURL = res.getHeaders()['Location'];
  
          cache.put(urlKey, finalURL, 21600);
          
          result = finalURL;
      }
  
      return result;
  }
  </code></pre>
  <p><a href="https://i.stack.imgur.com/Yvi18.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Yvi18.png" alt="code working" /></a>
  <a href="https://i.stack.imgur.com/EhHh5.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/EhHh5.png" alt="code not working" /></a></p>
  <p>And here's the spreadsheet: <a href="https://docs.google.com/spreadsheets/d/1xigMp9ADAe2f4w-MoZQPVHX2qaiCsFNTiTmDjFWxi7U/edit?usp=sharing" rel="nofollow noreferrer">working sheet</a></p>