# Triggering Workflows from Google Sheets

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


Is it possible to integrate [Google Workspace](https://workspace.google.com/)
tools such as Calendar, Sheets, and Forms with
[Workflows](https://cloud.google.com/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.
1. A Google Sheet saves the responses from the Google Form with an approved checkbox.
1. When a human selects the Approved checkbox, an [Apps
   Script](https://developers.google.com/apps-script) calls a workflow in Google
   Cloud with the VM details from the form.
1. The workflow calls the Compute Engine API to create the VM for the user in Google Cloud.

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

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](https://docs.google.com/forms) and create a new form
similar to the
[following](https://docs.google.com/forms/d/e/1FAIpQLSercSIn9KxVwCCADAC3vI6zNMBI42Zh7Nzba1_K6m24P_opqA/viewform?usp=sf_link):

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

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](https://raw.githubusercontent.com/GoogleCloudPlatform/workflows-demos/master/workspace-integration/sheets-to-workflows/images/image2.png)

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](https://github.com/GoogleCloudPlatform/workflows-demos/blob/master/workspace-integration/sheets-to-workflows/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](https://github.com/GoogleCloudPlatform/workflows-demos/blob/master/workspace-integration/sheets-to-workflows/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](https://raw.githubusercontent.com/GoogleCloudPlatform/workflows-demos/master/workspace-integration/sheets-to-workflows/images/image3.png)

## Create a 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 [setup.sh](https://github.com/GoogleCloudPlatform/workflows-demos/blob/master/workspace-integration/sheets-to-workflows/setup.sh) to enable required services, grant necessary roles, and deploy the
workflow defined in [workflow.yaml](https://github.com/GoogleCloudPlatform/workflows-demos/blob/master/workspace-integration/sheets-to-workflows/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](https://raw.githubusercontent.com/GoogleCloudPlatform/workflows-demos/master/workspace-integration/sheets-to-workflows/images/image4.png)

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

![Execution in the Apps Script console](https://raw.githubusercontent.com/GoogleCloudPlatform/workflows-demos/master/workspace-integration/sheets-to-workflows/images/image5.png)

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

![Execution in the Workflows console](https://raw.githubusercontent.com/GoogleCloudPlatform/workflows-demos/master/workspace-integration/sheets-to-workflows/images/image6.png)

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

![VM in Compute Engine console](https://raw.githubusercontent.com/GoogleCloudPlatform/workflows-demos/master/workspace-integration/sheets-to-workflows/images/image7.png)

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](https://twitter.com/meteatamel). Thanks to our summer interns
Cheran Mahalingam and Michael Yang, who validated the pattern and created the
initial sample.