Иcпользование Sheets Api для ускорения обработки данных часть 1

The Google Sheets API lets you read, write, and format Google Sheets data with your preferred programming language, including Java, JavaScript, and Python - пишут на странице официальной документации. А что если можно использовать его вместо SpreadsheetApp - основного инструмента для работы с данными таблиц в Google apps script, для ускорения получения и записи данных.

Операции чтения и записи в таблицах самые долгие, зачастую отнимающие львиную долю времени отведённого в 6 минут работы скрипта. Конечно, бывают скрипты, которые работают с данными неправильно - читают или записывают ячейки по одной, когда можно получить данные сотен и тысяч ячеек за один вызов, обработать массив данных в памяти и вставить данные также массивом в сотни и тысячи ячеек. Порой достаточно переписать исходный код и операции которые не укладывались в 6 будут отрабатывать меньше одной минуты.

В статье речь пойдёт немного о другом - о преимуществах, которые может предложить Sheets Api, в сравнении с SpreadsheetApp. 

Для примера возьмём открытые данные Министерства культуры Российской Федерации о библиотеках. Импортируем данные в гугл таблицу. Откроем редактор скриптов. Для начала работы c Sheets Api необходимо подключить его в Сервисах, по-умолчанию он не включён.

Попробуем получить все данные листа Данные с помощью SpreadsheetApp:

function getAllValues() {
 console.time('app')
   ss.getSheetByName('Данные').getDataRange().getValues()
 console.timeEnd('app')
}

console.time(), console.timeEnd() только для того чтобы получить время выполнения скрипта.

Получим все данные листа Данные с помощью Sheets Api:

function getAllValues(){
 console.time('app')
   Sheets.Spreadsheets.Values.get(ssId,"'Данные'!A3:AZ").values
 console.timeEnd('app')
}

И здесь упрёмся в первое ограничение Sheets Api, которое не позволит нам получить все данные за один запрос, 

HttpResponseException: Response Code: 413. Message: response too large.

Нужно разбить диапазон хотя бы на две части и затем объединить данные. Объединим код в одну функцию для того чтобы сравнить время выполнения:

function getAllValues() {
 const ss=SpreadsheetApp.getActive()
 const ssId=ss.getId()
 console.time('api')
   Sheets.Spreadsheets.Values.get(ssId,"'Данные'!A3:AZ10000").values.concat(Sheets.Spreadsheets.Values.get(ssId,"'Данные'!A10001:AZ").values)  
 console.timeEnd('api')
 console.time('app')
   ss.getSheetByName('Данные').getDataRange().getValues()
 console.timeEnd('app')
}

Казалось бы приплыли, сушите вёсла: время получения данных через Sheets Api больше. Причём это не зависит от того что мы делаем два запроса к api. Даже если мы ограничим такие большие диапазоны снизу SpreadsheetApp будет выигрывать. Скорее всего природа у них одинаковая, только оптимизации чуть отличаются.

Не будем отчаиваться и попробуем получить данные одного столбца

function getOneColumnValues(){
  const ss = SpreadsheetApp.getActive()
  const ssId = ss.getId()
  console.time('api')
    Sheets.Spreadsheets.Values.get(ssId, "'Данные'!A3:A").values
  console.timeEnd('api')
  console.time('app')
    ss.getSheetByName('Данные').getRange('A3:A').getValues()
  console.timeEnd('app')
}

Запускаем и становится интереснее.

Причём если в этой же таблице мы возьмём данные из столбца H, то и время выполнения будет уже ближе друг к другу.

Рассмотрим задачу получения данных из нескольких столбцов. Нам нужно название библиотеки - столбец A, и расписание работы по дням диапазон AE3:AK. 

Самое оптимальное для SpreadsheetApp взять данные за два запроса. Предположим что оптимальнее было бы взять большой диапазон A3:AK за один раз, но тогда придётся при обработке данных указывать номера столбцов, подсчитав их перед этим, очень муторное занятие. Можно будет придумать различные варианты обработки полученного массива, но мы пойдём самым простым путём.

Самое оптимальное, казалось бы, для Sheets Api тот же путь.

  function getSeveralColumnsValues(){
  const ss = SpreadsheetApp.getActive()
  const ssId = ss.getId()
  console.time('api')
    Sheets.Spreadsheets.Values.get(ssId, "'Данные'!A3:A").values
    Sheets.Spreadsheets.Values.get(ssId, "'Данные'!AE3:AK").values
  console.timeEnd('api')
  console.time('app')
    const sheet=ss.getSheetByName('Данные')
    sheet.getRange('A3:A').getValues()
    sheet.getRange('AE3:AK').getValues()
  console.timeEnd('app')
}
  

А что если можно получать данные таблицы с разных листов, а не отдельного, за один запрос? Есть метод batchGet, который так может, здесь возьмём только один лист:

function getSeveralColumnsValuesBatch(){
  const ss = SpreadsheetApp.getActive()
  const ssId = ss.getId()
  console.time('api')
    Sheets.Spreadsheets.Values.batchGet(ssId,{ranges:["'Данные'!A3:A","'Данные'!AE3:AK"]}).valueRanges.map(r=>r.values)
  console.timeEnd('api')
  console.time('app')
    const sheet=ss.getSheetByName('Данные')
    sheet.getRange('A3:A').getValues()
    sheet.getRange('AE3:AK').getValues()
  console.timeEnd('app')
}

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

В этой статье мы сравнили два инструмента для получения данных из таблиц. В следующей части, если будет интересно, можно сравнить эти же два инструмента в таком нелёгком деле как изменение данных в таблицах.

В самом начале упомянута таблица с данными библиотек РФ. Повторюсь - https://docs.google.com/spreadsheets/d/1v7ZVjsh_bws6Pl0KBwHQSsf7cyuIvpazhn_HcLuBOtQ, сделайте себе копию, там и код весь чтобы поэкспериментировать.)

Особая благодарность каналу телеграм - Google Таблицы и чату Чат | Google Таблицы и скрипты за продвижение технологий google скриптинга и таблицестроительства.