Gravar no Planilhas Google usando um fluxo de trabalho

O Planilhas Google é uma solução de gerenciamento de planilhas que oferece suporte à colaboração em tempo real e fornece ferramentas para visualizar, processar e comunicar dados.

O exemplo a seguir demonstra como gravar no Planilhas 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. Ela usa Os conectores de fluxos de trabalho para simplificar chamadas de APIs do Google Cloud.

Antes de começar

Antes de testar o exemplo neste documento, certifique-se de ter concluído o seguinte.

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

    Console

    Ative as APIs

    gcloud 

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

  2. Anote os valores Conta de serviço padrão do Compute Engine conta de serviço. Você vai associá-la ao fluxo de trabalho de exemplo para teste propósitos. Os novos projetos que ativaram a API Compute Engine têm a conta de serviço criada com o papel de Editor básico do IAM, e com o seguinte formato de e-mail:

    PROJECT_NUMBER-compute@developer.gserviceaccount.com

    O número do projeto está na página Olá! do console do Google Cloud ou recupere o número do projeto:

    gcloud projects describe PROJECT_ID

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

  3. Crie uma pasta no Google Drive. Ela é usada para armazenar planilha. Ao configurar uma permissão para a pasta compartilhada, seu fluxo de trabalho tem permissão para 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. Adicionar o endereço de e-mail do serviço padrão do Compute Engine do Compute Engine.

      Isso concede à conta de serviço acesso à pasta. Ao associar a conta de serviço com seu fluxo de trabalho, o fluxo de trabalho terá edições acesso a qualquer arquivo na pasta. Saiba mais sobre como compartilhar arquivos, pastas e unidade de disco rígido.

    7. Selecione o papel Editor.

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

    9. Clique em Compartilhar.

Criar uma planilha

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

Não há opção para criar uma planilha diretamente em uma pasta específica usando a API Google Sheets. No entanto, existem alternativas, incluindo mover a planilha para um diretório depois de criá-la, como 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 no Planilhas Google, ela é salva em Google Drive Por padrão, a planilha é salva na sua pasta raiz em Google Drive.

  1. Acesse sheets.google.com.

  2. Clique em Novo Mais.

    A nova planilha será criada e aberta. Toda planilha tem um valor Valor spreadsheetId, que contenham letras, números, hifens ou sublinhados. Encontre a ID de planilha em um URL do Planilhas Google:

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

  3. Anote esse ID, ele será necessário para criar seu 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 identidade do acionador, a planilha é criada no conta de serviço Pasta raiz do Google Drive. Em seguida, você pode mover a planilha para outro do Compute Engine.

No fluxo de trabalho a seguir, o spreadsheetId é recuperados 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ê mover a planilha. Cada pasta do Drive tem uma pasta ID, contendo letras, números, hifens ou sublinhados. Encontre a ID da pasta no URL da pasta:

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 id é o spreadsheetId:

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

analise o conjunto de dados público do BigQuery

O BigQuery hospeda vários conjuntos de dados públicos disponíveis para consulta do público em geral.

No BigQuery, é possível executar job de consulta interativa (sob demanda). Para exemplo, a consulta a seguir retorna os 100 nomes mais populares em um determinado e grava a saída em uma tabela temporária. Essa é a consulta será executado.

Console

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

    Acessar o BigQuery

  2. Digite a seguinte consulta SQL do BigQuery no Á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 seu terminal, digite 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 app Planilhas

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

Console

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

    Acessar fluxos de trabalho

  2. Clique em Criar.

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

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

  5. Em Conta de serviço, selecione o padrão do Compute Engine conta de serviço (PROJECT_NUMBER-compute@developer.gserviceaccount.com).

  6. Clique em Next.

  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 seu 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 número do projeto do Google Cloud. O número do projeto está na página Olá! do console do Google Cloud.

Executar o fluxo de trabalho e verificar os resultados

Quando um fluxo de trabalho é executado, a definição atual associada a ele também é.

  1. Execute o fluxo de trabalho:

    Console

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

      Acessar fluxos de trabalho

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

    3. Na página Detalhes do fluxo de trabalho, clique em Execução.

    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. Execute o fluxo de trabalho:

      gcloud workflows run read-bigquery-write-sheets

      Os resultados da execução vão ser semelhantes aos mostrados a seguir:

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

A seguir