Google Sheets is a cloud-based spreadsheet solution that supports real-time collaboration and provides tools to visualize, process, and communicate data.
The following example demonstrates how to write to Sheets from a workflow. The workflow queries a BigQuery dataset and writes the results to a Sheets spreadsheet. It uses Workflows connectors to simplify the calling of Google Cloud APIs.
Before you begin
Before trying out the example in this document, ensure that you have completed the following.
Enable the Compute Engine, Google Drive, Google Sheets, and Workflows APIs.
Console
gcloud
gcloud services enable \ compute.googleapis.com \ drive.googleapis.com \ sheets.googleapis.com \ workflows.googleapis.com
Make note of the Compute Engine default service account service account as you will associate it with the example workflow for testing purposes. New projects that have enabled the Compute Engine API have this service account created with the IAM basic Editor role, and with the following email format:
PROJECT_NUMBER-compute@developer.gserviceaccount.com
You can find your project number on the Welcome page of the Google Cloud console or you can retrieve your project number:
gcloud projects describe PROJECT_ID
For production environments, we strongly recommend creating a new service account and granting it one or more IAM roles that contain the minimum permissions required and follow the principle of least privilege.
Create a new folder in Google Drive. This folder is used to store your spreadsheet. By setting up a permission for the shared folder, your workflow is allowed to write to the spreadsheet.
- Go to drive.google.com.
- Click New > New folder.
- Enter a name for the folder.
- Click Create.
- Right-click your new folder and select Share.
Add the email address for the Compute Engine default service account.
This gives the service account access to the folder. When you associate the service account with your workflow, the workflow will have edit access to any file in the folder. Learn more about sharing files, folders & drives.
Select the Editor role.
Clear the Notify people checkbox.
Click Share.
Create a spreadsheet
You can create a spreadsheet in either one of the following ways:
There's no option to create a spreadsheet directly within a specified folder using the Google Sheets API. However, there are alternatives, including moving the spreadsheet to a specific folder after you create it, as is done in the following examples. For more information, see Work with Google Drive folders.
Create a spreadsheet using Google Sheets
When you create a spreadsheet through Google Sheets, it's saved in Google Drive. By default, the spreadsheet is saved to your root folder on Drive.
Go to sheets.google.com.
Click New .
This creates and opens your new spreadsheet. Every spreadsheet has a unique
spreadsheetId
value, containing letters, numbers, hyphens, or underscores. You can find the spreadsheet ID in a Google Sheets URL:https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=0
Note this ID as it will be needed when you create your workflow.
Move the spreadsheet to the Google Drive folder that you created previously:
- In the spreadsheet, select File > Move.
- Navigate to the folder you created.
- Click Move.
Create a spreadsheet using the Google Sheets API connector
You can use the Google Sheets API connector to create a spreadsheet. As Workflows uses the service account as the trigger identity, the spreadsheet is created in the service account's Google Drive root folder. You can then move the spreadsheet to another folder.
In the following workflow, the spreadsheetId
is
retrieved from the resp
result:
YAML
JSON
Replace FOLDER_ID
with the ID of the folder to which you
want to move the spreadsheet. Every Drive folder has a unique
ID, containing letters, numbers, hyphens, or underscores. You can find the
folder ID in the folder URL:
https://drive.google.com/drive/folders/FOLDER_ID/edit#gid=0
For more information, see Create and populate folders.
The output from the workflow should be similar to the following where the id
value is the spreadsheetId
:
"body": { "id": "spreadsheetId", "kind": "drive#file", "mimeType": "application/vnd.google-apps.spreadsheet", "name": "Untitled spreadsheet" }
Explore the BigQuery public dataset
BigQuery hosts a number of public datasets that are available to the general public to query.
In BigQuery, you can run an interactive (on-demand) query job. For example, the following query returns the 100 most popular names in a specific dataset and writes the output to a temporary table. This is the query that your workflow will run.
Console
In the Google Cloud console, go to the BigQuery page.
Enter the following BigQuery SQL query in the Query editor text area:
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
Click Run.
bq
In your terminal, enter the following bq query
command to run an
interactive query using standard SQL syntax:
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'
Deploy a workflow that writes to Sheets
Deploy a workflow that queries a BigQuery dataset using the BigQuery API connector and that writes the results to a Sheets spreadsheet using the Google Sheets API connector.
Console
In the Google Cloud console, go to the Workflows page:
Click
Create.Enter a name for the new workflow:
read-bigquery-write-sheets
.In the Region list, select us-central1 (Iowa).
For the Service account, select the Compute Engine default service account (
PROJECT_NUMBER-compute@developer.gserviceaccount.com
).Click Next.
In the workflow editor, enter the following definition for your workflow:
Replace the placeholder
sheetId
value with yourspreadsheetId
.Click Deploy.
gcloud
Create a source code file for your workflow:
touch read-bigquery-write-sheets.yaml
In a text editor, copy the following workflow to your source code file:
Replace the placeholder
sheetId
value with yourspreadsheetId
.Deploy the workflow by entering the following command:
gcloud workflows deploy read-bigquery-write-sheets \ --source=read-bigquery-write-sheets.yaml \ --location=us-central1 \ --service-account=PROJECT_NUMBER-compute@developer.gserviceaccount.com
Replace
PROJECT_NUMBER
with your Google Cloud project number. You can find your project number on the Welcome page of the Google Cloud console.
Execute the workflow and verify the results
Executing a workflow runs the current workflow definition associated with the workflow.
Run the workflow:
Console
In the Google Cloud console, go to the Workflows page:
On the Workflows page, select the read-bigquery-write-sheets workflow to go to its details page.
On the Workflow details page, click play_arrow Execute.
Click Execute again.
View the results of the workflow in the Output pane.
The output should be similar to the following:
{ "spreadsheetId": "1pgkNY7mWICA6o6INUNLHGnmk0Tdioh0nUghZPOYPpZA", "updatedCells": 303, "updatedColumns": 3, "updatedRange": "Sheet1!A1:C101", "updatedRows": 101 }
gcloud
Open a terminal.
Execute the workflow:
gcloud workflows run read-bigquery-write-sheets
The execution results should be similar to the following:
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
Verify that the workflow has written the results of the query to your spreadsheet. For example, the number of columns and rows in the spreadsheet should match the
updatedColumns
andupdatedRows
values.
What's next
- Trigger a workflow from Google Sheets.
- Pause and resume a workflow using callbacks and Google Sheets.
- Explore the Google Workspace Learning Center.