Иcпользование Sheets Api для ускорения обработки данных часть 2, с плюшками Wildberries.

 Часть 1

В первой части было сравнено два инструмента для получения данных из таблиц Google: SpreadsheetApp и Sheets Api. Сравнение было бы неполным если не рассмотреть вставку данных в таблицы. 

А для того чтобы  что-то вставить в таблицы, необходимо это что-то получить в скрипт. Возьмём что-нибудь из маркетплейсов, пусть на этот раз это будет Wildberries. У каждого такого магазина существуют пункты выдачи заказов - ПВЗ. На текущий момент их 21 229. 

Очень хорошо что, немного покопавшись на сайте, можно получить полный список ПВЗ с id и координатами.

function getWbPickups() {
  const url = 'https://www.wildberries.ru/webapi/spa/modules/pickups'
  const result = JSON.parse(UrlFetchApp.fetch(url, { contentType: 'application/json', headers: { 'x-requested-with': 'XMLHttpRequest' } }).getContentText())
    .value.pickups
  console.log(result)
  return result
}

Не отходя от кассы натравим эту информацию на SpreadsheetApp и Sheets Api. Таблица с кодом

function putValues(){
  let result=getWbPickups().map(pk=>{return [pk.id,pk.coordinates[0],pk.coordinates[1]]})
  console.time('app')
  SpreadsheetApp.getActive().getSheetByName('ПВЗ').getRange(2,1,result.length,result[0].length).setValues(result)
  console.timeEnd('app')
  console.time('api')
  Sheets.Spreadsheets.Values.update({values:result},SpreadsheetApp.getActive().getId(),"'ПВЗ'!A2:C",{valueInputOption:'USER_ENTERED'})
  console.timeEnd('api')
}

Маловато информации чтобы делать хоть какие-то выводы. Покопавшись на сайте дополнительно, можно найти этой информации о ПВЗ гораздо больше.

function getWBPickupsInfo() {
  const result = getWbPickups().map(pw => { return pw.id })
  const url2 = 'https://www.wildberries.ru/webapi/poo/byids'
  const result2 = JSON.parse(UrlFetchApp.fetch(url2, { method: 'POST', contentType: 'application/json', payload: JSON.stringify(result) }))
  console.log(result2)
  return result2.value
}

Пробуем обработать информацию и запустить тестирование на следующем листе:

function putValuesInfo(){
  let result=getWbPickupsInfo()
  let resultToPut=[]
  for (let i in result){  resultToPut.push([i,result[i].address,result[i].wayInfo.replace(/\n/g,''),result[i].workTime,result[i].rate,'https://images.wbstatic.net/PickupOffice/'+result[i].imageLocationPath+'_Photo0.jpg'])
  }
  console.time('app')
  SpreadsheetApp.getActive().getSheetByName('ПВЗ ИНФО').getRange(2,1,resultToPut.length,resultToPut[0].length).setValues(resultToPut)
  console.timeEnd('app')
  console.time('api')
  Sheets.Spreadsheets.Values.update({values:resultToPut},SpreadsheetApp.getActive().getId(),"'ПВЗ ИНФО'!A2:Z",{valueInputOption:'USER_ENTERED'})
  console.timeEnd('api')
}

Не ахти какая большая разница. Но если мы хотим обновлять не один лист, а сразу 2. В SpreadsheetApp для этого нужно два раза вызывать setValues. В Sheets api существует метод, подобный batchGet из первой части, позволяющий выполнить подобное за один запрос - batchUpdate. Модернизируем весь предыдущий код, чтобы можно было воспользоваться одним запросом в Sheets api:

function putValuesInfoBatch(){
  let ids=[]
  let idsToPut=[]
  getWbPickups().forEach(p=>{
    ids.push(p.id)
    idsToPut.push([p.id,p.coordinates[0],p.coordinates[1]])
  })
  const result=getWbPickupsInfo(ids)
  const resultToPut=[]
  for (let i in result){
    resultToPut.push([i,result[i].address,result[i].wayInfo.replace(/\n/g,''),result[i].workTime,result[i].rate,'https://images.wbstatic.net/PickupOffice/'+result[i].imageLocationPath+'_Photo0.jpg'])
  }
  console.time('app')
  const ss=SpreadsheetApp.getActive()
  ss.getSheetByName('ПВЗ').getRange(2,1,idsToPut.length,idsToPut[0].length).setValues(idsToPut)
  ss.getSheetByName('ПВЗ ИНФО').getRange(2,1,resultToPut.length,resultToPut[0].length).setValues(resultToPut)
  console.timeEnd('app')
  console.time('api')
  Sheets.Spreadsheets.Values.batchUpdate({
    'valueInputOption':'USER_ENTERED',
    'data':[
      {range:"'ПВЗ'!A2:C",values:idsToPut},
      {range:"'ПВЗ ИНФО'!A2:Z",values:resultToPut}
      ]},
      SpreadsheetApp.getActive().getId())
  console.timeEnd('api')
}

Ускорение есть, но всегда нужно помнить о нагруженности самой таблицы формулами, различными действиями проводимыми в таблице. Именно для того чтобы исключить влияние вставки данных с помощью SpreadsheetApp на скорость выполнения запроса Sheets Api в последней функции добавлен метод flush.

У Sheets Api свои, порой отличающиеся достаточно сильно от SpreadsheetApp, методы работы с информацией. Как работать с этими методами и различными параметрами очень хорошо описано в официальной документации. Расписывать их нет большого смысла - основываясь на примерах всегда можно попробовать самому что-нибудь изменить, запустить, увидеть результат.

Таблица, в которой проводились тесты - https://docs.google.com/spreadsheets/d/1Yc8BZAJB7s9eYKHP3ZZaCmKZ7zoT_oGfuKeh9enmWU4/edit?usp=sharing

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