wake-up-neo.net

So importieren Sie automatisch Daten aus hochgeladenen CSV- oder XLS-Dateien in Google Sheets

Ich habe ein älteres Datenbanksystem (nicht über das Internet zugänglich) auf einem Server, das CSV- oder XLS-Berichte in einem Google Drive-Ordner generiert. Derzeit öffne ich diese Dateien manuell in der Drive-Weboberfläche und konvertiere sie in Google Sheets.

Ich würde es vorziehen, wenn dies automatisch erfolgt, damit ich Jobs erstellen kann, die die Daten in anderen Blättern anhängen/transformieren und grafisch darstellen.

Ist es möglich, eine native .gsheet-Datei auszugeben? Oder gibt es eine Möglichkeit, CSV oder XLS nach dem Speichern in Google Drive in Google Apps oder über ein Windows-basiertes Skript/Hilfsprogramm programmgesteuert in .gsheet zu konvertieren?

32

Sie können mithilfe von Google Apps Script programmgesteuert Daten aus einer CSV-Datei in Ihr Laufwerk in ein vorhandenes Google Sheet importieren und die Daten nach Bedarf ersetzen/anfügen.

Unten ist ein Beispielcode. Es wird vorausgesetzt, dass: a) Sie einen bestimmten Ordner auf Ihrem Laufwerk haben, in dem die CSV-Datei gespeichert/hochgeladen wird; b) die CSV-Datei heißt "report.csv" und die darin enthaltenen Daten sind durch Kommas getrennt; und c) die CSV-Daten werden in eine bestimmte Tabelle importiert. Weitere Details finden Sie in den Kommentaren im Code.

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 );
};

Anschließend können Sie in Ihrem Skriptprojekt zeitgesteuerter Trigger erstellen, um die Funktion importData() regelmäßig auszuführen (z. B. jede Nacht um 1:00 Uhr morgens). Sie müssen also nur neue Funktionen hinzufügen Die Datei report.csv wird im angegebenen Laufwerksordner abgelegt und beim nächsten geplanten Start automatisch verarbeitet.

Wenn Sie unbedingt mit Excel-Dateien anstelle von CSV arbeiten MÜSSEN, können Sie diesen Code unten verwenden. Damit dies funktioniert, müssen Sie die Drive-API in den erweiterten Google-Diensten in Ihrem Skript und in der Entwicklerkonsole aktivieren (Details finden Sie unter So aktivieren Sie die erweiterten Dienste ).

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

Der obige Code ist hier auch als Gist verfügbar .

35
azawaza

Sie können Google Drive durch Anhängen dazu bringen, CSV-Dateien automatisch in Google Sheets zu konvertieren

?convert=true

bis zum Ende der API-URL, die Sie aufrufen.

BEARBEITEN: Hier finden Sie die Dokumentation zu den verfügbaren Parametern: https://developers.google.com/drive/v2/reference/files/insert

Während der Suche nach dem obigen Link habe ich festgestellt, dass diese Frage hier bereits beantwortet wurde:

Laden Sie CSV mit der Drive v2-API in Google Drive Spreadsheet hoch

7
Matt

(Mär 2017) Die akzeptierte Antwort ist nicht die beste Lösung. Es basiert auf manueller Übersetzung mithilfe von Apps Script. Der Code ist möglicherweise nicht stabil und muss gewartet werden. Wenn Ihr Altsystem CSV-Dateien automatisch generiert, sollten diese zur temporären Verarbeitung in einen anderen Ordner verschoben werden (Importieren von [Hochladen auf Google Drive & Konvertieren] in Google Sheets-Dateien).

Mein Gedanke ist, die Drive API das ganze Heben übernehmen zu lassen. Das Google Drive API Team Version hat Ende 2015 den Namen insert() in create() geändert um die Dateioperation besser widerzuspiegeln. Es gibt auch kein Konvertierungsflag mehr - Sie geben nur MIME-Typen an ... stellen Sie sich das vor!

Die Dokumentation wurde ebenfalls verbessert: Es gibt jetzt ein spezielles Handbuch für Uploads (einfach, mehrteilig und fortsetzbar), das mit Beispielcode in Java, Python, PHP, C # /. NET, Ruby, JavaScript geliefert wird /Node.js und iOS/Obj-C, mit denen CSV-Dateien wie gewünscht in das Google Sheets-Format importiert werden.

Unten ist eine alternative Python Lösung für kurze Dateien ("einfacher Upload"), bei der Sie nicht das apiclient.http.MediaFileUpload - Klasse. In diesem Snippet wird davon ausgegangen, dass Ihr Auth-Code funktioniert, wenn Ihr Service-Endpunkt DRIVE mit einem Mindest-Auth-Bereich von https://www.googleapis.com/auth/drive.file Ist.

# 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']))

Besser noch, als nach My Drive Hochzuladen, würden Sie in einen (oder mehrere) bestimmten Ordner hochladen, was bedeutet, dass Sie die ID (s) des übergeordneten Ordners zu METADATA hinzufügen würden. (Siehe auch das Codebeispiel auf diese Seite .) Schließlich gibt es keine native .gsheet "Datei" - diese Datei hat nur einen Link zum Online-Sheet, also ist das, was oben steht, das, was Sie tun möchten .

Wenn Sie Python nicht verwenden, können Sie das obige Snippet als Pseudocode verwenden, um auf Ihre Systemsprache zu portieren. Unabhängig davon muss viel weniger Code verwaltet werden, da keine CSV-Analyse durchgeführt wird. Das Einzige, was noch übrig bleibt, ist, den temporären Ordner der CSV-Datei wegzublasen, in den Ihr Altsystem geschrieben hat.

6
wescpy

Für den Fall, dass jemand suchen würde - Ich habe ein Hilfsprogramm für den automatischen Import von xlsx-Dateien in eine Google-Tabelle erstellt: xls2sheets . Man kann es automatisch tun, indem man den Cronjob für ./cmd/sheets-refresh, readme beschreibt alles. Hoffe das wäre von Nutzen.

0
Pukeko