Triggering Workflows from Google Sheets


Is it possible to integrate Google Workspace tools such as Calendar, Sheets, and Forms with Workflows? For example, can you trigger a workflow from a Google Form or a Sheet? Turns out, this is not only possible but also easier than you might think. Let me show you how with a sample use case.

Use case

Imagine you are an administrator in charge of allocating virtual machines (VM) in your cloud infrastructure to users. You want to capture user requests with the specifications for the VMs, have an approval step for the request, and then create the VM with an automated process.

You can implement such a process as follows:

  1. A Google Form captures the VM creation requests.
  2. A Google Sheet saves the responses from the Google Form with an approved checkbox.
  3. When a human selects the Approved checkbox, an Apps Script calls a workflow in Google Cloud with the VM details from the form.
  4. The workflow calls the Compute Engine API to create the VM for the user in Google Cloud.

Architecture

Let’s take a look at each step in more detail.

Create a Google Form

Create a Google Form to capture the VM creation request from users.

Go to Google Forms and create a new form similar to the following:

Google Form

Make sure you have the Collect email addresses option selected under settings. You’ll need an email address when creating the VM later.

Create a Google Sheet

Create a Google Sheet to capture the responses from the form. Under the Responses tab, click the Create Spreadsheet button.

This takes you to the spreadsheet with the responses. In this spreadsheet, add an Approved column with a checkbox:

Google Sheet

This checkbox will be used to trigger a workflow to create the VM.

Create an Apps Script

Create an Apps Script to watch for the Approved checkbox.

Go to Extensions and Apps Script in the spreadsheet. This opens up the Apps Script editor. Replace the default code in Code.gs with the code in Code.gs. Make sure you replace the PROJECT_ID with your own project id. This code watches for changes for the checkbox in the Approved column. When the checkbox is selected, it calls a workflow to create the VM.

In the Apps Script editor, go to Settings and check Show appsscript.json manifest file in editor. Replace the contents of appscript.json with appscript.json. This makes sure that the Apps Script has the required permissions.

Go to the Triggers section and create a trigger from the sheet to the Apps Script when the sheet is edited:

Edit trigger in Apps Script

Create a workflow

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

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

Run setup.sh to enable required services, grant necessary roles, and deploy the workflow defined in workflow.yaml. This workflow creates a Compute Engine VM with the supplied specifications.

Try it out

You’re now ready to test the end-to-end process.

Go back to the sheet and select the Approved checkbox for the entry created earlier:

Approved column in Google Sheets

In the Apps Script console, you can see a new execution:

Execution in the Apps Script console

In the Workflows console, you can also see a new execution:

Execution in the Workflows console

And finally, in the Compute Engine console, you can see that a new VM is being created:

VM in Compute Engine console

From now on, whenever a user fills the form, you’ll see a new entry to the Google Sheet. When you approve it by selecting the Approved checkbox, the VM will be created automatically by Workflows!


This is just one example of how to integrate Google Workspace with Workflows. If you have questions or feedback, feel free to reach out to me on Twitter @meteatamel. Thanks to our summer interns Cheran Mahalingam and Michael Yang, who validated the pattern and created the initial sample.


See also