Console
    Contact Us Start free

    Write to Google Sheets from a workflow

    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.

    1. Enable the Compute Engine, Google Drive, Google Sheets, and Workflows APIs.

      Console

      Enable the APIs

      gcloud

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

    2. 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 .

    3. 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.

      1. Go to drive.google.com .
      2. Click New > New folder.
      3. Enter a name for the folder.
      4. Click Create.
      5. Right-click your new folder and select Share.

      6. 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 .

      7. Select the Editorrole.

      8. Clear the Notify peoplecheckbox.

      9. 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.

    1. Go to sheets.google.com .

    2. 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

    3. Note this ID as it will be needed when you create your workflow.

    4. Move the spreadsheet to the Google Drive folder that you created previously:

      1. In the spreadsheet, select File > Move.
      2. Navigate to the folder you created.
      3. 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

      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}" 
  
 } 
  
 } 
  
 ] 
  
 } 
 }

    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

    1. In the Google Cloud console, go to the BigQuerypage.

      Go to BigQuery

    2. Enter the following BigQuery SQL query in the Query editortext 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

    3. 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

    1. In the Google Cloud console, go to the Workflowspage:

      Go to Workflows

    2. Click Create.

    3. Enter a name for the new workflow: read-bigquery-write-sheets .

    4. In the Regionlist, select us-central1 (Iowa).

    5. For the Service account, select the Compute Engine default service account ( PROJECT_NUMBER -compute@developer.gserviceaccount.com ).

    6. Click Next.

    7. In the workflow editor, enter the following definition for your workflow:

        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. Replace the placeholder sheetId value with your spreadsheetId .

    9. Click Deploy.

    gcloud

    1. Create a source code file for your workflow:

      touch  
read-bigquery-write-sheets.yaml

    2. In a text editor, copy the following workflow to your source code file:

        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. Replace the placeholder sheetId value with your spreadsheetId .

    4. 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.

    1. Run the workflow:

      Console

      1. In the Google Cloud console, go to the Workflowspage:

        Go to Workflows

      2. On the Workflowspage, select the read-bigquery-write-sheetsworkflow to go to its details page.

      3. On the Workflow detailspage, click Execute.

      4. Click Executeagain.

      5. View the results of the workflow in the Outputpane.

        The output should be similar to the following:

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

      gcloud

      1. Open a terminal.

      2. 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

    2. 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 and updatedRows values.

    What's next
    Create a Mobile Website
    View Site in Mobile | Classic
    Share by: