Pause and resume a workflow using callbacks and Google Sheets

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.

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 Editor role.

8. Clear the Notify people checkbox.

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

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. Add column headings to match the following example:

   

   Note that the value in column G, Approved?, is used to initiate callbacks in the workflow.

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

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.

1. Create an Apps Script project from Google Sheets:

   1. Open your Sheets spreadsheet.
   2. Select Extensions > Apps Script.
   3. In the script editor, click Untitled project.
   4. 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.

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

   1. Open your Apps Script project.
   2. Click Editor code.
   3. A script file appears as a project file named Code.gs. To edit the file, select it.

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

      function handleEdit(e) {
        var range = e.range.getA1Notation();
        var sheet = e.source;
      
        if (range.length > 1 && range[0] === 'G') {
          if (e.value == "TRUE") {
            Logger.log("Approved: TRUE");
      
            var row = range.slice(1);
            var url = sheet.getRange('E' + row).getCell(1, 1).getValue();
            var approver = sheet.getRange('F' + row).getCell(1, 1).getValue();
      
            callback(url, approver);
          }
          else {
            Logger.log("Approved: FALSE");
          }
        }
      }
      
      function callback(url, approver) {
        const headers = {
          "Authorization": "Bearer " + ScriptApp.getOAuthToken()
        };
      
        var payload = {
          'approver': approver
        };
      
        const params = {
          "method": 'POST',
          "contentType": 'application/json',
          "headers": headers,
          "payload": JSON.stringify(payload)
        };
      
      
        Logger.log("Workflow callback request to " + url);
        var response = UrlFetchApp.fetch(url, params);
        Logger.log(response);
      }

   5. Click Save save.

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

   1. Open your Apps Script project.
   2. Click Triggers alarm.
   3. Click Add Trigger.
   4. In the Add Trigger for YOUR_PROJECT_NAME dialog, configure the trigger:
      1. In the Choose which function to run list, select handleEdit.
      2. In the Choose which deployment should run list, select Head.
      3. In the Select event source list, select From spreadsheet.
      4. In the Select event type list, select On edit.
      5. In the Failure notification settings list, select Notify me daily.
   5. Click Save.

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

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

   1. Open your Apps Script project.
   2. Click Project Settings settings.
   3. Select the Show "appsscript.json" manifest file in editor checkbox.
   4. Click Editor code.
   5. The manifest file appears as a project file named appsscript.json. To edit the file, select it.

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

   7. Click Save 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.

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.