# Writing to Google Sheets from Workflows

Author: [Mete Atamel](https://atamel.dev/) | Published: 2022-09-26 | Updated: 2022-09-29 | Topics: Workflows, Serverless, Google Sheets, Google Cloud Platform, How To | Canonical: https://atamel.dev/posts/2022/09-26_writing_google_sheets_from_workflows/


In my previous
[post](https://atamel.dev/posts/2022/09-09_trigger_workflows_from_sheets/), I
showed how to trigger a workflow in Google Cloud from a Google Sheets
spreadsheet using Apps Script. In this post, I show how to do the reverse: write
to Google Sheets from a workflow in Google Cloud.

## Use case

Imagine you have some dataset in BigQuery. Periodically, you want to query and
extract a subset of the dataset and save it to a Google Sheets spreadsheet. You
can implement such a process with Workflows quite easily.

![Architecture](https://github.com/GoogleCloudPlatform/workflows-demos/raw/master/workspace-integration/workflows-to-sheets/images/image0.png)

Let’s take a look at the steps in detail.

## Create a spreadsheet in Google Sheets

First, create a spreadsheet in Google Sheets that the workflow will write
results to.

Once the sheet is created, note the spreadsheet id; you will need this in the
workflow later. You can find the sheet id in the url of the spreadsheet:

![Google Sheets URL](https://github.com/GoogleCloudPlatform/workflows-demos/raw/master/workspace-integration/workflows-to-sheets/images/image1.png)

For simplicity, you will deploy the workflow with the default compute service
account. Find this service account email address in the `IAM & Admin` ->
`Service Accounts` section of Google Cloud Console:

![Service account email](https://github.com/GoogleCloudPlatform/workflows-demos/raw/master/workspace-integration/workflows-to-sheets/images/image2.png)

Make sure this service account has write permissions to the spreadsheet:

![Editor right for service account](https://github.com/GoogleCloudPlatform/workflows-demos/raw/master/workspace-integration/workflows-to-sheets/images/image3.png)

## Explore the public BigQuery dataset

For this sample, you will use the `usa_names.usa_1910_2013` public BigQuery
dataset, which contains information on individuals in the United States from
1910 to 2013.

You can see the first 100 rows with this query:

```sql
SELECT *
FROM `bigquery-public-data.usa_names.usa_1910_2013`
LIMIT 100
```

![BigQuery result](https://github.com/GoogleCloudPlatform/workflows-demos/raw/master/workspace-integration/workflows-to-sheets/images/image4.png)

You can find the 100 most popular names with this query:

```sql
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
```

![BigQuery result](https://github.com/GoogleCloudPlatform/workflows-demos/raw/master/workspace-integration/workflows-to-sheets/images/image5.png)

This is the query you will use in the sample workflow.

## Create a workflow

Create a
[workflow.yaml](https://github.com/GoogleCloudPlatform/workflows-demos/blob/master/workspace-integration/workflows-to-sheets/workflow.yaml)
to find the most popular names from the BigQuery public dataset and write to the
spreadsheet in Google Sheets.

First, define your sheet id and limit:

```yaml
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
```

Run the query against the BigQuery public dataset:

```yaml
   - 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
```

Initialize a rows list, parse the query results, and insert each row into the
rows list:

```yaml
   - 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)}
```

Finally, clear any existing values in the spreadsheet and insert the rows using
the [Google Sheets API
connector](https://cloud.google.com/workflows/docs/reference/googleapis/sheets/Overview)
of Workflows:

```yaml
   - 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}
```

## Deploy the workflow

Make sure you have a Google Cloud project and the project id is set in `gcloud`:

```sh
PROJECT_ID=your-project-id
gcloud config set project $PROJECT_ID
```

Run a
[setup.sh](https://github.com/GoogleCloudPlatform/workflows-demos/blob/master/workspace-integration/workflows-to-sheets/setup.sh)
script to enable required services, and deploy the workflow defined in
[workflow.yaml](https://github.com/GoogleCloudPlatform/workflows-demos/blob/master/workspace-integration/workflows-to-sheets/workflow.yaml) with the default compute service account.

## Run the worfklow

You're now ready to test the workflow.

Run the workflow from Google Cloud Console or `gcloud`:

```sh
gcloud workflows run read-bigquery-write-sheets
```

In a few seconds, you should see that the workflow execution has finished and
the spreadsheet has the results from the query of the BigQuery dataset:

![Google Sheets spreadsheet](https://github.com/GoogleCloudPlatform/workflows-demos/raw/master/workspace-integration/workflows-to-sheets/images/image6.png)

---

This is just one example of how to write to Google Sheets from Workflows using
the [Google Sheets API
connector](https://cloud.google.com/workflows/docs/reference/googleapis/sheets/Overview).
You can also use the [Google Forms API
connector](https://cloud.google.com/workflows/docs/reference/googleapis/forms/Overview),
which provides easy and interesting integration opportunities between
Workflows and Google Forms and a whole suite of [Google Workspace REST APIs](https://developers.google.com/workspace/products#toc) that
you can call from Workflows with relative ease even without a connector.

For questions or feedback, feel free to reach out to me on Twitter
[@meteatamel](https://twitter.com/meteatamel).

