Resolving '#NAME?' Error in Google Sheets When Using Custom 'GETLINK()' Function with IMPORTRANGE

Question

I have 3 similar Google source sheets connected together into a master google target sheet via import range which is then connected to a dashboard. Every morning I notice every row in Columns "Links" will says "#NAME?". When I check the 3 source sheets, I notice the Links column says "Loading..." and a few seconds later it loads the URL via appscreipt perfectly fine. In a nutshell, I need to have my source sheet actively displayed in order for my getlink() function to work in my target_sheet. Is there any workarounds? Thanks!

source_sheet example

target_sheet example

function GETLINK(input){
  const formula = SpreadsheetApp.getActiveRange().getFormula()
  const regex = formula.match(/=\w+\((.*)\)/i)[1]
  const textValues = SpreadsheetApp.getActiveSheet().getRange(regex).getRichTextValues()
  return textValues.map(function(ele){return ele[0].getLinkUrl()})
}

Answer 1

Thanks to @Tanaike, I created a refreshSheet function with a time-driven trigger.

function GETLINKS(input) {
  const cache = CacheService.getScriptCache();
  const cacheKey = 'links_cache'; // Unique key to store and retrieve cached data

  // Try to retrieve the cached links
  let cachedLinks = cache.get(cacheKey);
  if (cachedLinks) {
    return JSON.parse(cachedLinks); // Parse and return the cached links if available
  }

  // If links are not cached, fetch them from the sheet
  const formula = SpreadsheetApp.getActiveRange().getFormula();
  const regex = formula.match(/=\w+\((.*)\)/i)[1];
  const textValues = SpreadsheetApp.getActiveSheet().getRange(regex).getRichTextValues();
  let links = textValues.map(function(ele) { return ele[0].getLinkUrl(); });

  // Cache the fetched links
  cache.put(cacheKey, JSON.stringify(links), 21600); // Cache for 6 hours (21600 seconds)

  return links; // Return the fetched links
}


function refreshSheet() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("source1"); // Replace with your target sheet name
    var cell = sheet.getRange("A1"); // Choose a cell that won't affect your data
    var value = cell.getValue();
    cell.setValue(""); // Clear the cell
    SpreadsheetApp.flush(); // Apply changes
    cell.setValue(value); // Reset the cell to its original value
}