ProgrammerForever
@ProgrammerForever
Учитель, автоэлектрик, программист, музыкант

Как быстрее всего получить данные из сторонних таблиц?

Добрый день. Частая задача - получать данные из других таблиц. Есть пара способов:
1) Получать данные с помощью IMPORTRANGE(url; range). Проблема в том, что если список таблиц меняется, то формулу надо переписывать.
={
IMPORTRANGE(URL1; RANGE1);
IMPORTRANGE(URL2; RANGE2);
IMPORTRANGE(URL3; RANGE3)
}

К тому же, при добавлении строки, нужно открыть доступ к данным к другой таблице
2) Использовать скрипт, наподобие такого:
/**
 * Возвращает данные из таблиц
 *
 * @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
};

Это рабоает, но не уверен, что это самое быстрое решение, к тому же - это нельзя вызвать как юзерфункцию, т.к. есть SpreadsheetApp.openByUrl()

Кто как решает эту задачу? Есть ли что-то более простое (для конечного юзера) и надёжное.
  • Вопрос задан
  • 153 просмотра
Пригласить эксперта
Ответы на вопрос 1
oshliaer
@oshliaer Куратор тега Google Sheets
Google Products Expert
К сожалению нельзя дать однозначный ответ на этот вопрос. Я бы разделил по способам применения.

Например, используя формулы мы не получаем набор данных, мы видим лишь их представление. Т.н. представление по ссылке. Мы не можем менять эти данные. Накладывает большие ограничения.

Программное получение данных является хорошей альтернативой. Но недешевой в плане эксплуатации и поддержки.

Использование стороннего сервиса. Из адекватных предпочитаю sheetgo.

Использование Google Apps Script. Там возможны варианты: SpreadsheetApp или Sheet Advanced Service. Для больших данных второй предпочтительнее, но не такой удобный как первый.

Использование Google Sheets API. Очень напоминает Sheet Advanced Service, но требует своей исполнительной среды и прочих накладных расходов.

У всех этих способов есть один большой недостаток - это все не мгновенно, потому что это сетевые приложения. К тому же требуется либо ждать события системы либо самому какие-то кнопки жмякать. Куча проблем, одним словом.

Но без худа не бывает чуда. Попробуйте какой-нибудь более-менее пригодный пример для Sheet Advanced Service или подобрать сниппет тут google-apps-script-snippets

С уважением.
Ответ написан
Комментировать
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы