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

1. Einführung

In diesem Codelab erfahren Sie, wie Sie eine Cloud Functions-Funktion schreiben, um auf eine CSV-Datei, die in Cloud Storage hochgeladen wird, zu reagieren, den Inhalt zu lesen und mithilfe der Sheets API eine Google-Tabelle zu aktualisieren.

Dies wird als Automatisierung eines ansonsten manuellen Imports als CSV-Datei bezeichnet. Schritt. Dadurch wird sichergestellt, dass Sie Daten (möglicherweise von einem anderen Team erstellt) in einer Tabellenkalkulation analysieren können, sobald sie verfügbar sind.

Die Implementierung sieht so aus :

2. Einrichtung und Anforderungen

Umgebung für das selbstbestimmte 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 ein Konto erstellen.

Hinweis: Sie können ganz einfach auf die Cloud Console zugreifen, indem Sie sich die URL merken: console.cloud.google.com.

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 in diesem Codelab später als PROJECT_ID bezeichnet.

Hinweis:Wenn Sie ein Gmail-Konto verwenden, können Sie die Standardeinstellung Keine Organisation beibehalten. 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 in der Cloud Console die Abrechnung aktivieren, um Google Cloud-Ressourcen nutzen zu können.

Dieses Codelab sollte möglichst wenig kosten. Folgen Sie der Anleitung im Abschnitt „Bereinigen“, . Hier erfahren Sie, wie Sie Ressourcen herunterfahren, damit Ihnen über dieses Tutorial hinaus keine Kosten entstehen. Neue Google Cloud-Nutzer können an einem kostenlosen Testzeitraum mit 300$Guthaben teilnehmen.

3. Google-Tabelle erstellen und konfigurieren und API aktivieren

Lassen Sie uns zuerst ein neues Google Tabellen-Dokument erstellen. Dieses Tabellenblatt kann einem beliebigen Nutzer gehören. Merken Sie sich die Kennung. wird sie 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 unter „APIs und Dienste“. und dann auf „API-Bibliothek“, Abschnitt :

Klicken Sie in der Tabelle „IAM & Admin“ rufen Sie „Dienstkonten“ auf. und notieren Sie sich die E-Mail-Adresse für das App Engine-Standarddienstkonto. Sie sollte das Format [e-mail] haben. Natürlich können Sie auch Ihr eigenes Dienstkonto für diese Aktion erstellen.

Abschließend erteilen Sie diesem Dienstkonto einfach über die Schaltfläche „Freigeben“ Bearbeitungsrechte für Ihre Tabelle. Schaltfläche :

Mit dieser Einrichtung können wir unsere Cloud Functions-Funktion schreiben und für die Verwendung dieses Dienstkontos konfigurieren. Sie kann in das soeben erstellte Tabellenkalkulationsdokument schreiben.

4. Storage-Bucket erstellen

Erstellen wir nun den Bucket, den unsere Cloud Functions-Funktion auf neue CSV-Dateien überwacht.

Gehen Sie in der Konsole über das Menü auf der linken Seite zu „Storage“... :

Erstellen Sie einen neuen Bucket mit dem Namen csv2sheet-POSTFIX (ersetzen Sie POSTFIX durch etwas Eindeutiges), wobei alle anderen Einstellungen auf ihre Standardwerte festgelegt sind :

Hinweis:Sie können Ihren eigenen Standort, den Standorttyp und die Speicherklasse auswählen. Dies sollte keine Auswirkungen auf den Rest des Codelabs haben. Im Zweifelsfall sollten Sie sich nur an die Standardwerte halten.

5. Cloud Functions-Funktion erstellen

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

Der Trigger muss auf „Cloud Storage“ festgelegt sein 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 in csv2sheet geschrieben werden soll :

Ändern Sie nun den Funktionshauptteil in :

1. die Cloud Storage API und die Sheets API verwenden
2. csv2sheet-Funktion als async markieren
3. Rufen Sie fileName aus den Cloud Storage-Ereignismetadaten ab und leiten Sie einen Namen für das neue Tabellenblatt ab, 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 für await erforderlich, wie wir gleich sehen werden.

Bei der Erstellung dieser Funktion können Sie unter anderem auf den Link "Mehr" unten im Bildschirm klicken:

• 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 Tabellenblattdokument entsprechen sollte :

Als letzten Einrichtungsschritt sehen Sie hier den package.json-Inhalt mit der Cloud Storage API und der Google Sheet API als zwei Abhängigkeiten, die wir verwenden werden (verwenden Sie den Inline-Editor PACKAGE.JSON) :

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

Nachdem Sie alles wie beschrieben konfiguriert haben, klicken Sie auf „Erstellen“. ! Nach kurzer Minute sollte die Funktion erstellt und bereitgestellt sein.

Hinweis:Alle weiteren in diesem Lab besprochenen Code müssen der gerade erstellten Cloud Functions-Funktion hinzugefügt werden.

6. Auth und Sheets API einrichten

Bevor wir mit dem Inline-Editor weiteren Code in unserer Cloud Functions-Funktion schreiben, müssen wir die Erstellung einer Google Client API mit den richtigen Zugriffs- und Tabellenbereichen blockieren. Denken Sie daran, dass dies Teil einer async-Funktion ist.

Klicken Sie im Funktionseditor der Konsole auf „BEARBEITEN“. Fügen Sie dem Hauptteil der Funktion csv2sheet den folgenden Code hinzu :

// 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"
  ]
});

Von dort aus können wir einen Sheets API-Client erstellen :

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

7. Mit der Sheets API ein leeres Tabellenblatt erstellen

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

• Eine Tabelle ist das eigentliche Dokument. Es wird durch seine Kennung referenziert (wie oben erläutert und in der Dokument-URL sichtbar).
• Ein Tabellenblatt ist eine der Registerkarten im Dokument und es kann über seinen Namen (der Tab-Name) oder eine Kennung, die bei der Erstellung des Tabellenblatts generiert wurde, auf sie verwiesen werden.

Vor diesem Hintergrund wird hier eine Funktion mit dem Sheets API-Client verwendet, um ein leeres Tabellenblatt an Position 2 (in der Regel nach der Standardeinstellung „Sheet1“) mit 26 Spalten und 2.000 Zeilen zu erstellen, wobei die erste Zeile fixiert ist. Fügen Sie sie über den Inline-Editor zu Ihrer Funktion hinzu:

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

Anstatt den Verweis auf die Tabelle hartzucodieren, verwenden wir die zuvor erstellte Umgebungsvariable SPREADSHEET_ID.

Wir müssen uns die sheetId für weitere Anfragen zu diesem Tabellenblatt merken. Außerdem muss der Name des Tabellenblatts eindeutig sein und die Erstellung schlägt fehl, wenn bereits ein Tabellenblatt mit dem Namen sheetName vorhanden ist.

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

8. Daten aus einer Speicher-CSV-Datei lesen

Da wir nun einen Speicherort zum Auslesen der Daten haben, können wir unsere Cloud Functions-Funktion im Inline-Editor weiterentwickeln und mit der Cloud Storage API die tatsächlichen Daten aus der Datei abrufen, die gerade hochgeladen wurde, und sie in einem String 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. Füllen Sie das neu erstellte Tabellenblatt.

Jetzt ist es an der Zeit, das Tabellenblatt, das wir mit derselben Sheet Client API erstellt haben, und den soeben erfassten Daten zu füllen. Wir werden diese Gelegenheit nutzen, um auch die Spalten des Arbeitsblatts mit Stilen zu versehen, indem wir die Schriftgröße der obersten Zeile ändern und sie fett formatieren :

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 Functions-Funktion hinzugefügt werden, die nun zu 99% abgeschlossen ist.

Daten und Stile werden als mehrere requests in einem einzigen batchUpdate-Aufruf der Sheets API kombiniert. Dies ermöglicht ein effizienteres und atomares Update.

Beachten Sie auch, dass wir einen Bearbeitungsbereich definieren, der der Größe des von uns erstellten Tabellenblatts entspricht. Das bedeutet, dass Inhalte mit mehr als 26 Spalten (der Wert columnCount, der beim Erstellen des Tabellenblatts verwendet wurde) nicht mit diesem Code zurückgegeben werden.

Wenn alles gut läuft, können Sie zu diesem Zeitpunkt Folgendes tun:

1. Aktualisierte Funktion speichern
2. CSV-Datei in den Bucket ziehen
3. werden die entsprechenden Daten in einem Pop-up-Fenster in Ihrer Tabelle angezeigt.

10. Alles zusammenfügen und den Ablauf testen

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

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

Den vollständigen Quellcode der Funktion finden Sie hier. Es ist wahrscheinlich einfacher, alles in einem Satz zusammenzustellen.

Sobald alles eingerichtet ist, laden Sie einfach eine CSV-Datei in den richtigen Bucket hoch und beobachten Sie, wie Ihre Tabelle mit einem neuen Tabellenblatt mit dem Inhalt der Datei aktualisiert wird. Hier finden Sie eine Beispiel-CSV-Datei, falls Sie keine zur Hand haben.

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

11. Fertig! Zeit, die Infrastruktur außer Betrieb zu nehmen

Scherz beiseite, es muss keine Infrastruktur abgebaut werden, alles wurde serverlos gemacht.

Wenn Sie möchten, können Sie die Cloud Functions-Funktion und den von Ihnen erstellten Bucket oder sogar das gesamte Projekt löschen.

12. Nächste Schritte

In diesem Codelab erfahren Sie, wie Sie Uploads in einen Cloud Storage-Bucket in einer Cloud Functions-Funktion überwachen, um eine Google-Tabelle mit der entsprechenden API zu aktualisieren.

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

• Cloud Functions-Anleitungen (einschließlich Best Practices)
• Eine der Anleitungen für Cloud Functions kennenlernen
• Weitere Informationen zur Google Sheets API

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

Wir wissen Ihr Feedback zu schätzen.