Escribir en Hojas de cálculo de Google desde un flujo de trabajo

Hojas de cálculo de Google es una solución de hojas de cálculo basada en la nube que admite la colaboración en tiempo real y proporciona herramientas para visualizar, procesar y comunicar datos.

En el siguiente ejemplo se muestra cómo escribir en Hojas de cálculo desde un flujo de trabajo. El flujo de trabajo consulta un conjunto de datos de BigQuery y escribe los resultados en una hoja de cálculo. Usa conectores de Workflows para simplificar las llamadas a las APIs de Google Cloud .

Antes de empezar

Antes de probar el ejemplo de este documento, asegúrate de que has completado lo siguiente.

  1. Habilita las APIs Compute Engine, Google Drive, Hojas de cálculo de Google y Workflows.

    Consola

    Habilita las APIs

    gcloud

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

  2. Anota la cuenta de servicio predeterminada de Compute Engine, ya que la asociarás al flujo de trabajo de ejemplo para hacer pruebas. Los proyectos nuevos en los que se ha habilitado la API Compute Engine tienen esta cuenta de servicio creada con el rol básico de editor de IAM y con el siguiente formato de correo:

    PROJECT_NUMBER-compute@developer.gserviceaccount.com

    Puedes encontrar el número de tu proyecto en la página Bienvenido de la consola Google Cloud o puedes obtenerlo de la siguiente forma:

    gcloud projects describe PROJECT_ID

    En los entornos de producción, te recomendamos que crees una cuenta de servicio y le asignes uno o varios roles de IAM que contengan los permisos mínimos necesarios y que sigas el principio de privilegio mínimo.

  3. Crea una carpeta en Google Drive. Esta carpeta se usa para almacenar tu hoja de cálculo. Al configurar un permiso para la carpeta compartida, tu flujo de trabajo podrá escribir en la hoja de cálculo.

    1. Ve a drive.google.com.
    2. Haz clic en Nuevo > Nueva carpeta.
    3. Asigna un nombre a la carpeta.
    4. Haz clic en Crear.
    5. Haz clic con el botón derecho en la carpeta nueva y selecciona Compartir.

    6. Añade la dirección de correo de la cuenta de servicio predeterminada de Compute Engine.

      De esta forma, la cuenta de servicio tendrá acceso a la carpeta. Cuando asocies la cuenta de servicio a tu flujo de trabajo, este tendrá acceso de edición a cualquier archivo de la carpeta. Más información sobre cómo compartir archivos, carpetas y unidades

    7. Selecciona el rol Editor.

    8. Desmarca la casilla Notificar a los usuarios.

    9. Haz clic en Compartir.

Crear una hoja de cálculo

Puedes crear una hoja de cálculo de cualquiera de las siguientes formas:

No hay ninguna opción para crear una hoja de cálculo directamente en una carpeta específica mediante la API Hojas de cálculo de Google. Sin embargo, hay alternativas, como mover la hoja de cálculo a una carpeta específica después de crearla, como se hace en los siguientes ejemplos. Para obtener más información, consulta el artículo Trabajar con carpetas de Google Drive.

Crear una hoja de cálculo con Hojas de cálculo de Google

Cuando creas una hoja de cálculo con Hojas de cálculo de Google, se guarda en Google Drive. De forma predeterminada, la hoja de cálculo se guarda en la carpeta raíz de Drive.

  1. Ve a sheets.google.com.

  2. Haz clic en Nuevo Plus.

    Al hacerlo, se abrirá una hoja de cálculo nueva. Cada hoja de cálculo tiene un valor spreadsheetId único que contiene letras, números, guiones o guiones bajos. Puedes encontrar el ID de hoja de cálculo en una URL de Hojas de cálculo de Google:

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

  3. Anota este ID, ya que lo necesitarás cuando crees el flujo de trabajo.

  4. Mueve la hoja de cálculo a la carpeta de Google Drive que has creado anteriormente:

    1. En la hoja de cálculo, selecciona Archivo > Mover.
    2. Ve a la carpeta que has creado.
    3. Haz clic en Mover.

Crear una hoja de cálculo con el conector de la API de Hojas de cálculo de Google

Puedes usar el conector de la API de Hojas de cálculo de Google para crear una hoja de cálculo. Como Workflows usa la cuenta de servicio como identidad del activador, la hoja de cálculo se crea en la carpeta raíz de Google Drive de la cuenta de servicio. Después, puedes mover la hoja de cálculo a otra carpeta.

En el siguiente flujo de trabajo, el spreadsheetId se obtiene del 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}"
        }
      }
    ]
  }
}

Sustituye FOLDER_ID por el ID de la carpeta a la que quieras mover la hoja de cálculo. Cada carpeta de Drive tiene un ID único que contiene letras, números, guiones o guiones bajos. Puedes encontrar el ID de la carpeta en la URL de la carpeta:

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

Para obtener más información, consulta el artículo Crear y rellenar carpetas.

La salida del flujo de trabajo debería ser similar a la siguiente, donde el valor de id es spreadsheetId:

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

Consultar el conjunto de datos público de BigQuery

BigQuery aloja varios conjuntos de datos públicos que están disponibles para que el público en general los consulte.

En BigQuery, puedes ejecutar una tarea de consulta interactiva (bajo demanda). Por ejemplo, la siguiente consulta devuelve los 100 nombres más populares de un conjunto de datos específico y escribe el resultado en una tabla temporal. Esta es la consulta que ejecutará tu flujo de trabajo.

Consola

  1. En la Google Cloud consola, ve a la página BigQuery.

    Ir a BigQuery

  2. Introduce la siguiente consulta de SQL de BigQuery en el área de texto 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. Haz clic en Ejecutar.

bq

En el terminal, introduce el siguiente comando bq query para ejecutar una consulta interactiva con la sintaxis de SQL estándar:

    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'

Desplegar un flujo de trabajo que escriba en Hojas de cálculo

Implementa un flujo de trabajo que consulte un conjunto de datos de BigQuery mediante el conector de la API BigQuery y que escriba los resultados en una hoja de cálculo de Hojas de cálculo mediante el conector de la API Google Sheets.

Consola

  1. En la Google Cloud consola, ve a la página Flujos de trabajo:

    Ve a Workflows

  2. Haz clic en Crear.

  3. Escribe un nombre para el nuevo flujo de trabajo: read-bigquery-write-sheets.

  4. En la lista Región, selecciona us-central1 (Iowa).

  5. En Cuenta de servicio, selecciona la cuenta de servicio predeterminada de Compute Engine (PROJECT_NUMBER-compute@developer.gserviceaccount.com).

  6. Haz clic en Siguiente.

  7. En el editor del flujo de trabajo, introduce la siguiente definición para tu flujo de trabajo:

    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. Sustituye el valor del marcador de posición sheetId por tu spreadsheetId.

  9. Haz clic en Desplegar.

gcloud

  1. Crea un archivo de código fuente para tu flujo de trabajo:

    touch read-bigquery-write-sheets.yaml

  2. En un editor de texto, copia el siguiente flujo de trabajo en tu archivo de código fuente:

    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. Sustituye el valor del marcador de posición sheetId por tu spreadsheetId.

  4. Para desplegar el flujo de trabajo, introduce el siguiente 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

    Sustituye PROJECT_NUMBER por el número de tu proyecto. Google Cloud Puedes encontrar el número de tu proyecto en la página Bienvenido de la consola de Google Cloud .

Ejecutar el flujo de trabajo y verificar los resultados

Al ejecutar un flujo de trabajo, se ejecuta la definición del flujo de trabajo actual asociada al flujo de trabajo.

  1. Ejecuta el flujo de trabajo:

    Consola

    1. En la Google Cloud consola, ve a la página Flujos de trabajo:

      Ve a Workflows

    2. En la página Flujos de trabajo, selecciona el flujo de trabajo read-bigquery-write-sheets para ir a su página de detalles.

    3. En la página Detalles del flujo de trabajo, haz clic en Ejecutar.

    4. Vuelve a hacer clic en Ejecutar.

    5. Consulta los resultados del flujo de trabajo en el panel Información.

      La salida debería ser similar a la siguiente:

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

    gcloud

    1. Abre la terminal.

    2. Ejecuta el flujo de trabajo:

      gcloud workflows run read-bigquery-write-sheets

      Los resultados de la ejecución deberían ser similares a los siguientes:

      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. Comprueba que el flujo de trabajo haya escrito los resultados de la consulta en tu hoja de cálculo. Por ejemplo, el número de columnas y filas de la hoja de cálculo debe coincidir con los valores updatedColumns y updatedRows.

Siguientes pasos