Scrivere in Fogli Google da un flusso di lavoro

Fogli Google è una soluzione di 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 query su un set di dati BigQuery e scrive i risultati in un foglio di lavoro di Fogli. Utilizza connettori di Workflows per semplificare la chiamata delle API Google Cloud.

Prima di iniziare

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

  1. Abilita 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. Prendi nota dell'account di servizio predefinito di Compute Engine, poiché lo assocerai al flusso di lavoro di esempio a scopo di test. I nuovi progetti che hanno attivato l'API Compute Engine hanno questo account di servizio creato con il ruolo di 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 oppure recuperarlo:

    gcloud projects describe PROJECT_ID

    Per gli ambienti di produzione, ti consigliamo vivamente di creare un nuovo account di servizio e di assegnargli uno o più ruoli IAM contenenti le autorizzazioni minime richieste e di seguire il principio del privilegio minimo.

  3. Crea una nuova cartella su Google Drive. Questa cartella viene utilizzata per archiviare il foglio di lavoro. Configurando un'autorizzazione per la cartella condivisa, il flusso di lavoro è autorizzato a 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 tasto destro del mouse sulla nuova cartella e seleziona Condividi.

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

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

    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 è possibile creare un foglio di lavoro direttamente all'interno di una cartella specificata utilizzando l'API Fogli Google. Esistono però delle alternative, ad esempio spostare il foglio di lavoro in una cartella specifica dopo averlo creato, come mostrato negli esempi riportati di seguito. Per ulteriori informazioni, vedi Utilizzare le cartelle di Google Drive.

Creare un foglio di lavoro utilizzando Fogli Google

Quando crei un foglio di lavoro tramite Fogli Google, questo viene salvato su Google Drive. Per impostazione predefinita, il foglio di lavoro viene salvato nella cartella principale su Drive.

  1. Vai alla pagina sheets.google.com.

  2. Fai clic su Nuovo Plus.

    Verrà creato e aperto un 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 perché ti servirà quando crei il flusso di lavoro.

  4. Sposta il foglio di lavoro nella cartella di Google Drive che hai creato 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 dell'API Fogli Google

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

Nel seguente flusso di lavoro, 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 della cartella nell'URL della cartella:

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

Per saperne di più, consulta Creare e compilare 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"
  }

Esplorare il set di dati pubblico BigQuery

BigQuery ospita una serie di set di dati pubblici su cui il pubblico in generale può eseguire query.

In BigQuery, puoi eseguire un job di query interattiva (on demand). Ad esempio, la seguente query restituisce i 100 nomi più popolari in un insieme 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 BigQuery SQL 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 API Fogli Google.

Console

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

    Vai a Flussi di lavoro

  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. Fai clic su Avanti.

  7. Nell'editor del flusso di lavoro, inserisci la seguente definizione 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 del segnaposto sheetId con il tuo 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 del segnaposto sheetId con il tuo 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 la definizione attuale del flusso di lavoro associata al flusso di lavoro.

  1. Esegui il flusso di lavoro:

    Console

    1. Nella console Google Cloud, vai alla pagina Workflows:

      Vai a Flussi di lavoro

    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 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 al seguente:

      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