Google Sheets is a cloud-based spreadsheet solution that supports real-time collaboration and provides tools to visualize, process, and communicate data.
This tutorial demonstrates how to create and deploy a workflow that creates a callback endpoint (or webhook), saves the callback URL to Google Sheets, pauses executing, and then waits for human approval through the Sheets spreadsheet to restart the workflow. Learn more about using callbacks.
Objectives
In this tutorial, you will:
- Create a new folder in Google Drive. This folder is used to store your spreadsheet and allows the workflow to write to the spreadsheet.
- Create a Google Sheets spreadsheet to capture an approval and initiate a callback to a workflow.
- Use Google Apps Script, a cloud-based JavaScript platform that lets you programmatically create, read, and edit Google Workspace products, to trigger the resumption of a paused workflow whenever a request is approved through an update to the spreadsheet.
- Create and deploy a workflow that calls the Google Sheets API connector to append data to the spreadsheet. The workflow executes, pauses, and then resumes when a callback is approved through the spreadsheet. Learn more about Workflows connectors.
- Test the entire process and confirm that the workflow proceeds as expected.
Costs
In this document, you use the following billable components of Google Cloud:
To generate a cost estimate based on your projected usage,
use the pricing calculator.
The tutorial also uses Google Workspace. Business-grade services that are not included with Google's free consumer apps are billable.
Before you begin
You can run some of the following commands in the Google Cloud console, or by using the Google Cloud CLI in either your terminal or Cloud Shell.
Security constraints defined by your organization might prevent you from completing the following steps. For troubleshooting information, see Develop applications in a constrained Google Cloud environment.
Console
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
Make sure that billing is enabled for your Google Cloud project. Learn how to check if billing is enabled on a project.
Enable the Compute Engine, Sheets, and Workflows APIs.
Make note of the Compute Engine default service account as you will associate it with the workflow in this tutorial 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.
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.
gcloud
In the Google Cloud console, activate Cloud Shell.
At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.
Make sure that billing is enabled for your Google Cloud project. Learn how to check if billing is enabled on a project.
Enable the Compute Engine, Sheets, and Workflows APIs.
gcloud services enable \ compute.googleapis.com \ sheets.googleapis.com \ workflows.googleapis.com
Make note of the Compute Engine default service account as you will associate it with the workflow in this tutorial 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 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
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 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. 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 this example. For more information, see Work with Google Drive folders.
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.
Add column headings to match the following example:
Note that the value in column G, Approved?, is used to initiate callbacks in the 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.
You can also use the
Google Sheets API connector
to create a spreadsheet. Note that when using the connector, the
spreadsheetId
can be retrieved from the resp
result.
For example:
- create_spreadsheet: call: googleapis.sheets.v4.spreadsheets.create args: body: connector_params: scopes: ${driveScope} result: resp - assign_sheet_id: assign: - sheetId: ${resp.spreadsheetId}
Extend Google Sheets using Apps Script
Apps Script lets you programmatically create, read, and edit Google Sheets. Most scripts designed for Sheets manipulate arrays to interact with the cells, rows, and columns in a spreadsheet. For an introduction to using Apps Script with Google Sheets, see the Custom function quickstart.
Create an Apps Script project from Google Sheets:
- Open your Sheets spreadsheet.
- Select Extensions > Apps Script.
- In the script editor, click Untitled project.
- Give your project a name and click Rename.
Your script is now bound to your spreadsheet, which gives the script special abilities to alter the user interface or respond when the spreadsheet is opened.
A script project represents a collection of Apps Script files and resources. Code files in a script project have a
.gs
extension.You can use Apps Script to write custom functions that you can use in Google Sheets just like a built-in function. Custom functions are created using standard JavaScript. Create a function:
- Open your Apps Script project.
- Click Editor .
- A script file appears as a project file named
Code.gs
. To edit the file, select it. Replace any code in the script editor with the following code which reads the data in your spreadsheet and passes it along as input to a workflow execution:
Click Save
.
Apps Script installable triggers let a script project execute a specified function when certain conditions are met, such as when a spreadsheet is opened or edited. Create a trigger:
- Open your Apps Script project.
- Click Triggers .
- Click Add Trigger.
- In the Add Trigger for YOUR_PROJECT_NAME dialog, configure the
trigger:
- In the Choose which function to run list, select handleEdit.
- In the Choose which deployment should run list, select Head.
- In the Select event source list, select From spreadsheet.
- In the Select event type list, select On edit.
- In the Failure notification settings list, select Notify me daily.
- Click Save.
If you receive a prompt to choose a Google Account, select the appropriate account, and then click Allow.
This allows your Apps Script project to see, edit, create, and delete your Google Sheets spreadsheets; and to connect to an external service.
An Apps Script project manifest file is a JSON file that specifies basic project information that Apps Script needs to run a script successfully. Note that the Apps Script editor hides manifest files by default to protect your Apps Script project settings. Edit the manifest file:
- Open your Apps Script project.
- Click Project Settings .
- Select the Show "appsscript.json" manifest file in editor checkbox.
- Click Editor .
- The manifest file appears as a project file named
appsscript.json
. To edit the file, select it. The
oauthScopes
field specifies an array of strings. To set the authorization scopes your project uses, add an array with the scopes you want supported. For example:{ "timeZone": "America/Toronto", "dependencies": { }, "exceptionLogging": "STACKDRIVER", "runtimeVersion": "V8", "oauthScopes": [ "https://www.googleapis.com/auth/script.external_request", "https://www.googleapis.com/auth/cloud-platform", "https://www.googleapis.com/auth/spreadsheets" ] }
This sets explicit scopes to:
- Connect to an external service
- See, edit, configure, and delete your Google Cloud data and see the email address for your Google Account
- See, edit, create, and delete all your Google Sheets spreadsheets
Click Save
.
Deploy a workflow that writes to a spreadsheet and uses callbacks
Deploy a workflow that executes, pauses, and then resumes when a callback is approved through a spreadsheet. The workflow writes 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:
workflows-awaits-callback-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:
Make sure to replace the placeholder
sheetId
value with yourspreadsheetId
.Click Deploy.
gcloud
Create a source code file for your workflow:
touch workflows-awaits-callback-sheets.yaml
In a text editor, copy the following workflow to your source code file:
Make sure to replace the placeholder
sheetId
value with yourspreadsheetId
.Deploy the workflow by entering the following command:
gcloud workflows deploy workflows-awaits-callback-sheets \ --source=workflows-awaits-callback-sheets.yaml \ --location=us-central1 \ --service-account=PROJECT_NUMBER-compute@developer.gserviceaccount.com
Replace
PROJECT_NUMBER
with your Google Cloud project number. You can retrieve your project number:gcloud projects describe PROJECT_ID
Test the end-to-end flow
Execute the workflow to test the end-to-end flow. Executing a workflow runs the current workflow definition associated with the workflow.
Console
In the Google Cloud console, go to the Workflows page:
On the Workflows page, select the workflows-awaits-callback-sheets workflow to go to its details page.
On the Workflow details page, click play_arrow Execute.
Click Execute again.
The workflow starts and its execution state should be Running. The logs also indicate that the workflow is paused and waiting:
Execute steps here before waiting for callback from sheets ... Started waiting for callback from sheet 1JlNFFnqs760M_KDqeeeDc_qtrABZDxoalyCmRE39dpM
Verify that the workflow has written the callback details to a row in your spreadsheet.
For example, you should see your workflow execution ID in the Execution ID column, a callback endpoint in the Callback URL column, and FALSE in the Approved? column.
In the spreadsheet, change the FALSE to TRUE.
After a minute or two, the execution should resume and then complete with an execution state of Succeeded.
gcloud
Open a terminal.
Execute the workflow:
gcloud workflows run workflows-awaits-callback-sheets
The workflow starts and the output should indicate that the workflow is paused and waiting:
Waiting for execution [a8361789-90e0-467f-8bd7-ea1c81977820] to complete...working.
Verify that the workflow has written the callback details to a row in your spreadsheet.
For example, you should see your workflow execution ID in the Execution ID column, a callback endpoint in the Callback URL column, and FALSE in the Approved? column.
In the spreadsheet, change the FALSE to TRUE.
After a minute or two, the execution should resume and then complete with an execution state of
SUCCEEDED
.
Clean up
If you created a new project for this tutorial, delete the project. If you used an existing project and wish to keep it without the changes added in this tutorial, delete resources created for the tutorial.
Delete the project
The easiest way to eliminate billing is to delete the project that you created for the tutorial.
To delete the project:
- In the Google Cloud console, go to the Manage resources page.
- In the project list, select the project that you want to delete, and then click Delete.
- In the dialog, type the project ID, and then click Shut down to delete the project.