google-apps-script einfügen - So importieren Sie automatisch Daten aus hochgeladenen CSV- oder XLS-Dateien in Google Tabellen





verknüpfen mit (4)


Sie können Daten aus einer csv-Datei in Google Drive mithilfe von Google Apps Script in ein vorhandenes Google-Blatt importieren und Daten nach Bedarf ersetzen / hinzufügen.

Unten finden Sie einen Beispielcode. Es setzt Folgendes voraus: a) Sie haben einen bestimmten Ordner in Ihrem Laufwerk, 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. Siehe Kommentare im Code für weitere Details.

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

Sie können dann in Ihrem Skriptprojekt einen importData() Trigger erstellen , um die Funktion importData() regelmäßig auszuführen (z. B. jede Nacht um 1 Uhr morgens). Sie müssen lediglich die neue report.csv-Datei in den angegebenen Drive-Ordner legen und Es wird automatisch beim nächsten geplanten Lauf 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 Developers Console aktivieren (weitere Informationen finden Sie unter Aktivieren der 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 auch hier verfügbar .

Ich habe ein Legacy-Datenbanksystem (nicht webfähig) auf einem Server, der CSV- oder XLS-Berichte in einem Google Drive-Ordner generiert. Momentan öffne ich diese Dateien manuell in der Google Drive-Weboberfläche und konvertiere sie in Google Tabellen.

Ich würde das lieber automatisch machen, 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 programmgesteuert in .gsheet zu konvertieren, nachdem Sie sie in Google Drive oder über ein Windows-basiertes Skript / Dienstprogramm gespeichert haben?




(Mär 2017) Die angenommene Antwort ist nicht die beste Lösung. Es basiert auf manueller Übersetzung mit Hilfe von Apps Script und der Code ist unter Umständen nicht belastbar und muss gewartet werden. Wenn das Legacy-System CSV-Dateien automatisch generiert, sollten sie in einen anderen Ordner zur vorübergehenden Verarbeitung gehen (Importieren [Hochladen in Google Drive & Konvertieren] in Google Tabellen-Dateien).

Mein Gedanke ist, dass die Drive-API das gesamte Heavy-Lifting übernimmt. Das Google Drive API- Team hat v3 Ende 2015 veröffentlicht, und in dieser Version hat insert() Namen in create() geändert, create() den Dateivorgang besser widerzuspiegeln. Es gibt auch kein Convert-Flag mehr - Sie geben nur MIMEtypes an ... stellen Sie sich das vor!

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

Im Folgenden finden Sie eine alternative Python-Lösung für kurze Dateien ("einfacher Upload"), für die Sie die Klasse apiclient.http.MediaFileUpload nicht benötigen. In diesem Code-Schnipsel wird davon ausgegangen, dass Ihr DRIVE mit einem Mindestauthentifizierungsumfang von https://www.googleapis.com/auth/drive.file funktioniert, wenn Ihr Dienstendpunkt DRIVE 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 auf My Drive hochzuladen, würden Sie in einen oder mehrere spezifische Ordner hochladen, was bedeutet, dass Sie die ID des übergeordneten Ordners zu METADATA hinzufügen METADATA . (Siehe auch das Codebeispiel auf dieser Seite .) Schließlich gibt es keine native .gsheet "Datei" - diese Datei hat nur einen Link zum Online-Blatt, also was Sie wollen, ist was Sie wollen.

Wenn Sie Python nicht verwenden, können Sie das obige Snippet als Pseudocode verwenden, um es in Ihre Systemsprache zu portieren. Unabhängig davon, es gibt viel weniger Code zu pflegen, weil es keine CSV-Parsing gibt. Das einzige, was übrig bleibt, ist, den CSV-Datei-Temp-Ordner wegzublasen, an den Ihr Altsystem geschrieben hat.







So erhalten Sie die Anzahl der Spalten oder den Index der letzten Spalte:

var numColumns = sheet.getLastColumn()

Um die Anzahl der Zeilen oder den Index der letzten Zeile zu erhalten:

var numRows = sheet.getLastRow()

woher

var sheet = SpreadsheetApp.getActiveSheet()




google-apps-script google-spreadsheet google-drive-sdk google-spreadsheet-api