Как автоматически импортировать данные из загруженного CSV или XLS файла в Google Таблицы

Ответ 1

Вы можете программно импортировать данные из файла csv на своем Диске в существующий Лист Google, используя Google Apps Script, при необходимости заменяя/добавляя данные.

Ниже приведен пример кода. Он предполагает, что: a) у вас есть назначенная папка на вашем Диске, где CSV файл сохраняется/загружается; b) файл CSV называется "report.csv", а данные в нем разделены запятой; и c) данные CSV импортируются в указанную электронную таблицу. См. Комментарии в коде для более подробной информации.

function importData() {
  var fSource = DriveApp.getFolderById(reports_folder_id); // reports_folder_id = id of folder where csv reports are saved
  var fi = fSource.getFilesByName('report.csv'); // latest report file
  var ss = SpreadsheetApp.openById(data_sheet_id); // data_sheet_id = id of spreadsheet that holds the data to be updated with new report data

  if ( fi.hasNext() ) { // proceed if "report.csv" file exists in the reports folder
    var file = fi.next();
    var csv = file.getBlob().getDataAsString();
    var csvData = CSVToArray(csv); // see below for CSVToArray function
    var newsheet = ss.insertSheet('NEWDATA'); // create a 'NEWDATA' sheet to store imported data
    // loop through csv data array and insert (append) as rows into 'NEWDATA' sheet
    for ( var i=0, lenCsv=csvData.length; i<lenCsv; i++ ) {
      newsheet.getRange(i+1, 1, 1, csvData[i].length).setValues(new Array(csvData[i]));
    }
    /*
    ** report data is now in 'NEWDATA' sheet in the spreadsheet - process it as needed,
    ** then delete 'NEWDATA' sheet using ss.deleteSheet(newsheet)
    */
    // rename the report.csv file so it is not processed on next scheduled run
    file.setName("report-"+(new Date().toString())+".csv");
  }
};


// http://www.bennadel.com/blog/1504-Ask-Ben-Parsing-CSV-Strings-With-Javascript-Exec-Regular-Expression-Command.htm
// This will parse a delimited string into an array of
// arrays. The default delimiter is the comma, but this
// can be overriden in the second argument.

function CSVToArray( strData, strDelimiter ) {
  // Check to see if the delimiter is defined. If not,
  // then default to COMMA.
  strDelimiter = (strDelimiter || ",");

  // Create a regular expression to parse the CSV values.
  var objPattern = new RegExp(
    (
      // Delimiters.
      "(\\" + strDelimiter + "|\\r?\\n|\\r|^)" +

      // Quoted fields.
      "(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" +

      // Standard fields.
      "([^\"\\" + strDelimiter + "\\r\\n]*))"
    ),
    "gi"
  );

  // Create an array to hold our data. Give the array
  // a default empty first row.
  var arrData = [[]];

  // Create an array to hold our individual pattern
  // matching groups.
  var arrMatches = null;

  // Keep looping over the regular expression matches
  // until we can no longer find a match.
  while (arrMatches = objPattern.exec( strData )){

    // Get the delimiter that was found.
    var strMatchedDelimiter = arrMatches[ 1 ];

    // Check to see if the given delimiter has a length
    // (is not the start of string) and if it matches
    // field delimiter. If id does not, then we know
    // that this delimiter is a row delimiter.
    if (
      strMatchedDelimiter.length &&
      (strMatchedDelimiter != strDelimiter)
    ){

      // Since we have reached a new row of data,
      // add an empty row to our data array.
      arrData.push( [] );

    }

    // Now that we have our delimiter out of the way,
    // let check to see which kind of value we
    // captured (quoted or unquoted).
    if (arrMatches[ 2 ]){

      // We found a quoted value. When we capture
      // this value, unescape any double quotes.
      var strMatchedValue = arrMatches[ 2 ].replace(
        new RegExp( "\"\"", "g" ),
        "\""
      );

    } else {

      // We found a non-quoted value.
      var strMatchedValue = arrMatches[ 3 ];

    }

    // Now that we have our value string, let add
    // it to the data array.
    arrData[ arrData.length - 1 ].push( strMatchedValue );
  }

  // Return the parsed data.
  return( arrData );
};

Затем вы можете создать управляемый по времени триггер в вашем проекте script для регулярной работы функции importData() (например, каждую ночь в 1:00), поэтому все, что вам нужно сделать, - поместить новый файл report.csv в указанную папку Диска, и он будет автоматически обработан в следующем запланированном прогоне.

Если вы абсолютно ДОЛЖНЫ работать с файлами Excel вместо CSV, вы можете использовать этот код ниже. Для его работы вы должны включить Drive API в Advanced Google Services в script и в консоли разработчиков (см. Как включить дополнительные службы).

/**
 * Convert Excel file to Sheets
 * @param {Blob} excelFile The Excel file blob data; Required
 * @param {String} filename File name on uploading drive; Required
 * @param {Array} arrParents Array of folder ids to put converted file in; Optional, will default to Drive root folder
 * @return {Spreadsheet} Converted Google Spreadsheet instance
 **/
function convertExcel2Sheets(excelFile, filename, arrParents) {

  var parents  = arrParents || []; // check if optional arrParents argument was provided, default to empty array if not
  if ( !parents.isArray ) parents = []; // make sure parents is an array, reset to empty array if not

  // Parameters for Drive API Simple Upload request (see https://developers.google.com/drive/web/manage-uploads#simple)
  var uploadParams = {
    method:'post',
    contentType: 'application/vnd.ms-excel', // works for both .xls and .xlsx files
    contentLength: excelFile.getBytes().length,
    headers: {'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()},
    payload: excelFile.getBytes()
  };

  // Upload file to Drive root folder and convert to Sheets
  var uploadResponse = UrlFetchApp.fetch('https://www.googleapis.com/upload/drive/v2/files/?uploadType=media&convert=true', uploadParams);

  // Parse upload&convert response data (need this to be able to get id of converted sheet)
  var fileDataResponse = JSON.parse(uploadResponse.getContentText());

  // Create payload (body) data for updating converted file name and parent folder(s)
  var payloadData = {
    title: filename, 
    parents: []
  };
  if ( parents.length ) { // Add provided parent folder(s) id(s) to payloadData, if any
    for ( var i=0; i<parents.length; i++ ) {
      try {
        var folder = DriveApp.getFolderById(parents[i]); // check that this folder id exists in drive and user can write to it
        payloadData.parents.push({id: parents[i]});
      }
      catch(e){} // fail silently if no such folder id exists in Drive
    }
  }
  // Parameters for Drive API File Update request (see https://developers.google.com/drive/v2/reference/files/update)
  var updateParams = {
    method:'put',
    headers: {'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()},
    contentType: 'application/json',
    payload: JSON.stringify(payloadData)
  };

  // Update metadata (filename and parent folder(s)) of converted sheet
  UrlFetchApp.fetch('https://www.googleapis.com/drive/v2/files/'+fileDataResponse.id, updateParams);

  return SpreadsheetApp.openById(fileDataResponse.id);
}

/**
 * Sample use of convertExcel2Sheets() for testing
 **/
 function testConvertExcel2Sheets() {
  var xlsId = "0B9**************OFE"; // ID of Excel file to convert
  var xlsFile = DriveApp.getFileById(xlsId); // File instance of Excel file
  var xlsBlob = xlsFile.getBlob(); // Blob source of Excel file for conversion
  var xlsFilename = xlsFile.getName(); // File name to give to converted file; defaults to same as source file
  var destFolders = []; // array of IDs of Drive folders to put converted file in; empty array = root folder
  var ss = convertExcel2Sheets(xlsBlob, xlsFilename, destFolders);
  Logger.log(ss.getId());
}

Вышеприведенный код также доступен здесь.

Ответ 2

Вы можете заставить Google Диск автоматически конвертировать CSV файлы в Google Таблицы, добавив

?convert=true

до конца вызываемого вами имени.

EDIT: Вот документация по доступным параметрам: https://developers.google.com/drive/v2/reference/files/insert

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

Загрузка CSV в электронную таблицу на Диске с помощью API v2.

Ответ 3

(март 2017) Принятый ответ - не лучшее решение. Он использует ручной перевод с помощью приложений Script, и этот код может быть невосприимчивым, требующим обслуживания. Если ваша устаревшая система автоматически генерирует CSV файлы, лучше всего перейти в другую папку для временной обработки (импортировать [загрузку на Google Диск и конвертирование] в файлы Google Таблиц).

Моя мысль - позволить Drive API делать все тяжелые работы. API Google Диска команда выпущена v3 в конце 2015 года, и в этом выпуске insert() изменили имена на create(), чтобы лучше отразить операцию файла. Там также больше нет флага конвертации - вы просто указываете MIMEtypes... представьте, что!

Документация также была улучшена: теперь существует специальное руководство, посвященное загрузке (простое, многократное и возобновляемое), которое поставляется с образцом код в Java, Python, PHP, С#/. NET, Ruby, JavaScript/ Node.js и iOS/Obj-C, который импортирует CSV файлы в формат Google Таблиц по желанию.

Ниже приведено одно альтернативное решение Python для коротких файлов ( "простая загрузка" ), где вам не нужно нужен класс apiclient.http.MediaFileUpload. Этот фрагмент предполагает, что ваш auth-код работает там, где конечная точка службы DRIVE с минимальным объемом полномочий https://www.googleapis.com/auth/drive.file.

# filenames & MIMEtypes
DST_FILENAME = 'inventory'
SRC_FILENAME = DST_FILENAME + '.csv'
SHT_MIMETYPE = 'application/vnd.google-apps.spreadsheet'
CSV_MIMETYPE = 'text/csv'

# Import CSV file to Google Drive as a Google Sheets file
METADATA = {'name': DST_FILENAME, 'mimeType': SHT_MIMETYPE}
rsp = DRIVE.files().create(body=METADATA, media_body=SRC_FILENAME).execute()
if rsp:
    print('Imported %r to %r (as %s)' % (SRC_FILENAME, DST_FILENAME, rsp['mimeType']))

Еще лучше, вместо того, чтобы загружать в My Drive, вы загружаете в одну (или более) конкретную папку (ы), то есть добавляете идентификаторы родительских папок в METADATA. (Также см. Образец кода на на этой странице.) Наконец, нет никакого собственного файла .gsheet "file" - этот файл имеет только ссылку на онлайн-лист, так что выше, что вы хотите сделать.

Если вы не используете Python, вы можете использовать фрагмент выше как псевдокод для подключения к вашему системному языку. Несмотря на это, гораздо меньше кода для поддержки, потому что нет синтаксического анализа CSV. Остается только удалить папку временного файла CSV, на которую написала ваша устаревшая система.

Ответ 4

В случае, если кто-то будет искать - я создал утилиту для автоматического импорта файлов xlsx в электронную таблицу Google: xls2sheets. Это можно сделать автоматически, установив cronjob для ./cmd/sheets-refresh, readme описывает все это. Надеюсь, что это будет полезно.