Über einen Workflow in Google Tabellen schreiben

Google Tabellen ist eine cloudbasierte Tabellenkalkulationslösung, die die Zusammenarbeit in Echtzeit unterstützt und Tools zum Visualisieren, Verarbeiten und Kommunizieren von Daten bietet.

Im folgenden Beispiel wird gezeigt, wie Sie aus einem Workflow in Google Tabellen schreiben. Der Workflow fragt ein BigQuery-Dataset ab und schreibt die Ergebnisse in eine Google-Tabelle. Dabei werden Workflows-Connectors verwendet, um das Aufrufen von Google Cloud APIs zu vereinfachen.

Hinweise

Bevor Sie das Beispiel in diesem Dokument ausprobieren, müssen Sie Folgendes erledigt haben.

  1. Aktivieren Sie die Compute Engine API, die Google Drive API, die Google Sheets API und die Workflows API.

    Console

    Aktivieren Sie die APIs

    gcloud 

    gcloud services enable \
    compute.googleapis.com \
    drive.googleapis.com \
    sheets.googleapis.com \
    workflows.googleapis.com

  2. Notieren Sie sich das Compute Engine-Standarddienstkonto, das Sie zu Testzwecken mit dem Beispielworkflow verknüpfen. Für neue Projekte, für die die Compute Engine API aktiviert ist, wird dieses Dienstkonto mit der einfachen IAM-Rolle „Bearbeiter“ und dem folgenden E-Mail-Format erstellt:

    PROJECT_NUMBER-compute@developer.gserviceaccount.com

    Sie finden Ihre Projektnummer auf der Willkommensseite der Google Cloud -Konsole oder Sie können sie so abrufen:

    gcloud projects describe PROJECT_ID

    Für Produktionsumgebungen empfehlen wir dringend, ein neues Dienstkonto zu erstellen und ihm eine oder mehrere IAM-Rollen zuzuweisen, die die erforderlichen Mindestberechtigungen enthalten und dem Grundsatz der geringsten Berechtigung folgen.

  3. Erstellen Sie einen neuen Ordner in Google Drive. In diesem Ordner wird Ihre Tabelle gespeichert. Wenn Sie eine Berechtigung für den freigegebenen Ordner einrichten, darf Ihr Workflow in die Tabelle schreiben.

    1. Rufen Sie drive.google.com auf.
    2. Klicken Sie auf Neu > Neuer Ordner.
    3. Geben Sie einen Namen für den Ordner ein.
    4. Klicken Sie auf Erstellen.
    5. Klicken Sie mit der rechten Maustaste auf den neuen Ordner und wählen Sie Freigeben aus.

    6. Fügen Sie die E-Mail-Adresse für das Compute Engine-Standarddienstkonto hinzu.

      Dadurch erhält das Dienstkonto Zugriff auf den Ordner. Wenn Sie das Dienstkonto mit Ihrem Workflow verknüpfen, hat der Workflow Bearbeitungszugriff auf alle Dateien im Ordner. Weitere Informationen zum Freigeben von Dateien, Ordnern und Ablagen

    7. Wählen Sie die Rolle Editor aus.

    8. Entfernen Sie das Häkchen aus dem Kästchen Personen benachrichtigen.

    9. Klicken Sie auf Freigeben.

Tabelle erstellen

Sie haben folgende Möglichkeiten, eine Tabelle zu erstellen:

Mit der Google Sheets API ist es nicht möglich, eine Tabelle direkt in einem bestimmten Ordner zu erstellen. Es gibt jedoch Alternativen, z. B. die Tabellenkalkulation nach dem Erstellen in einen bestimmten Ordner zu verschieben, wie in den folgenden Beispielen gezeigt. Weitere Informationen finden Sie unter Mit Google Drive-Ordnern arbeiten.

Tabelle mit Google Sheets erstellen

Wenn Sie eine Tabelle über Google Sheets erstellen, wird sie in Google Drive gespeichert. Standardmäßig wird die Tabelle im Stammordner in Drive gespeichert.

  1. Rufen Sie sheets.google.com auf.

  2. Klicken Sie auf Neu Plus.

    Dadurch wird eine neue Tabelle erstellt und geöffnet. Jede Tabelle hat einen eindeutigen spreadsheetId-Wert, der Buchstaben, Ziffern, Bindestriche oder Unterstriche enthalten kann. Sie finden die Tabellen-ID in einer Google Sheets-URL:

    https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=0

  3. Notieren Sie sich diese ID, da sie beim Erstellen des Workflows benötigt wird.

  4. Verschieben Sie die Tabelle in den Google Drive-Ordner, den Sie zuvor erstellt haben:

    1. Wählen Sie in der Tabelle Datei> Verschieben aus.
    2. Rufen Sie den von Ihnen erstellten Ordner auf.
    3. Klicken Sie auf Verschieben.

Tabelle mit dem Google Sheets API-Connector erstellen

Mit dem Google Sheets API-Connector können Sie eine Tabelle erstellen. Da Workflows das Dienstkonto als Triggeridentität verwendet, wird die Tabelle im Google Drive-Stammordner des Dienstkontos erstellt. Anschließend können Sie die Tabelle in einen anderen Ordner verschieben.

Im folgenden Workflow wird spreadsheetId aus dem resp-Ergebnis abgerufen:

YAML

main:
  steps:
    - init:
        assign:
          - folder_id: 'FOLDER_ID'
          - drive_url: 'https://www.googleapis.com/drive/v3/files/'
          - drive_auth_scope: 'https://www.googleapis.com/auth/drive'
    - create_sheet:
        call: googleapis.sheets.v4.spreadsheets.create
        args:
          body: null
          connector_params:
            scopes: 'https://www.googleapis.com/auth/drive'
        result: resp
    - get_sheet_info:
        call: http.get
        args:
          url: '${drive_url + resp.spreadsheetId}'
          query:
            fields: parents
          auth:
            type: OAuth2
            scopes: '${drive_auth_scope}'
        result: sheet_info
    - move_sheet:
        call: http.patch
        args:
          url: '${drive_url + resp.spreadsheetId}'
          query:
            addParents: '${folder_id}'
            removeParents: '${sheet_info["body"]["parents"][0]}'
          auth:
            type: OAuth2
            scopes: '${drive_auth_scope}'
        result: resp
    - return:
        return: '${resp}'

JSON

{
  "main": {
    "steps": [
      {
        "init": {
          "assign": [
            {
              "folder_id": "FOLDER_ID"
            },
            {
              "drive_url": "https://www.googleapis.com/drive/v3/files/"
            },
            {
              "drive_auth_scope": "https://www.googleapis.com/auth/drive"
            }
          ]
        }
      },
      {
        "create_sheet": {
          "call": "googleapis.sheets.v4.spreadsheets.create",
          "args": {
            "body": null,
            "connector_params": {
              "scopes": "https://www.googleapis.com/auth/drive"
            }
          },
          "result": "resp"
        }
      },
      {
        "get_sheet_info": {
          "call": "http.get",
          "args": {
            "url": "${drive_url + resp.spreadsheetId}",
            "query": {
              "fields": "parents"
            },
            "auth": {
              "type": "OAuth2",
              "scopes": "${drive_auth_scope}"
            }
          },
          "result": "sheet_info"
        }
      },
      {
        "move_sheet": {
          "call": "http.patch",
          "args": {
            "url": "${drive_url + resp.spreadsheetId}",
            "query": {
              "addParents": "${folder_id}",
              "removeParents": "${sheet_info[\"body\"][\"parents\"][0]}"
            },
            "auth": {
              "type": "OAuth2",
              "scopes": "${drive_auth_scope}"
            }
          },
          "result": "resp"
        }
      },
      {
        "return": {
          "return": "${resp}"
        }
      }
    ]
  }
}

Ersetzen Sie FOLDER_ID durch die ID des Ordners, in den Sie die Tabelle verschieben möchten. Jeder Drive-Ordner hat eine eindeutige ID, die Buchstaben, Ziffern, Bindestriche oder Unterstriche enthalten kann. Die Ordner-ID finden Sie in der Ordner-URL:

https://drive.google.com/drive/folders/FOLDER_ID/edit#gid=0

Weitere Informationen finden Sie unter Ordner erstellen und mit Daten füllen.

Die Ausgabe des Workflows sollte in etwa so aussehen, wobei der Wert id der spreadsheetId ist:

"body": {
    "id": "spreadsheetId",
    "kind": "drive#file",
    "mimeType": "application/vnd.google-apps.spreadsheet",
    "name": "Untitled spreadsheet"
  }

Öffentliches BigQuery-Dataset untersuchen

In BigQuery werden eine Reihe von öffentlichen Datasets gehostet, die von der Allgemeinheit abgefragt werden können.

In BigQuery können Sie einen interaktiven (On-Demand) Abfragejob ausführen. Die folgende Abfrage gibt beispielsweise die 100 beliebtesten Namen in einem bestimmten Dataset zurück und schreibt die Ausgabe in eine temporäre Tabelle. Dies ist die Abfrage, die von Ihrem Workflow ausgeführt wird.

Console

  1. Rufen Sie in der Google Cloud Console die Seite BigQuery auf.

    BigQuery aufrufen

  2. Geben Sie im Textbereich des Abfrageeditors die folgende BigQuery-SQL-Abfrage ein:

    SELECT name, gender, SUM(number) AS total
FROM `bigquery-public-data.usa_names.usa_1910_2013`
GROUP BY name, gender
ORDER BY total DESC
LIMIT 100

  3. Klicken Sie auf Ausführen.

bq

Geben Sie im Terminal den folgenden bq query-Befehl ein, um eine interaktive Abfrage mit Standard-SQL-Syntax auszuführen:

    bq query \
    --use_legacy_sql=false \
    'SELECT
      name, gender, SUM(number) AS total
    FROM
      `bigquery-public-data.usa_names.usa_1910_2013`
    GROUP BY
      name, gender
    ORDER BY
      total DESC
    LIMIT 100'

Workflow bereitstellen, der in Google Tabellen schreibt

Stellen Sie einen Workflow bereit, der ein BigQuery-Dataset mit dem BigQuery API-Connector abfragt und die Ergebnisse mit dem Google Sheets API-Connector in eine Google-Tabelle schreibt.

Console

  1. Rufen Sie in der Google Cloud Console die Seite Workflows auf:

    Zur Seite "Workflows"

  2. Klicken Sie auf  Erstellen.

  3. Geben Sie einen Namen für den neuen Workflow ein: read-bigquery-write-sheets.

  4. Wählen Sie in der Liste Region die Option us-central1 (Iowa) aus.

  5. Wählen Sie für das Dienstkonto das Compute Engine-Standarddienstkonto (PROJECT_NUMBER-compute@developer.gserviceaccount.com) aus.

  6. Klicken Sie auf Weiter.

  7. Geben Sie im Workflow-Editor die folgende Definition für Ihren Workflow ein:

    main:
    steps:
    - init:
        assign:
        # Replace with your sheetId and make sure the service account
        # for the workflow has write permissions to the sheet
        - sheetId: "1D8n7uoU8kGwQvR4rcLkF10CdAfnUKE2o0yl6P-Z7nfM"
        - limit: 100
    - runQuery:
        call: googleapis.bigquery.v2.jobs.query
        args:
            projectId: ${sys.get_env("GOOGLE_CLOUD_PROJECT_ID")}
            body:
                useLegacySql: false
                # Query name and gender of most popular names
                query: ${"SELECT name, gender, SUM(number) AS total
                    FROM `bigquery-public-data.usa_names.usa_1910_2013`
                    GROUP BY name, gender
                    ORDER BY total DESC
                    LIMIT " + limit}
        result: queryResult
    - init_header_row:
        assign:
        - rows:
            - ["Name", "Gender", "Total"]
    - process_query_result:
        for:
            value: row
            in: ${queryResult.rows}
            steps:
            - process_each_row:
                assign:
                - name: ${row.f[0].v}
                - gender: ${row.f[1].v}
                - total: ${row.f[2].v}
                - row: ["${name}", "${gender}", "${total}"]
                - rows: ${list.concat(rows, row)}
    - clear_existing_values:
        call: googleapis.sheets.v4.spreadsheets.values.clear
        args:
            range: "Sheet1"
            spreadsheetId: ${sheetId}
        result: clearResult
    - update_sheet:
        call: googleapis.sheets.v4.spreadsheets.values.update
        args:
            range: ${"Sheet1!A1:C" + (limit + 1)}
            spreadsheetId: ${sheetId}
            valueInputOption: RAW
            body:
                majorDimension: "ROWS"
                values: ${rows}
        result: updateResult
    - returnResult:
        return: ${updateResult}

  8. Ersetzen Sie den Platzhalterwert sheetId durch Ihre spreadsheetId.

  9. Klicken Sie auf Bereitstellen.

gcloud

  1. Erstellen Sie eine Quellcodedatei für Ihren Workflow:

    touch read-bigquery-write-sheets.yaml

  2. Kopieren Sie den folgenden Workflow in einem Texteditor in Ihre Quellcodedatei:

    main:
    steps:
    - init:
        assign:
        # Replace with your sheetId and make sure the service account
        # for the workflow has write permissions to the sheet
        - sheetId: "1D8n7uoU8kGwQvR4rcLkF10CdAfnUKE2o0yl6P-Z7nfM"
        - limit: 100
    - runQuery:
        call: googleapis.bigquery.v2.jobs.query
        args:
            projectId: ${sys.get_env("GOOGLE_CLOUD_PROJECT_ID")}
            body:
                useLegacySql: false
                # Query name and gender of most popular names
                query: ${"SELECT name, gender, SUM(number) AS total
                    FROM `bigquery-public-data.usa_names.usa_1910_2013`
                    GROUP BY name, gender
                    ORDER BY total DESC
                    LIMIT " + limit}
        result: queryResult
    - init_header_row:
        assign:
        - rows:
            - ["Name", "Gender", "Total"]
    - process_query_result:
        for:
            value: row
            in: ${queryResult.rows}
            steps:
            - process_each_row:
                assign:
                - name: ${row.f[0].v}
                - gender: ${row.f[1].v}
                - total: ${row.f[2].v}
                - row: ["${name}", "${gender}", "${total}"]
                - rows: ${list.concat(rows, row)}
    - clear_existing_values:
        call: googleapis.sheets.v4.spreadsheets.values.clear
        args:
            range: "Sheet1"
            spreadsheetId: ${sheetId}
        result: clearResult
    - update_sheet:
        call: googleapis.sheets.v4.spreadsheets.values.update
        args:
            range: ${"Sheet1!A1:C" + (limit + 1)}
            spreadsheetId: ${sheetId}
            valueInputOption: RAW
            body:
                majorDimension: "ROWS"
                values: ${rows}
        result: updateResult
    - returnResult:
        return: ${updateResult}

  3. Ersetzen Sie den Platzhalterwert sheetId durch Ihre spreadsheetId.

  4. Stellen Sie den Workflow bereit. Geben Sie hierzu den folgenden Befehl ein:

    gcloud workflows deploy read-bigquery-write-sheets \
    --source=read-bigquery-write-sheets.yaml \
    --location=us-central1 \
    --service-account=PROJECT_NUMBER-compute@developer.gserviceaccount.com

    Ersetzen Sie PROJECT_NUMBER durch die Projektnummer Ihres Google Cloud-Projekts. Sie finden Ihre Projektnummer auf der Willkommensseite der Google Cloud -Konsole.

Workflow ausführen und Ergebnisse prüfen

Bei der Ausführung eines Workflows wird die aktuelle Workflowdefinition ausgeführt, die dem Workflow zugeordnet ist.

  1. Workflow ausführen:

    Console

    1. Rufen Sie in der Google Cloud Console die Seite Workflows auf:

      Zur Seite "Workflows"

    2. Wählen Sie auf der Seite Workflows den Workflow read-bigquery-write-sheets aus, um die zugehörige Detailseite aufzurufen.

    3. Klicken Sie auf der Seite Workflow-Details auf  Ausführen.

    4. Klicken Sie noch einmal auf Ausführen.

    5. Sehen Sie sich die Ergebnisse des Workflows im Bereich Ausgabe an.

      Die Ausgabe sollte in etwa so aussehen:

      {
"spreadsheetId": "1pgkNY7mWICA6o6INUNLHGnmk0Tdioh0nUghZPOYPpZA",
"updatedCells": 303,
"updatedColumns": 3,
"updatedRange": "Sheet1!A1:C101",
"updatedRows": 101
}

    gcloud

    1. Öffnen Sie ein Terminalfenster.

    2. Führen Sie den Workflow aus:

      gcloud workflows run read-bigquery-write-sheets

      Die Ausführungsergebnisse sollten in etwa so aussehen:

      Waiting for execution [4dcf737b-69d9-4081-b8d9-86d39ae86bd1] to complete...done.     
argument: 'null'
duration: 3.131912897s
endTime: '2023-01-25T14:59:46.818828242Z'
name: projects/918619793306/locations/us-central1/workflows/read-bigquery-write-sheets/executions/4dcf737b-69d9-4081-b8d9-86d39ae86bd1
result: '{"spreadsheetId":"1pgkNY7mWICA6o6INUNLHGnmk0Tdioh0nUghZPOYPpZA","updatedCells":303,"updatedColumns":3,"updatedRange":"Sheet1!A1:C101","updatedRows":101}'
startTime: '2023-01-25T14:59:43.686915345Z'
state: SUCCEEDED

  2. Prüfen Sie, ob die Ergebnisse der Abfrage in die Tabelle geschrieben wurden. Die Anzahl der Spalten und Zeilen in der Tabelle sollte beispielsweise mit den Werten updatedColumns und updatedRows übereinstimmen.

Nächste Schritte