# Workflows that pause and wait for human approvals from Google Sheets

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


I’ve been writing a series of posts to showcase [Google
Workspace](https://workspace.google.com/) and Google Cloud
[Workflows](https://cloud.google.com/workflows) integration.

In my [first
post](https://atamel.dev/posts/2022/09-09_trigger_workflows_from_sheets/), I
showed an IT automation use case in which a Google Sheets spreadsheet triggers a
workflow to create virtual machines in Google Cloud. In the [second
post](https://atamel.dev/posts/2022/09-26_writing_google_sheets_from_workflows/),
I showed how to feed a Google Sheets spreadsheet with data from BigQuery using a
workflow.

In this third and final post of the series, I show how to design a workflow that
pauses and waits for human approvals from Google Sheets.

## Use case

With Workflows
[callbacks](https://cloud.google.com/workflows/docs/creating-callback-endpoints),
a workflow can create an HTTP endpoint and pause execution until it receives an
HTTP callback to that endpoint. This is very useful for creating
human-in-the-middle type workflows. However, there can be quite a bit of setup
needed before a human is able to send that approval. The workflow has to create
the callback URL, then the workflow must signal in some way what that URL is to
a frontend, and finally the frontend has to enable the user to approve and send
the callback.

**Is there an easier way to enable users to send these approval callbacks?**
Sure, there is! You can use Google Sheets as the approval frontend.

Here’s the idea:

1. A workflow runs some initial steps.
1. The workflow creates a callback, saves the callback info to a Google Sheets
   spreadsheet, and starts to wait for an approval from a human for its
   remaining steps.
1. A human sends his/her approval via the Google Sheets spreadsheet.
1. The workflow receives the approval and runs the rest of the steps.

![Architecture](https://github.com/GoogleCloudPlatform/workflows-demos/blob/master/workspace-integration/workflows-awaits-sheets-callback/images/image0.png?raw=true)

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

## Create a Google Sheet spreadsheet

First, create a Google Sheet spreadsheet to capture callback approval requests.
The spreadsheet can contain any info you deem necessary for the approval
request.

Here's an example spreadsheet:

![Approval spreadsheet](https://github.com/GoogleCloudPlatform/workflows-demos/blob/master/workspace-integration/workflows-awaits-sheets-callback/images/image1.png?raw=true)

The `Approved` column will be used to initiate a callback to the workflow.

Once the sheet is created, note the spreadsheet id, which you will need in the
workflow later. You can find the sheet id in the URL of the spreadsheet:

![Spreadsheet id](https://github.com/GoogleCloudPlatform/workflows-demos/blob/master/workspace-integration/workflows-to-sheets/images/image1.png?raw=true)

Later, you will deploy the workflow with the default compute service account for simplicity. Find this service account email address by visiting the `IAM & Admin -> Service Accounts` section of Google Cloud Console:

![Default compute service account](https://github.com/GoogleCloudPlatform/workflows-demos/blob/master/workspace-integration/workflows-to-sheets/images/image2.png?raw=true)

Make sure the service account has write permissions to the spreadsheet:

![Sharing page](https://github.com/GoogleCloudPlatform/workflows-demos/blob/master/workspace-integration/workflows-to-sheets/images/image3.png?raw=true)

## Create an Apps Script

Create an Apps Script to watch for changes in the `Approved` column.

Go to `Extensions` and `Apps Script` in the spreadsheet. This opens 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/workflows-awaits-sheets-callback/Code.gs)
and click `Save`.

This code watches for changes in the `Approved` column. When a cell in this
column is set to `TRUE`, it calls the workflow's callback URL with the approver
information.

In the Apps Script editor, go to `Settings` and select `Show appsscript.json
manifest file in editor`. Replace the contents of `appsscript.json` with
[appsscript.json](https://github.com/GoogleCloudPlatform/workflows-demos/blob/master/workspace-integration/workflows-awaits-sheets-callback/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:

![Create trigger](https://github.com/GoogleCloudPlatform/workflows-demos/blob/master/workspace-integration/workflows-awaits-sheets-callback/images/image2.png?raw=true)

## Create a workflow

Create a
[workflow.yaml](https://github.com/GoogleCloudPlatform/workflows-demos/blob/master/workspace-integration/workflows-awaits-sheets-callback/workflow.yaml)
to run some initial steps, wait for the callback, and (once the call back is
received) run some more steps. Make sure you replace the sheet id with your own:

```yaml
main:
  steps:
    - init:
        assign:
        # Replace with your sheetId and make sure the service account
        # for the workflow has write permissions to the sheet
        - sheetId: "10hieAH6b-oMeIVT_AerSLNxQck14IGhgi8ign-x2x8g"
    - before_sheets_callback:
        call: sys.log
        args:
          severity: INFO
          data: ${"Execute steps here before waiting for callback from sheets"}
    - wait_for_sheets_callback:
        call: await_callback_sheets
        args:
          sheetId: ${sheetId}
        result: await_callback_result
    - after_sheets_callback:
        call: sys.log
        args:
          severity: INFO
          data: ${"Execute steps here after receiving callback from sheets"}
    - returnResult:
        return: ${await_callback_result}
```

The `await_callback_sheets` sub-workflow receives a sheet id, creates a
callback, saves the callback to Google Sheets, and waits for the callback:

```yaml
await_callback_sheets:
    params: [sheetId]
    steps:
        - init:
            assign:
              - project_id: ${sys.get_env("GOOGLE_CLOUD_PROJECT_ID")}
              - location: ${sys.get_env("GOOGLE_CLOUD_LOCATION")}
              - workflow_id: ${sys.get_env("GOOGLE_CLOUD_WORKFLOW_ID")}
              - execution_id: ${sys.get_env("GOOGLE_CLOUD_WORKFLOW_EXECUTION_ID")}
        - create_callback:
            call: events.create_callback_endpoint
            args:
              http_callback_method: POST
            result: callback_details
        - save_callback_to_sheets:
            call: googleapis.sheets.v4.spreadsheets.values.append
            args:
                range: ${"Sheet1!A1:G1"}
                spreadsheetId: ${sheetId}
                valueInputOption: RAW
                body:
                    majorDimension: "ROWS"
                    values:
                      - ["${project_id}", "${location}", "${workflow_id}", "${execution_id}", "${callback_details.url}", "", "FALSE"]
        - log_and_await_callback:
            try:
              steps:
                - log_await_start:
                    call: sys.log
                    args:
                      severity: INFO
                      data: ${"Started waiting for callback from sheet " + sheetId}
                - await_callback:
                    call: events.await_callback
                    args:
                      callback: ${callback_details}
                      timeout: 3600
                    result: callback_request
                - log_await_stop:
                    call: sys.log
                    args:
                      severity: INFO
                      data: ${"Stopped waiting for callback from sheet " + sheetId}
            except:
                as: e
                steps:
                    - log_error:
                        call: sys.log
                        args:
                            severity: "ERROR"
                            text: ${"Received error " + e.message}
        - check_null_await_result:
            switch:
              - condition: ${callback_request == null}
                return: null
        - log_await_result:
            call: sys.log
            args:
              severity: INFO
              data: ${"Approved by " + callback_request.http_request.body.approver}
        - return_await_result:
            return: ${callback_request.http_request.body}
```

## Deploy the 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/workflows-awaits-sheets-callback/setup.sh)
to enable required services and deploy the workflow defined in
[workflow.yaml](https://github.com/GoogleCloudPlatform/workflows-demos/blob/master/workspace-integration/workflows-awaits-sheets-callback/workflow.yaml).

## Run the workflow

You're now ready to test the end-to-end flow.

Run the workflow from Google Cloud Console or `gcloud`:

```sh
gcloud workflows run workflows-awaits-callback-sheets
```

You should see the workflow is running and waiting for the callback:

![Workflow in waiting state](https://github.com/GoogleCloudPlatform/workflows-demos/blob/master/workspace-integration/workflows-awaits-sheets-callback/images/image3.png?raw=true)

The logs also show that the workflow is waiting:

```log
Info
2022-09-27 09:58:00.892 BST Execute steps here before waiting for callback from sheets
Info
2022-09-27 09:58:01.887 BST Started waiting for callback from sheet 10hieAH6b-oMeIVT_AerSLNxQck14IGhgi8ign-x2x8g
```

Go back to the sheet; you should see the callback info appended by Workflows
with the `Approved` column set to `FALSE`:

![Approval spreadsheet with approved false](https://github.com/GoogleCloudPlatform/workflows-demos/blob/master/workspace-integration/workflows-awaits-sheets-callback/images/image4.png?raw=true)

Now, add an approver name/email and change the `Approved` column to `TRUE`:

![Approval spreadsheet with approved true](https://github.com/GoogleCloudPlatform/workflows-demos/blob/master/workspace-integration/workflows-awaits-sheets-callback/images/image5.png?raw=true)

You should see the workflow execution state is now `Succeeded`:

![Workflow in succeeded state](https://github.com/GoogleCloudPlatform/workflows-demos/blob/master/workspace-integration/workflows-awaits-sheets-callback/images/image6.png?raw=true)

The logs also show that the workflow is approved and completed:

```log
Info
2022-09-27 10:04:11.101 BST Approved by Mete Atamel
Info
2022-09-27 10:04:11.442 BST Execute steps here after receiving callback from sheets
```

---

Callbacks are especially useful for creating human-in-the-loop type workflows.
Google Sheets provides an ready-to-use frontend for users to provide their
approvals, and Apps Script provides an easy way for developers to signal a
workflow that is waiting for a callback to resume.

For questions or feedback, feel free to reach out to me on Twitter
[@meteatamel](https://twitter.com/meteatamel).