Scrivere in Fogli Google da un flusso di lavoro

Fogli Google è una soluzione per fogli di lavoro basata su cloud che supporta la collaborazione in tempo reale e fornisce strumenti per visualizzare, elaborare e comunicare i dati.

L'esempio seguente mostra come scrivere in Fogli da un flusso di lavoro. Il flusso di lavoro esegue una query su un set di dati BigQuery e scrive i risultati in un foglio di lavoro Fogli. Utilizza i connettori di Workflows per semplificare le chiamate alle API Google Cloud.

Inizia

Prima di provare l'esempio in questo documento, assicurati di aver completato quanto segue.

  1. Abilitare le API Compute Engine, Google Drive, Fogli Google e Workflows.

    Console

    Abilita le API

    gcloud 

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

  2. I nuovi progetti che hanno abilitato l'API Compute Engine hanno un account di servizio predefinito di Compute Engine creato con il ruolo Editor di base IAM e con il seguente formato email:

    PROJECT_NUMBER-compute@developer.gserviceaccount.com

    Puoi trovare il numero del progetto nella pagina Ti diamo il benvenuto della console Google Cloud.

    Prendi nota di questo account di servizio, che viene utilizzato per testare gli esempi in questo documento.

  3. Crea una nuova cartella su Google Drive. Questa cartella viene utilizzata per archiviare il foglio di lavoro. Se imposti un'autorizzazione per la cartella condivisa, il flusso di lavoro può scrivere nel foglio di lavoro.

    1. Accedi a drive.google.com.
    2. Fai clic su Nuovo > Nuova cartella.
    3. Inserisci un nome per la cartella.
    4. Fai clic su Crea.
    5. Fai clic con il pulsante destro del mouse sulla nuova cartella e seleziona Condividi.

    6. Aggiungi l'indirizzo email dell'account di servizio predefinito di Compute Engine.

      In questo modo l'account di servizio potrà accedere alla cartella. Quando associ l'account di servizio al flusso di lavoro, quest'ultimo avrà accesso in modifica a qualsiasi file nella cartella. Scopri di più sulla condivisione di file, cartelle e Drive.

    7. Seleziona il ruolo Editor.

    8. Deseleziona la casella di controllo Invia notifiche.

    9. Fai clic su Condividi.

Creazione di un foglio di lavoro

Puoi creare un foglio di lavoro in uno dei seguenti modi:

Non esiste un'opzione per creare un foglio di lavoro direttamente in una cartella specificata con l'API Fogli Google. Esistono però delle alternative, tra cui lo spostamento del foglio di lavoro in una cartella specifica dopo averlo creato, come fai negli esempi che seguono. Per ulteriori informazioni, vedi Lavorare con le cartelle di Google Drive.

Creare un foglio di lavoro con Fogli Google

I fogli di lavoro creati con Fogli Google vengono salvati su Google Drive. Per impostazione predefinita, il foglio di lavoro viene salvato nella cartella principale di Drive.

  1. Vai a sheets.google.com.

  2. Fai clic su Nuovo Plus.

    Verrà creato e aperto il nuovo foglio di lavoro. Ogni foglio di lavoro ha un valore spreadsheetId univoco contenente lettere, numeri, trattini o trattini bassi. Puoi trovare l'ID del foglio di lavoro in un URL di Fogli Google:

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

  3. Prendi nota di questo ID, che sarà necessario quando crei il flusso di lavoro.

  4. Sposta il foglio di lavoro nella cartella di Google Drive creata in precedenza:

    1. Nel foglio di lavoro, seleziona File > Sposta.
    2. Vai alla cartella che hai creato.
    3. Fai clic su Sposta.

Creare un foglio di lavoro utilizzando il connettore API Fogli Google

Puoi utilizzare il connettore API Fogli Google per creare un foglio di lavoro. Poiché Workflows utilizza l'account di servizio come identità trigger, il foglio di lavoro viene creato nella cartella principale di Google Drive dell'account di servizio. Puoi quindi spostare il foglio di lavoro in un'altra cartella.

Nel seguente flusso di lavoro, l'elemento spreadsheetId viene recuperato dal risultato resp:

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}"
        }
      }
    ]
  }
}

Sostituisci FOLDER_ID con l'ID della cartella in cui vuoi spostare il foglio di lavoro. Ogni cartella di Drive ha un ID univoco contenente lettere, numeri, trattini o trattini bassi. Puoi trovare l'ID cartella nell'URL della cartella:

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

Per maggiori informazioni, consulta Creare e compilare le cartelle.

L'output del flusso di lavoro dovrebbe essere simile al seguente, dove il valore id è spreadsheetId:

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

Esplora il set di dati pubblico BigQuery

BigQuery ospita una serie di set di dati pubblici disponibili per le query al pubblico.

In BigQuery, puoi eseguire un job di query interattivo (on demand). Ad esempio, la seguente query restituisce i 100 nomi più popolari in un set di dati specifico e scrive l'output in una tabella temporanea. Questa è la query che verrà eseguita dal flusso di lavoro.

Console

  1. Nella console Google Cloud, vai alla pagina BigQuery.

    Vai a BigQuery

  2. Inserisci la seguente query SQL di BigQuery nell'area di testo Editor 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 100

  3. Fai clic su Esegui.

bq

Nel terminale, inserisci il seguente comando bq query per eseguire una query interattiva utilizzando la sintassi SQL standard:

    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'

Esegui il deployment di un flusso di lavoro che scrive in Fogli

Esegui il deployment di un flusso di lavoro che esegue query su un set di dati BigQuery utilizzando il connettore API BigQuery e che scrive i risultati in un foglio di lavoro di Fogli utilizzando il connettore dell'API di Fogli Google.

Console

  1. Nella console Google Cloud, vai alla pagina Flussi di lavoro:

    Vai a Workflows

  2. Fai clic su Crea.

  3. Inserisci un nome per il nuovo flusso di lavoro: read-bigquery-write-sheets.

  4. Nell'elenco Regione, seleziona us-central1 (Iowa).

  5. Per Account di servizio, seleziona l'account di servizio predefinito di Compute Engine (PROJECT_NUMBER-compute@developer.gserviceaccount.com).

  6. Tocca Avanti.

  7. Nell'editor del flusso di lavoro, inserisci la definizione seguente per il flusso di lavoro:

    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. Sostituisci il valore segnaposto sheetId con il valore spreadsheetId.

  9. Fai clic su Esegui il deployment.

gcloud

  1. Crea un file di codice sorgente per il tuo flusso di lavoro:

    touch read-bigquery-write-sheets.yaml

  2. In un editor di testo, copia il seguente flusso di lavoro nel file di codice sorgente:

    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. Sostituisci il valore segnaposto sheetId con il valore spreadsheetId.

  4. Esegui il deployment del flusso di lavoro inserendo il seguente comando:

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

    Sostituisci PROJECT_NUMBER con il numero del tuo progetto Google Cloud. Puoi trovare il numero del progetto nella pagina Ti diamo il benvenuto della console Google Cloud.

Esegui il flusso di lavoro e verifica i risultati

L'esecuzione di un flusso di lavoro esegue l'attuale definizione del flusso di lavoro associata al flusso di lavoro.

  1. Esegui il flusso di lavoro:

    Console

    1. Nella console Google Cloud, vai alla pagina Flussi di lavoro:

      Vai a Workflows

    2. Nella pagina Flussi di lavoro, seleziona il flusso di lavoro read-bigquery-write-sheets per accedere alla relativa pagina dei dettagli.

    3. Nella pagina Dettagli flusso di lavoro, fai clic su Esegui.

    4. Fai di nuovo clic su Execute (Esegui).

    5. Visualizza i risultati del flusso di lavoro nel riquadro Output.

      L'output dovrebbe essere simile al seguente:

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

    gcloud

    1. Apri un terminale.

    2. Esegui il flusso di lavoro:

      gcloud workflows run read-bigquery-write-sheets

      I risultati dell'esecuzione dovrebbero essere simili ai seguenti:

      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. Verifica che il flusso di lavoro abbia scritto i risultati della query nel foglio di lavoro. Ad esempio, il numero di colonne e righe nel foglio di lavoro deve corrispondere ai valori updatedColumns e updatedRows.

Passaggi successivi