Cloud Functions-Funktion zur Automatisierung des CSV-Datenimports in Google Tabellen

1. Einführung

In diesem Codelab erfahren Sie, wie Sie eine Cloud-Funktion schreiben, die auf das Hochladen einer CSV-Datei in Cloud Storage reagiert, deren Inhalt liest und damit eine Google-Tabelle über die Sheets API aktualisiert.

Das kann als Automatisierung eines ansonsten manuellen Schritts „Als CSV importieren“ angesehen werden. So können Sie Daten, die möglicherweise von einem anderen Team erstellt wurden, in einer Tabelle analysieren, sobald sie verfügbar sind.

So sieht die Implementierung aus :

2. Einrichtung und Anforderungen

Umgebung zum selbstbestimmten Lernen einrichten

1. Melden Sie sich in der Cloud Console an und erstellen Sie ein neues Projekt oder verwenden Sie ein vorhandenes Projekt. Wenn Sie noch kein Gmail- oder G Suite-Konto haben, müssen Sie eines erstellen.

Hinweis:Wir empfehlen Ihnen, sich die URL console.cloud.google.com zu merken. Damit können Sie mühelos auf die Cloud Console zugreifen.

Notieren Sie sich die Projekt-ID, also den projektübergreifend nur einmal vorkommenden Namen eines Google Cloud-Projekts. Der oben angegebene Name ist bereits vergeben und kann leider nicht mehr verwendet werden. Sie wird später in diesem Codelab als PROJECT_ID bezeichnet.

Hinweis:Wenn Sie ein Gmail-Konto verwenden, können Sie den Standardspeicherort auf Keine Organisation belassen. Wenn Sie ein G Suite-Konto verwenden, wählen Sie einen Standort aus, der für Ihre Organisation sinnvoll ist.

2. Als Nächstes müssen Sie die Abrechnung in der Cloud Console aktivieren, um Google Cloud-Ressourcen verwenden zu können.

Die Durchführung dieses Codelabs sollte keine oder nur geringe Kosten verursachen. Folgen Sie bitte der Anleitung im Abschnitt „Bereinigen“, in der Sie erfahren, wie Sie Ressourcen herunterfahren können, damit nach Abschluss dieser Anleitung keine Gebühren anfallen. Neue Nutzer von Google Cloud kommen für das Programm für den kostenlosen Testzeitraum mit einem Guthaben von 300$ infrage.

3. Google-Tabelle erstellen und konfigurieren und API aktivieren

Erstellen Sie zuerst ein neues Google-Tabellen-Dokument. Dieses Tabellenblatt kann einem beliebigen Nutzer gehören. Merken Sie sich nach dem Erstellen den Bezeichner. Er wird als Umgebungsvariable für die Funktion verwendet, die wir schreiben werden :

Aktivieren Sie in der GCP Console die Google Sheets API für Ihr neu erstelltes Projekt. Rufen Sie dazu den Bereich „APIs & Dienste“ und dann „API-Bibliothek“ auf :

Rufen Sie im Bereich „IAM & Verwaltung“ die Seite „Dienstkonten“ auf und notieren Sie sich die E-Mail-Adresse des App Engine-Standarddienstkontos. Muss das Format [e-mail] haben. Sie können natürlich auch ein eigenes Dienstkonto für diese Aktion erstellen.

Gewähren Sie diesem Dienstkonto schließlich einfach Bearbeitungsberechtigungen für Ihre Tabelle, indem Sie auf die Schaltfläche „Teilen“ klicken :

Mit dieser Einrichtung können wir nun unsere Cloud-Funktion schreiben und sie so konfigurieren, dass sie dieses Dienstkonto verwendet. Es kann in das Tabellendokument schreiben, das wir gerade erstellt haben.

4. Storage-Bucket erstellen

Erstellen wir den Bucket, in dem unsere Cloud-Funktion nach neuen CSV-Dateien sucht.

Klicken Sie in der Konsole im Menü auf der linken Seite auf „Storage“... :

… und erstellen Sie einen neuen Bucket mit dem Namen csv2sheet-POSTFIX (ersetzen Sie POSTFIX durch einen eindeutigen Wert), wobei alle anderen Einstellungen auf ihre Standardwerte festgelegt sind :

Hinweis:Sie können den Standort, den Standorttyp und die Speicherklasse selbst auswählen. Das sollte keine Auswirkungen auf den Rest des Codelabs haben. Im Zweifelsfall sollten Sie die Standardwerte beibehalten.

5. Cloud Functions-Funktion erstellen

Wir können jetzt eine Cloud Functions-Funktion namens csv2sheet erstellen, die bei Dateiuploads in einen bestimmten Cloud Storage-Bucket ausgelöst wird. Der Code wird in Node.js 8 mit async-Funktionen geschrieben und der Inline-Editor wird direkt in der Cloud Console verwendet :

Achten Sie darauf, dass der Trigger auf „Cloud Storage“ festgelegt ist und passen Sie den Bucket-Namen an den Namen an, den Sie im vorherigen Schritt erstellt haben.

Aktualisieren Sie auch den Einstiegspunkt für die Funktion, die wir gleich schreiben, auf csv2sheet :

Ändern Sie den Funktionsrumpf nun in :

1. Cloud Storage API und Sheets API verwenden
2. Markieren Sie die Funktion csv2sheet als async.
3. fileName aus den Cloud Storage-Ereignismetadaten abrufen und einen Namen für das neue Tabellenblatt ableiten, das wir erstellen :

const {google} = require("googleapis");
const {Storage} = require("@google-cloud/storage")

exports.csv2sheet = async (data, context) => {
  var fileName = data.name;
  // basic check that this is a *.csv file, etc...
  if (!fileName.endsWith(".csv")) {
    console.log("Not a .csv file, ignoring.");
    return;
  }
  // define name of new sheet  
  const sheetName = fileName.slice(0, -4);

  // TODO!
};

Die Verwendung von async ist hier erforderlich, um await zu verwenden, wie wir gleich sehen werden.

Beim Erstellen dieser Funktion gibt es einige wichtige Optionen (klicken Sie unten auf dem Bildschirm auf den Link „Mehr“) :

• Wählen Sie im Drop-down-Menü das oben beschriebene Dienstkonto aus.
• Definieren Sie eine Umgebungsvariable mit dem Namen SPREADSHEET_ID, die dem zuvor erstellten Tabellendokument entsprechen sollte :

Als letzten Einrichtungsschritt finden Sie hier den Inhalt von package.json mit den Cloud Storage- und Google Sheets-APIs als den beiden Abhängigkeiten, die wir verwenden werden (verwenden Sie den Inline-Editor-Tab PACKAGE.JSON der Konsole) :

{
    "name": "csv2sheet",
    "version": "0.0.42",
    "dependencies": {
        "googleapis": "^51.0.0",
        "@google-cloud/storage": "^5.0.1"
    }
}

Wenn Sie alles wie beschrieben konfiguriert haben, klicken Sie auf „Erstellen“. Nach kurzer Zeit sollte Ihre Funktion erstellt und bereitgestellt sein.

Hinweis:Der gesamte weitere Code, der in diesem Lab behandelt wird, muss der gerade erstellten Cloud-Funktion hinzugefügt werden.

6. Authentifizierung und Sheets API einrichten

Bevor wir mit dem Inline-Editor weiteren Code in unsere Cloud-Funktion schreiben, müssen wir die Erstellung einer Google Client API mit den richtigen Storage- und Sheet-Bereichen blockieren. Das ist Teil einer async-Funktion.

Klicken Sie im Funktionseditor der Konsole auf „BEARBEITEN“ und fügen Sie den folgenden Code in den Hauptteil Ihrer csv2sheet-Funktion ein :

// block on auth + getting the sheets API object
const auth = await google.auth.getClient({
  scopes: [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/devstorage.read_only"
  ]
});

Daraus können wir einen Sheets API-Client erstellen :

const sheetsAPI = google.sheets({version: 'v4', auth});

7. Leeres Tabellenblatt mit der Sheets API erstellen

Mit einem Sheets API-Client können wir ein einfaches neues Tabellenblatt in unserem Dokument erstellen. Bevor wir fortfahren, hier noch ein kurzer Hinweis zum Vokabular:

• Eine Tabelle ist das eigentliche Dokument und wird über ihre ID referenziert (siehe oben und in der Dokument-URL).
• Ein Tabellenblatt ist einer der Tabs im Dokument. Es kann über seinen Namen (den Tabellennamen) oder eine beim Erstellen des Tabellenblatts generierte Kennung referenziert werden.

Hier ist eine Funktion, mit der Sie mit dem Sheets API-Client ein leeres Tabellenblatt an Position 2 (in der Regel nach dem Standardtabellenblatt „Sheet1“) mit 26 Spalten und 2.000 Zeilen erstellen können, wobei die erste Zeile fixiert ist (fügen Sie sie mit dem Inline-Editor in Ihre Funktion ein) :

function addEmptySheet(sheetsAPI, sheetName) {
  return new Promise((resolve, reject) => {
    const emptySheetParams = {
      spreadsheetId: process.env.SPREADSHEET_ID,
      resource: {
        requests: [
          {
            addSheet: {
              properties: {
                title: sheetName,
                index: 1,
                gridProperties: {
                  rowCount: 2000,
                  columnCount: 26,
                  frozenRowCount: 1
                }
              }
            }
          }
        ]
      }
    };
    sheetsAPI.spreadsheets.batchUpdate( emptySheetParams, function(err, response) {
        if (err) {
          reject("The Sheets API returned an error: " + err);
        } else {
          const sheetId = response.data.replies[0].addSheet.properties.sheetId;
          console.log("Created empty sheet: " + sheetId);
          resolve(sheetId);
        }
      }
    );
  });
}

Beachten Sie, dass wir anstelle einer hartzucodierenden Referenz auf die Tabelle auf die zuvor erstellte Umgebungsvariable SPREADSHEET_ID zurückgreifen.

Wir müssen uns die sheetId für weitere Anfragen an dieses bestimmte Tabellenblatt merken. Außerdem muss der Tabellenblattname eindeutig sein. Die Erstellung schlägt fehl, wenn es bereits ein Tabellenblatt mit dem Namen sheetName gibt.

Die Funktion batchUpdate in der Sheets API ist eine gängige Methode zur Interaktion mit Dokumenten und wird hier beschrieben.

8. Daten aus einer CSV-Datei im Speicher lesen

Jetzt, da wir einen Ort haben, an dem wir unsere Daten ablegen können, entwickeln wir unsere Cloud-Funktion im Inline-Editor weiter und verwenden die Cloud Storage API, um die tatsächlichen Daten aus der gerade hochgeladenen Datei abzurufen und in einem String zu speichern:

function readCSVContent(sheetsAPI, file, sheetName) {
  return new Promise((resolve, reject) => {
    const storage = new Storage();
    let fileContents = new Buffer('');
    storage.bucket(file.bucket).file(file.name).createReadStream()
    .on('error', function(err) {
      reject('The Storage API returned an error: ' + err);
    })
    .on('data', function(chunk) {
      fileContents = Buffer.concat([fileContents, chunk]);
    })  
    .on('end', function() {
      let content = fileContents.toString('utf8');
      console.log("CSV content read as string : " + content );
      resolve(content);
    });
  });
}

9. Neu erstelltes Tabellenblatt ausfüllen

Jetzt müssen wir das erstellte Tabellenblatt mit derselben Sheet-Client-API und den gerade erhobenen Daten füllen. Wir nutzen die Gelegenheit, um den Spalten des Tabellenblatts auch etwas Formatierung hinzuzufügen. Dazu ändern wir die Schriftgröße der obersten Zeile und machen sie fett:

function populateAndStyle(sheetsAPI, theData, sheetId) {
  return new Promise((resolve, reject) => {
    // Using 'batchUpdate' allows for multiple 'requests' to be sent in a single batch.
    // Populate the sheet referenced by its ID with the data received (a CSV string)
    // Style: set first row font size to 11 and to Bold. Exercise left for the reader: resize columns
    const dataAndStyle = {
      spreadsheetId: process.env.SPREADSHEET_ID,
      resource: {
        requests: [
          {
            pasteData: {
              coordinate: {
                sheetId: sheetId,
                rowIndex: 0,
                columnIndex: 0
              },
              data: theData,
              delimiter: ","
            }
          },
          {
            repeatCell: {
              range: {
                sheetId: sheetId,
                startRowIndex: 0,
                endRowIndex: 1
              },
              cell: {
                userEnteredFormat: {
                  textFormat: {
                    fontSize: 11,
                    bold: true
                  }
                }
              },
              fields: "userEnteredFormat(textFormat)"
            }
          }       
        ]
      }
    };
        
    sheetsAPI.spreadsheets.batchUpdate(dataAndStyle, function(err, response) {
      if (err) {
        reject("The Sheets API returned an error: " + err);
      } else {
        console.log(sheetId + " sheet populated with " + theData.length + " rows and column style set.");
        resolve();
      }
    });    
  });
}

Dieser Code sollte unserer Cloud-Funktion hinzugefügt werden, die jetzt zu 99% fertig ist.

Beachten Sie, wie Daten und Formatierung als mehrere requests in einem einzigen Sheets API-Aufruf batchUpdate kombiniert werden. Das sorgt für ein effizienteres und atomares Update.

Außerdem definieren wir einen Bearbeitungsbereich, der der Größe des erstellten Blatts entspricht. Das bedeutet, dass Inhalte, die mehr als 26 Spalten umfassen (der columnCount-Wert, der beim Erstellen des Tabellenblatts verwendet wurde), mit diesem Code fehlschlagen.

Wenn alles gut läuft, können Sie jetzt Folgendes tun:

1. Aktualisierte Funktion speichern
2. eine CSV-Datei in den Bucket ziehen
3. Die entsprechenden Daten werden in Ihrer Tabelle angezeigt.

10. Alles zusammenfügen und den Ablauf testen

Die Aufrufe der gerade besprochenen Funktionen können als aufeinanderfolgende blockierende Aufrufe in der ursprünglichen csv2sheet-Funktion erfolgen:

  const sheetId = await addEmptySheet(sheetsAPI, sheetName);
  const theData = await readCSVContent(sheetsAPI, data, sheetName);
  await populateAndStyle(sheetsAPI, theData, sheetId);

Wenn Sie den vollständigen Quellcode der Funktion benötigen, finden Sie ihn hier (wahrscheinlich einfacher, alles in einem Set zu erhalten).

Wenn alles eingerichtet ist, laden Sie einfach eine CSV-Datei in den richtigen Bucket hoch. Ihre Tabelle wird dann mit einem neuen Tab mit dem Inhalt der Datei aktualisiert. Hier finden Sie ein Beispiel für eine CSV-Datei.

Laden Sie mehrere Dateien in den Bucket hoch, um zu sehen, was passiert.

11. Geschafft! Zeit, die Infrastruktur abzubauen

Scherz – es gibt keine Infrastruktur, die abgebaut werden muss. Das wurde alles serverlos erledigt.

Sie können die Cloud-Funktion und den Bucket, die Sie erstellt haben, oder sogar das gesamte Projekt löschen.

12. Nächste Schritte

Damit ist dieses Codelab abgeschlossen. Sie haben gelernt, wie Sie in einer Cloud-Funktion auf Uploads in einen Cloud Storage-Bucket reagieren und ein Google-Tabellenblatt mit der entsprechenden API aktualisieren.

Anhand der folgenden Ressourcen können Sie sich weiter einarbeiten:

• Anleitungen zu Cloud Functions (mit einigen Best Practices)
• Arbeiten Sie eine der Cloud Functions-Anleitungen durch.
• Weitere Informationen zur Google Sheets API

Wenn Sie Probleme mit diesem Codelab haben, können Sie diese über den Link unten links melden.

Vielen Dank für Ihr Feedback!