Écrire dans Google Sheets à partir d'un workflow

Google Sheets est une solution de feuille de calcul dans le cloud qui permet de collaborer en temps réel et fournit des outils pour visualiser, traiter et communiquer des données.

L'exemple suivant montre comment écrire dans Sheets à partir d'un workflow. Le workflow interroge un ensemble de données BigQuery et écrit les résultats dans une feuille de calcul Sheets. Il utilise des connecteurs Workflows pour simplifier l'appel des API Google Cloud.

Avant de commencer

Avant d'essayer l'exemple de ce document, assurez-vous d'avoir effectué les opérations suivantes.

  1. Activez les API Compute Engine, Google Drive, Google Sheets et Workflows.

    Console

    Activer les API

    gcloud

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

  2. Notez le compte de service Compute Engine par défaut, car vous allez l'associer à l'exemple de workflow à des fins de test. Pour les nouveaux projets pour lesquels l'API Compute Engine est activée, ce compte de service est créé avec le rôle d'éditeur de base IAM et au format d'adresse e-mail suivant:

    PROJECT_NUMBER-compute@developer.gserviceaccount.com

    Vous pouvez trouver le numéro de votre projet sur la page Bienvenue de la console Google Cloud ou le récupérer:

    gcloud projects describe PROJECT_ID

    Pour les environnements de production, nous vous recommandons vivement de créer un compte de service et de lui attribuer un ou plusieurs rôles IAM contenant les autorisations minimales requises conformément au principe du moindre privilège.

  3. Créez un dossier dans Google Drive. Ce dossier permet de stocker votre feuille de calcul. En configurant une autorisation pour le dossier partagé, votre workflow est autorisé à écrire dans la feuille de calcul.

    1. Accédez à drive.google.com.
    2. Cliquez sur Nouveau > Nouveau dossier.
    3. Saisissez un nom pour le dossier.
    4. Cliquez sur Créer.
    5. Effectuez un clic droit sur votre nouveau dossier, puis sélectionnez Partager.

    6. Ajoutez l'adresse e-mail du compte de service Compute Engine par défaut.

      Cela donne au compte de service accès au dossier. Lorsque vous associez le compte de service à votre workflow, celui-ci dispose d'un accès en modification à tous les fichiers du dossier. En savoir plus sur le partage de fichiers, de dossiers et de Drive

    7. Sélectionnez le rôle Rédacteur.

    8. Décochez la case Envoyer une notification.

    9. Cliquez sur Partager,

Créer une feuille de calcul

Vous pouvez créer une feuille de calcul de l'une des manières suivantes:

Il n'est pas possible de créer une feuille de calcul directement dans un dossier spécifié à l'aide de l'API Google Sheets. Toutefois, il existe d'autres options, comme déplacer la feuille de calcul dans un dossier spécifique après l'avoir créée, comme dans les exemples suivants. Pour en savoir plus, consultez la section Utiliser des dossiers Google Drive.

Créer une feuille de calcul à l'aide de Google Sheets

Lorsque vous créez une feuille de calcul via Google Sheets, elle est enregistrée dans Google Drive. Par défaut, la feuille de calcul est enregistrée dans votre dossier racine sur Drive.

  1. Accédez à sheets.google.com.

  2. Cliquez sur Nouveau Plus.

    La nouvelle feuille de calcul s'ouvre. Chaque feuille de calcul est associée à une valeur spreadsheetId unique, composée de lettres, de chiffres, de traits d'union ou de traits de soulignement. Vous trouverez l'identifiant de la feuille de calcul dans une URL Google Sheets:

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

  3. Notez cet ID, car vous en aurez besoin lorsque vous créerez votre workflow.

  4. Déplacez la feuille de calcul vers le dossier Google Drive que vous avez créé précédemment:

    1. Dans la feuille de calcul, sélectionnez Fichier > Déplacer.
    2. Accédez au dossier que vous avez créé.
    3. Cliquez sur Déplacer.

Créer une feuille de calcul à l'aide du connecteur de l'API Google Sheets

Vous pouvez utiliser le connecteur de l'API Google Sheets pour créer une feuille de calcul. Comme Workflows utilise le compte de service comme identité du déclencheur, la feuille de calcul est créée dans le dossier racine Google Drive du compte de service. Vous pouvez ensuite déplacer la feuille de calcul vers un autre dossier.

Dans le workflow suivant, le spreadsheetId est extrait du résultat 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}"
        }
      }
    ]
  }
}

Remplacez FOLDER_ID par l'ID du dossier vers lequel vous souhaitez déplacer la feuille de calcul. Chaque dossier Drive possède un ID unique, composé de lettres, de chiffres, de traits d'union ou de traits de soulignement. Vous trouverez l'ID du dossier dans l'URL du dossier:

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

Pour en savoir plus, consultez la section Créer et renseigner des dossiers.

Le résultat du workflow devrait ressembler à ce qui suit, où la valeur id est spreadsheetId:

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

Explorer l'ensemble de données public BigQuery

BigQuery héberge un certain nombre d'ensembles de données publics que le grand public peut interroger.

Dans BigQuery, vous pouvez exécuter une tâche de requête interactive (à la demande). Par exemple, la requête suivante renvoie les 100 noms les plus populaires d'un ensemble de données spécifique et écrit la sortie dans une table temporaire. Il s'agit de la requête que votre workflow exécutera.

Console

  1. Dans la console Google Cloud, accédez à la page BigQuery.

    Accéder à BigQuery

  2. Saisissez la requête SQL BigQuery suivante dans la zone de texte de l'éditeur de requête:

    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. Cliquez sur Exécuter.

bq

Dans le terminal, saisissez la commande bq query suivante pour exécuter une requête interactive à l'aide de la syntaxe 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'

Déployer un workflow qui écrit dans Sheets

Déployez un workflow qui interroge un ensemble de données BigQuery à l'aide du connecteur de l'API BigQuery et qui écrit les résultats dans une feuille de calcul Sheets à l'aide du connecteur de l'API Google Sheets.

Console

  1. Dans la console Google Cloud, accédez à la page Workflows:

    Accéder à "Workflows"

  2. Cliquez sur  Créer.

  3. Saisissez un nom pour le nouveau workflow: read-bigquery-write-sheets.

  4. Dans la liste Région, sélectionnez us-central1 (Iowa).

  5. Pour Compte de service, sélectionnez le compte de service Compute Engine par défaut (PROJECT_NUMBER-compute@developer.gserviceaccount.com).

  6. Cliquez sur Suivant.

  7. Dans l'éditeur de workflow, saisissez la définition suivante pour votre workflow:

    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. Remplacez la valeur de l'espace réservé sheetId par votre spreadsheetId.

  9. Cliquez sur Déployer.

gcloud

  1. Créez un fichier de code source pour votre workflow:

    touch read-bigquery-write-sheets.yaml

  2. Dans un éditeur de texte, copiez le workflow suivant dans votre fichier de code source:

    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. Remplacez la valeur de l'espace réservé sheetId par votre spreadsheetId.

  4. Déployez le workflow en saisissant la commande suivante :

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

    Remplacez PROJECT_NUMBER par votre numéro de projet Google Cloud. Vous trouverez le numéro de votre projet sur la page Bienvenue de la console Google Cloud.

Exécuter le workflow et vérifier les résultats

L'exécution d'un workflow exécute la définition actuelle du workflow associé au workflow.

  1. Exécutez le workflow comme suit :

    Console

    1. Dans Google Cloud Console, accédez à la page Workflows :

      Accéder à "Workflows"

    2. Sur la page Workflows (Workflows), sélectionnez le workflow read-bigquery-write-sheets pour accéder à sa page d'informations.

    3. Sur la page Détails du workflow, cliquez sur Exécuter.

    4. Cliquez à nouveau sur Exécuter.

    5. Affichez les résultats du workflow dans le volet Output (Résultat).

      La sortie devrait ressembler à ce qui suit :

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

    gcloud

    1. Ouvrez un terminal.

    2. Exécutez le workflow :

      gcloud workflows run read-bigquery-write-sheets

      Les résultats de l'exécution doivent ressembler à ce qui suit:

      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. Vérifiez que le workflow a écrit les résultats de la requête dans votre feuille de calcul. Par exemple, le nombre de colonnes et de lignes de la feuille de calcul doit correspondre aux valeurs updatedColumns et updatedRows.

Étape suivante