Escreva no Google Sheets a partir de um fluxo de trabalho

O Google Sheets é uma solução de folha de cálculo baseada na nuvem que suporta a colaboração em tempo real e oferece ferramentas para visualizar, processar e comunicar dados.

O exemplo seguinte demonstra como escrever no Sheets a partir de um fluxo de trabalho. O fluxo de trabalho consulta um conjunto de dados do BigQuery e escreve os resultados numa folha de cálculo do Sheets. Usa os conetores do Workflows para simplificar a chamada de Google Cloud APIs.

Antes de começar

Antes de experimentar o exemplo neste documento, certifique-se de que concluiu o seguinte.

  1. Ative as APIs Compute Engine, Google Drive, Google Sheets e Workflows.

    Consola

    Ative as APIs

    gcloud 

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

  2. Tome nota da conta de serviço predefinida do Compute Engine, uma vez que a vai associar ao fluxo de trabalho de exemplo para fins de teste. Os novos projetos que ativaram a API Compute Engine têm esta conta de serviço criada com a função básica de editor do IAM e com o seguinte formato de email:

    PROJECT_NUMBER-compute@developer.gserviceaccount.com

    Pode encontrar o número do projeto na página Boas-vindas da Google Cloud consola ou pode obter o número do projeto:

    gcloud projects describe PROJECT_ID

    Para ambientes de produção, recomendamos vivamente que crie uma nova conta de serviço e lhe conceda uma ou mais funções do IAM que contenham as autorizações mínimas necessárias e siga o princípio do privilégio mínimo.

  3. Crie uma nova pasta no Google Drive. Esta pasta é usada para armazenar a sua folha de cálculo. Ao configurar uma autorização para a pasta partilhada, o seu fluxo de trabalho tem permissão para escrever na folha de cálculo.

    1. Aceda a drive.google.com.
    2. Clique em Novo > Nova pasta.
    3. Introduza um nome para a pasta.
    4. Clique em Criar.
    5. Clique com o botão direito do rato na nova pasta e selecione Partilhar.

    6. Adicione o endereço de email da conta de serviço predefinida do Compute Engine.

      Isto concede à conta de serviço acesso à pasta. Quando associa a conta de serviço ao seu fluxo de trabalho, o fluxo de trabalho tem acesso de edição a qualquer ficheiro na pasta. Saiba mais sobre a partilha de ficheiros, pastas e discos.

    7. Selecione a função Editor.

    8. Desmarque a caixa de verificação Notificar pessoas.

    9. Clique em Partilhar.

Crie uma folha de cálculo

Pode criar uma folha de cálculo de uma das seguintes formas:

Não existe uma opção para criar uma folha de cálculo diretamente numa pasta especificada através da API Google Sheets. No entanto, existem alternativas, incluindo mover a folha de cálculo para uma pasta específica depois de a criar, como é feito nos exemplos seguintes. Para mais informações, consulte o artigo Trabalhe com pastas do Google Drive.

Crie uma folha de cálculo com o Google Sheets

Quando cria uma folha de cálculo através do Google Sheets, esta é guardada no Google Drive. Por predefinição, a folha de cálculo é guardada na pasta raiz do Drive.

  1. Aceda a sheets.google.com.

  2. Clique em Novo Plus.

    Isto cria e abre a sua nova folha de cálculo. Cada folha de cálculo tem um valor spreadsheetId exclusivo, com letras, números, hífenes ou sublinhados. Pode encontrar o ID da folha de cálculo num URL do Google Sheets:

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

  3. Tome nota deste ID, pois vai precisar dele quando criar o fluxo de trabalho.

  4. Mova a folha de cálculo para a pasta do Google Drive que criou anteriormente:

    1. Na folha de cálculo, selecione Ficheiro > Mover.
    2. Navegue até à pasta que criou.
    3. Clique em Mover.

Crie uma folha de cálculo com o conetor da API Google Sheets

Pode usar o conetor da API Google Sheets para criar uma folha de cálculo. Como os fluxos de trabalho usam a conta de serviço como a identidade do acionador, a folha de cálculo é criada na pasta raiz do Google Drive da conta de serviço. Em seguida, pode mover a folha de cálculo para outra pasta.

No fluxo de trabalho seguinte, o spreadsheetId é obtido a partir do resultado 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}"
        }
      }
    ]
  }
}

Substitua FOLDER_ID pelo ID da pasta para a qual quer mover a folha de cálculo. Cada pasta do Drive tem um ID exclusivo com letras, números, hífenes ou sublinhados. Pode encontrar o ID da pasta no URL da pasta:

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

Para mais informações, consulte o artigo Crie e preencha pastas.

A saída do fluxo de trabalho deve ser semelhante à seguinte, em que o valor id é spreadsheetId:

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

Explore o conjunto de dados público do BigQuery

O BigQuery aloja vários conjuntos de dados públicos que estão disponíveis para consulta pelo público em geral.

No BigQuery, pode executar uma tarefa de consulta interativa (a pedido). Por exemplo, a seguinte consulta devolve os 100 nomes mais populares num conjunto de dados específico e escreve o resultado numa tabela temporária. Esta é a consulta que o seu fluxo de trabalho vai executar.

Consola

  1. Na Google Cloud consola, aceda à página BigQuery.

    Aceda ao BigQuery

  2. Introduza a seguinte consulta SQL do BigQuery na área de texto do editor de consultas:

    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. Clique em Executar.

bq

No terminal, introduza o seguinte comando bq query para executar uma consulta interativa com a sintaxe SQL padrão:

    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'

Implemente um fluxo de trabalho que escreva no Sheets

Implemente um fluxo de trabalho que consulte um conjunto de dados do BigQuery através do conetor da API BigQuery e que escreva os resultados numa folha de cálculo do Sheets através do conetor da API Google Sheets.

Consola

  1. Na Google Cloud consola, aceda à página Fluxos de trabalho:

    Aceda a Fluxos de trabalho

  2. Clique em Criar.

  3. Introduza um nome para o novo fluxo de trabalho: read-bigquery-write-sheets.

  4. Na lista Região, selecione us-central1 (Iowa).

  5. Para a Conta de serviço, selecione a conta de serviço predefinida do Compute Engine (PROJECT_NUMBER-compute@developer.gserviceaccount.com).

  6. Clicar em Seguinte.

  7. No editor de fluxos de trabalho, introduza a seguinte definição para o seu fluxo de trabalho:

    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. Substitua o valor do marcador de posição sheetId pelo seu spreadsheetId.

  9. Clique em Implementar.

gcloud

  1. Crie um ficheiro de código-fonte para o seu fluxo de trabalho:

    touch read-bigquery-write-sheets.yaml

  2. Num editor de texto, copie o seguinte fluxo de trabalho para o ficheiro de código-fonte:

    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. Substitua o valor do marcador de posição sheetId pelo seu spreadsheetId.

  4. Implemente o fluxo de trabalho introduzindo o seguinte 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

    Substitua PROJECT_NUMBER pelo seu Google Cloud número do projeto. Pode encontrar o número do projeto na página Boas-vindas da Google Cloud consola.

Execute o fluxo de trabalho e valide os resultados

A execução de um fluxo de trabalho executa a definição do fluxo de trabalho atual associada ao fluxo de trabalho.

  1. Execute o fluxo de trabalho:

    Consola

    1. Na Google Cloud consola, aceda à página Fluxos de trabalho:

      Aceda a Fluxos de trabalho

    2. Na página Fluxos de trabalho, selecione o fluxo de trabalho read-bigquery-write-sheets para aceder à respetiva página de detalhes.

    3. Na página Detalhes do fluxo de trabalho, clique em Executar.

    4. Clique novamente em Executar.

    5. Veja os resultados do fluxo de trabalho no painel Saída.

      O resultado deve ser semelhante ao seguinte:

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

    gcloud

    1. Abra um terminal.

    2. Execute o fluxo de trabalho:

      gcloud workflows run read-bigquery-write-sheets

      Os resultados da execução devem ser semelhantes aos seguintes:

      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. Verifique se o fluxo de trabalho escreveu os resultados da consulta na sua folha de cálculo. Por exemplo, o número de colunas e linhas na folha de cálculo deve corresponder aos valores updatedColumns e updatedRows.

O que se segue?