Загружаем данные из файлов xlsx в google spreadsheet.

Довольно часто возникает необходимость обработать файлы программы Microsoft Office Excel, но в таблицах google. Например, очень многие торгующие сайты предлагают скачивать различного рода информацию, касающуюся количества проданного, остатков, дефицитных товаров и многого-многого другого в формате xlsx. 

Формат таких файлов, хорошо описанный в документации, легко конвертируется в родной для google таблиц формат при загрузке на диск google. Но даже при отказе от конвертирования он с лёгкостью открывается в таблицах google. 

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

Проблема №1: у файла может поменяться название и скачать его по прямой ссылке не получится.

Проблема  №2: формат файла, хоть и открытый и хорошо описанный, не позволит взять из него информацию напрямую с помощью, например, формулы importxml, или XmlService скриптов. Нужно его скачивать, открывать и копировать нужную информацию в таблицу google.

Решение проблемы №1 - скрипт который выдёргивает название нужного файла со страницы

const siteUrl='http://www.gucodd.ru'
const partUrlFileFrom='/index.php/spisok-dorojnykh-kamer'

function getFileUrlPart_(){
  let content=UrlFetchApp.fetch(siteUrl+partUrlFileFrom).getContentText()
  return content.match(/a href="(\/cams\/.*?)"/)[1]
}

Проблема №2 может быть решена двумя способами:

1. Самый простой вариант - воспользовавшись встроенными возможностями google по конвертированию файлов на лету, создать временный файл google spreadsheet, забрать интересующую информацию, вставить в таблицу и затем удалить временный файл

function getXlsxToSpreadsheet1() {
  let fileBlob=UrlFetchApp.fetch(siteUrl+getFileUrlPart_()).getBlob().setContentType(MimeType.MICROSOFT_EXCEL)
   let resource = {
    title: fileBlob.getName().split('.')[0],
    mimeType: MimeType.GOOGLE_SHEETS
  };
  let newfileId = Drive.Files.insert(resource, fileBlob, {"convert":"true"}).getId();
  let data=Sheets.Spreadsheets.Values.get(newfileId,'A1:C').values
  resource={
    'valueInputOption':'USER_ENTERED',
            'data':
            {
                'range':"'Адреса'!A1:C",'values':data
            }
        
  }
  Sheets.Spreadsheets.Values.batchUpdate(resource,SpreadsheetApp.getActive().getId())
  Drive.Files.remove(newfileId)
}

2. Прочитать о формате xlsx - стандартизированном, открытым для исследователей. Понять что стандарт описывает файл xlsx как архив zip, а внутри него файлы формата xml. Прикрутить к этому XmlService, Utilities.unzip получить на выходе функцию позволяющую выполнить тоже самое что и первый способ, но работающее, к сожалению, с меньшей скоростью.

function getXlsxToSpreadsheet2(){
      let fileBlob=UrlFetchApp.fetch(siteUrl+getFileUrlPart_()).getBlob()  
      blob=fileBlob.setName('unzip.zip').setContentTypeFromExtension();
      let unzip=Utilities.unzip(blob)
      let xml={sheets:[]}
      unzip.forEach(function(zFile){
        let name=zFile.getName()
        if (name.indexOf('sharedStrings')>0){
          xml.shared=zFile
        }
        if (name.indexOf('/worksheets/sheet1.xml')>=0){
          xml.sheets.push(zFile)
        }
      })
      let arrayShared=[]
      if (xml.shared){
        let doc=XmlService.parse(xml.shared.getDataAsString().trim())
        let main = XmlService.getNamespace('http://schemas.openxmlformats.org/spreadsheetml/2006/main');
        let root = doc.getRootElement()
        let entries = root.getChildren('si',main)
        entries.forEach(ent=>arrayShared.push(ent.getChild('t',main).getText()))
      }
      let dataToReturn=[]
      xml.sheets.forEach(function(sheet){
        let doc=XmlService.parse(sheet.getDataAsString().trim())
        let root = doc.getRootElement()
        let sheetDat = root.getChildren().find(r=>r.getName()=='sheetData')
        let rows=sheetDat.getChildren()
        rows.forEach(function(r){
          let rowToReturn=[]
          let cells=r.getChildren()
          for (let i=0;i<cells.length;i++){
            if (cells[i]){
              let a1Notation=cells[i].getAttribute('r').getValue()
              let type=cells[i].getAttribute('t')
              if (type){type=type.getValue()}
              let column=colA1ToIndex_(a1Notation)-1
              let v=cells[i].getChildren()[0]
              if (!v){v=cells[i]}
              if (v.getText()&&type=='s'){
                rowToReturn[column]=arrayShared[+(v.getText())]
              }
              else {rowToReturn[column]=v.getText()}
              }
          
          }
          dataToReturn.push(rowToReturn)
          
        })
      })
     let resource={
    'valueInputOption':'USER_ENTERED',
            'data':
            {
                'range':"'Адреса'!A1:L",'values':dataToReturn
            }
        
  }
  Sheets.Spreadsheets.Values.batchUpdate(resource,SpreadsheetApp.getActive().getId())
}

function colA1ToIndex_(A1) {
  A1=A1.replace(/\d/g,'')
  let i, l, chr,
      sum = 0,
      A = "A".charCodeAt(0),
      radix = "Z".charCodeAt(0) - A + 1;
  for(i = 0, l = A1.length ; i < l ; i++) {
    chr = A1.charCodeAt(i);
    sum = sum * radix + chr - A + 1
  }
  return sum;
}

В чём полезность второго подхода - в возможности реализовать сбор данных из большого количества маленьких файлов.) В первом случае создаётся файл google spreadsheet, а обычная учётка имеет ограничение в 250 файлов, создаваемых за сутки.

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

Таблица с кодом на просмотр, копию которой можно утянуть себе - https://docs.google.com/spreadsheets/d/1BiJXhg1o4SCv_Ssg08kIrRV9S4fp0myr6CBxkyaDxdk/edit?usp=sharing