Gravar nas Planilhas Google de um fluxo de trabalho

O Planilhas Google é uma solução de planilha baseada na nuvem que oferece colaboração em tempo real e ferramentas para visualizar, processar e comunicar dados.

O exemplo a seguir demonstra como gravar no Planilhas a partir de um fluxo de trabalho. O fluxo de trabalho consulta um conjunto de dados do BigQuery e grava os resultados em uma planilha do Planilhas. Ele usa conectores do Workflows para simplificar a chamada de Google Cloud APIs.

Antes de começar

Antes de testar o exemplo neste documento, verifique se você concluiu o seguinte.

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

    Console

    Ativar as APIs

    gcloud 

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

  2. Anote a conta de serviço padrão do Compute Engine porque você vai associá-la ao fluxo de trabalho de exemplo para fins de teste. Os novos projetos que ativaram a API Compute Engine têm essa conta de serviço criada com o papel básico de editor do IAM e com o seguinte formato de e-mail:

    PROJECT_NUMBER-compute@developer.gserviceaccount.com

    Encontre o número do projeto na página Boas-vindas do Google Cloud console ou recupere o número do projeto:

    gcloud projects describe PROJECT_ID

    Para ambientes de produção, é altamente recomendável criar uma nova conta de serviço e conceder a ela um ou mais papéis do IAM que contenham as permissões mínimas necessárias e seguir o princípio de privilégio mínimo.

  3. Crie uma nova pasta no Google Drive. Essa pasta é usada para armazenar sua planilha. Ao configurar uma permissão para a pasta compartilhada, o fluxo de trabalho pode gravar na planilha.

    1. Acesse drive.google.com.
    2. Clique em Novo > Nova pasta.
    3. Digite um nome para a pasta.
    4. Clique em Criar.
    5. Clique com o botão direito do mouse na nova pasta e selecione Compartilhar.

    6. Adicione o endereço de e-mail da conta de serviço padrão do Compute Engine.

      Isso dá à conta de serviço acesso à pasta. Quando você associa a conta de serviço ao fluxo de trabalho, ele tem acesso de edição a qualquer arquivo na pasta. Saiba mais sobre como compartilhar arquivos, pastas e drives.

    7. Selecione o papel Editor.

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

    9. Clique em Compartilhar.

Criar uma planilha

É possível criar uma planilha de uma das seguintes maneiras:

Não há opção para criar uma planilha diretamente em uma pasta especificada usando a API Google Sheets. No entanto, há alternativas, incluindo mover a planilha para uma pasta específica depois de criá-la, como é feito nos exemplos a seguir. Para mais informações, consulte Trabalhar com pastas do Google Drive.

Criar uma planilha usando o Planilhas Google

Quando você cria uma planilha pelo Planilhas Google, ela é salva no Google Drive. Por padrão, a planilha é salva na pasta raiz do Drive.

  1. Acesse sheets.google.com.

  2. Clique em Novo Plus.

    A planilha será criada e aberta. Cada planilha tem um valor spreadsheetId exclusivo, contendo letras, números, hifens ou sublinhados. Você pode encontrar o ID da planilha em um URL do Planilhas Google:

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

  3. Anote esse ID, porque ele será necessário ao criar o fluxo de trabalho.

  4. Mova a planilha para a pasta do Google Drive que você criou anteriormente:

    1. Na planilha, selecione Arquivo > Mover.
    2. Navegue até a pasta que você criou.
    3. Clique em Mover.

Criar uma planilha usando o conector da API Google Sheets

Você pode usar o conector da API Google Sheets para criar uma planilha. Como o Workflows usa a conta de serviço como a identidade do acionador, a planilha é criada na pasta raiz do Google Drive da conta de serviço. Em seguida, você pode mover a planilha para outra pasta.

No fluxo de trabalho a seguir, o spreadsheetId é recuperado 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 você quer mover a planilha. Cada pasta do Drive tem um ID exclusivo que contém letras, números, hífens ou sublinhados. Você pode encontrar o ID da pasta no URL dela:

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

Para mais informações, consulte Criar e preencher pastas.

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

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

Conhecer o conjunto de dados público do BigQuery

O BigQuery hospeda vários conjuntos de dados públicos que estão disponíveis para consulta.

No BigQuery, é possível executar um job de consulta interativo (sob demanda). Por exemplo, a consulta a seguir retorna os 100 nomes mais populares em um conjunto de dados específico e grava a saída em uma tabela temporária. Essa é a consulta que o fluxo de trabalho vai executar.

Console

  1. No Google Cloud console, acesse a página BigQuery.

    Acessar o BigQuery

  2. Insira 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, insira o seguinte comando bq query para executar uma consulta interativa usando 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'

Implantar um fluxo de trabalho que grava no Planilhas

Implante um fluxo de trabalho que consulta um conjunto de dados do BigQuery usando o conector da API BigQuery e grava os resultados em uma planilha do Planilhas usando o conector da API Google Sheets.

Console

  1. No Google Cloud console, acesse a página Fluxos de trabalho:

    Acessar fluxos de trabalho

  2. Clique em Criar.

  3. Insira 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 padrão do Compute Engine service account (PROJECT_NUMBER-compute@developer.gserviceaccount.com).

  6. Clique em Próxima.

  7. No editor de fluxo de trabalho, insira a seguinte definição para 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 sheetId pelo seu spreadsheetId.

  9. Clique em Implantar.

gcloud

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

    touch read-bigquery-write-sheets.yaml

  2. Em um editor de texto, copie o fluxo de trabalho a seguir para o arquivo 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 sheetId pelo seu spreadsheetId.

  4. Implante o fluxo de trabalho digitando 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. Encontre o número do projeto na página Boas-vindas do Google Cloud console.

Executar o fluxo de trabalho e verificar os resultados

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

  1. Executar o fluxo de trabalho:

    Console

    1. No Google Cloud console, acesse a página Fluxos de trabalho:

      Acessar fluxos de trabalho

    2. Na página Fluxos de trabalho, selecione o fluxo de trabalho read-bigquery-write-sheets para acessar a página de detalhes dele.

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

    4. Clique em Executar novamente.

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

      A saída será semelhante a esta:

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

    gcloud

    1. Abra um terminal.

    2. Executar o fluxo de trabalho:

      gcloud workflows run read-bigquery-write-sheets

      Os resultados da execução serão semelhantes a estes:

      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 gravou os resultados da consulta na planilha. Por exemplo, o número de colunas e linhas na planilha precisa corresponder aos valores updatedColumns e updatedRows.

A seguir