google apps script - que - Cómo importar automáticamente datos de archivos CSV o XLS cargados en Hojas de cálculo de Google

importar excel google docs

Question

Tengo un sistema de base de datos heredado (no accesible desde la web) en un servidor que genera informes CSV o XLS en una carpeta de Google Drive. Actualmente, abro manualmente esos archivos en la interfaz web de Drive y los convierto en Hojas de cálculo de Google.

Prefiero que esto sea automático para poder crear trabajos que agreguen / transformen y grafiquen los datos en otras hojas.

¿Es posible generar un archivo .gsheet nativo? ¿O hay una manera de convertir CSV o XLS a .gsheet mediante programación después de guardarlo en Google Drive en Google Apps o mediante un script / utilidad basado en Windows?

Answer 1

En caso de que alguien esté buscando, creé una utilidad para la importación automática de archivos xlsx en la hoja de cálculo de Google: xls2sheets . Uno puede hacerlo automáticamente mediante la configuración del cronjob para ./cmd/sheets-refresh , el ./cmd/sheets-refresh léame lo describe todo. Espero que sea de utilidad.

Answer 2

Puede hacer que Google Drive convierta automáticamente archivos csv a Hojas de cálculo de Google agregando

?convert=true

hasta el final de la API que está llamando.

EDITAR: Aquí está la documentación sobre los parámetros disponibles: https://developers.google.com/drive/v2/reference/files/insert

Además, mientras buscaba el enlace anterior, encontré que esta pregunta ya ha sido respondida aquí:

Cargue CSV en la hoja de cálculo de Google Drive con la API de Drive v2

Answer 3

Puede importar datos mediante programación desde un archivo csv en su Drive a una hoja de Google existente usando Google Apps Script, reemplazando / agregando datos según sea necesario.

A continuación se muestra un código de muestra. Se supone que: a) tiene una carpeta designada en su unidad donde se guarda / carga el archivo CSV; b) el archivo CSV se denomina "report.csv" y los datos en él están delimitados por comas; y c) los datos CSV se importan a una hoja de cálculo designada. Ver comentarios en código para más detalles.

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''s 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''s add
    // it to the data array.
    arrData[ arrData.length - 1 ].push( strMatchedValue );
  }

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

A continuación, puede crear un desencadenante basado en el tiempo en su proyecto de script para ejecutar la función importData() de forma regular (por ejemplo, todas las noches a la 1 AM), por lo que todo lo que tiene que hacer es colocar un nuevo archivo report.csv en la carpeta de Drive designada, y se procesará automáticamente en la próxima ejecución programada.

Si DEBE trabajar absolutamente con archivos Excel en lugar de CSV, puede usar este código a continuación. Para que funcione, debe habilitar Drive API en los Servicios avanzados de Google en su script y en la Consola de desarrolladores (consulte Cómo habilitar los Servicios avanzados para obtener más detalles).

/**
 * 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''s 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());
}

El código anterior también está disponible como una esencia aquí .

Answer 4

(Mar 2017) La respuesta aceptada no es la mejor solución. Se basa en la traducción manual usando Apps Script, y el código puede no ser resistente, lo que requiere mantenimiento. Si su sistema heredado genera automáticamente archivos CSV, es mejor que vayan a otra carpeta para el procesamiento temporal (importación [carga en Google Drive y conversión] a archivos de Hojas de cálculo de Google).

Mi pensamiento es dejar que la API de Drive haga todo el trabajo pesado. El equipo de API de Google Drive lanzó v3 a finales de 2015, y en esa versión, insert() cambió los nombres para create() para reflejar mejor la operación del archivo. Tampoco hay más bandera de conversión: solo debe especificar MIMEtypes ... ¡imagine eso!

La documentación también se ha mejorado: ahora hay una guía especial dedicada a las cargas (simple, multiparte y reanudable) que viene con código de muestra en Java, Python, PHP, C # / .NET, Ruby, JavaScript / Node.js e iOS / Obj-C que importa archivos CSV en formato de Hojas de cálculo de Google según lo deseado.

A continuación se muestra una solución alternativa de Python para archivos cortos ("carga simple") donde no necesita la clase apiclient.http.MediaFileUpload . Este fragmento asume que su código de autenticación funciona donde su punto final de servicio es DRIVE con un alcance de autenticación mínimo de 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'']))

Mejor aún, en lugar de cargar en My Drive , cargaría en una (o más) carpeta (s) específica (s), lo que significa que agregaría la (s) ID (s) de la carpeta principal a METADATA . (Consulte también el ejemplo de código en esta página ). Finalmente, no hay un "archivo" de hoja de cálculo .gs nativo: ese archivo solo tiene un enlace a la Hoja en línea, así que lo que está arriba es lo que desea hacer.

Si no usa Python, puede usar el fragmento de arriba como pseudocódigo para transferir al idioma de su sistema. De todos modos, hay mucho menos código para mantener porque no hay análisis CSV. Lo único que queda es eliminar la carpeta temporal de archivos CSV en la que escribió su sistema heredado.