What is the fastest way to get data from foreign tables?

Question

Google Sheets

Good afternoon. A common task is to get data from other tables. There are a couple of ways:
1) Get data using IMPORTRANGE(url; range) . The problem is that if the list of tables changes, then the formula must be rewritten.

={
IMPORTRANGE(URL1; RANGE1);
IMPORTRANGE(URL2; RANGE2);
IMPORTRANGE(URL3; RANGE3)
}

In addition, when adding a row, you need to open data access to another table
2) Use a script like this:

/**
 * Возвращает данные из таблиц
 *
 * @param {A1:A10} urls Ссылки на таблицы
 * @param {"Лист1!A:Z"} links Диапазоны
 * @param {1} columnFilter Номер столбца по которому фильтруются пустые, нулевые и т.п. строки
 * @return Возвращает данные из таблиц
 * @customfunction
 */
function massImportRange(urls, links, columnFilter){
  urls  = (Array.isArray(urls)?urls.flat(2):[urls]).map(el=>el+"");
  links = (Array.isArray(links)?links.flat(2):Array(urls.length).fill(links)).map(el=>el+"");
  urls = urls.map((el,ind,arr)=>[el,links[ind]]).filter(el=>el[0]!="");
  
  links = urls.map(el=>el[1]);
  urls = urls.map(el=>el[0]);
  
  var outData = [];
  for (var iUrl=0; iUrl<urls.length; iUrl++){
    try{
      var ss = SpreadsheetApp.openByUrl(urls[iUrl]);
      SpreadsheetApp.flush();
      var sheetName = links[iUrl].split("!")[0];
      var rangeName = links[iUrl].split("!")[1];        
      ss = SpreadsheetApp.openByUrl(urls[iUrl]).getSheetByName(sheetName);
      var inData = ss.getRange(rangeName);
      inData = inData.getValues();
      if (columnFilter){
        inData = inData.filter(row=>row[columnFilter-1]);
      };
      outData.push(...inData);
      Logger.log(Utilities.formatString("%s - outData[%d,%d]", urls[iUrl], outData.length, outData[0]&&outData[0].length?outData[0].length:0));
    }catch(err){
      Logger.log(err);
    };
  };
  
  return inData
};

This works, but I'm not sure if it's the fastest solution, and besides, it can't be called as a user function, because there is SpreadsheetApp.openByUrl()

Who solves this problem? Is there something simpler (for the end user) and more reliable.

Answer 1

Unfortunately, no definite answer can be given to this question. I would categorize them according to how they are used.
For example, using formulas, we do not get a data set, we only see their representation. so-called. link presentation. We cannot change this data. Imposes great restrictions.
Programmatic data acquisition is a good alternative. But not cheap in terms of operation and support.
Use of a third party service. Of the adequate ones, I prefer sheetgo .
Using Google Apps Script. There are possible options: SpreadsheetApp or Sheet Advanced Service. For big data, the second is preferable, but not as convenient as the first.
Using the Google Sheets API. It is very similar to Sheet Advanced Service, but requires its own runtime environment and other overheads.
All these methods have one big drawback - it's not all instant, because these are network applications. In addition, it is required either to wait for the system events or to press some buttons yourself. A bunch of problems, in a word.
But without evil there is no miracle. Try some more or less suitable example for Sheet Advanced Service or pick up a snippet here google-apps-script-snippets
Regards.