Trigger a workflow from Google Sheets

Create a form to request VM instances

Create a form that can be used to submit a request to create a Compute Engine virtual machine (VM) instance. By default, when you create a form through Google Forms, it's saved in Google Drive.

1. Go to forms.google.com.
2. Click Blank .

   A new form opens.

3. Create a form with five required sections. The form should appear similar to the following:

   View sample form

   

   Machine name	Short answer text
   Zone	Dropdown: us-central1-a us-central1-b us-central1-c us-central1-f
   Type	Dropdown: e2-micro e2-small e2-medium e2-standard-2 e2-standard-4
   Disk size (GB)	Short answer text
   Operating system	Dropdown: debian-10 centos-stream-9 cos-93-lts cos-97-lts

4. Enable email collection to record the email addresses of people who fill in your form:
   1. Click Settings.
   2. Expand the Responses section.
   3. Click the Collect email addresses toggle.

Link a spreadsheet to your form

After you have created a form, link a spreadsheet to it so that you can save your form responses in that spreadsheet. The spreadsheet is saved in Drive.

1. Open your form in Forms.
2. Click Responses.
3. Click Link to Sheets.
4. In the Select destination for responses dialog, select Create a new spreadsheet.

5. Click Create.

   The linked spreadsheet opens.

6. In column H, add an Approved? heading.

7. Place your cursor in the first row of column H and select Insert > Checkbox.

   The spreadsheet should appear similar to the following:

   

   Each time the sheet is updated with a response, the checkbox will also appear in the row for that response.

Extend Google Sheets using Apps Script

Apps Script lets you programmatically create, read, and edit 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 Sheets, see the Custom function quickstart.

1. Create an Apps Script project from 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 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 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:

      const PROJECT_ID = "your-project-id";
      const REGION = "us-central1";
      const WORKFLOW = "create-vm-from-form";
      
      function handleEdit(e) {
        var range = e.range.getA1Notation();
        var sheet = e.source;
      
        if (range.length > 1 && range[0] === 'H') {
          if (e.value == "TRUE") {
            Logger.log("Approved checkbox: true");
      
            var row = range.slice(1)
            var email = sheet.getRange('B' + row).getCell(1, 1).getValue()
            var vmName = sheet.getRange('c' + row).getCell(1, 1).getValue()
            var zone = sheet.getRange('D' + row).getCell(1, 1).getValue()
            var machineType = sheet.getRange('E' + row).getCell(1, 1).getValue()
            var diskSize = sheet.getRange('F' + row).getCell(1, 1).getValue()
            var imageFamily = sheet.getRange('G' + row).getCell(1, 1).getValue()
            var imageProject = imageFamily.substring(0, imageFamily.indexOf('-')) + "-cloud"
      
            const executionPayload = {
              "argument": "{\"diskSize\": \"" + diskSize + "\", \"email\": \"" + email + "\", \"imageFamily\": \"" + imageFamily + "\", \"imageProject\": \"" + imageProject + "\", \"machineType\": \"" + machineType + "\", \"vmName\": \"" + vmName + "\", \"zone\": \"" + zone +  "\"}"
            };
      
            approve(executionPayload);
          }
          else {
            Logger.log("Approved checkbox: false");
          }
        }
      }
      
      function approve(executionPayload) {
        const headers = {
          "Authorization": "Bearer " + ScriptApp.getOAuthToken()
        };
      
        const params = {
          "method": 'post',
          "contentType": 'application/json',
          "headers": headers,
          "payload": JSON.stringify(executionPayload)
        };
      
        const url = "https://workflowexecutions.googleapis.com/v1/projects/" + PROJECT_ID + "/locations/" + REGION + "/workflows/" + WORKFLOW + "/executions";
      
        Logger.log("Workflow execution request to " + url);
        var response = UrlFetchApp.fetch(url, params);
        Logger.log(response);
      }

   5. Replace your-project-id with your Google Cloud project ID.

      You can find your project ID on the Welcome page of the Google Cloud console.

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

   7. Click Save save.

Deploy a workflow that creates a VM instance

Deploy a workflow that is triggered when a request to create a VM instance is approved. The workflow calls the Compute Engine API connector to create a Compute Engine VM instance based on the specifications collected through the form.

Test the end-to-end flow

To confirm that all is working as expected, try running through the entire process.

1. Send your form to yourself and answer the questions appropriately.

   Ensure that the specifications you use to create a VM instance are valid; otherwise, your workflow execution will fail. For more information, see Compute Engine general-purpose machine family.

2. To submit your response, click Submit.

3. Open your form in Forms.

4. Click Responses.

5. Click View in Sheets.

   Your linked spreadsheet opens.

6. In the row for your response, select the Approved? checkbox.

7. Open your Apps Script project.

8. Click Executions playlist_play.

   Your trigger should be executed and listed with a status of Completed.

9. Confirm that the create-vm-from-form workflow completed successfully:

   Console

   1. In the Google Cloud console, go to the Workflows page.

      Go to Workflows

   2. On the Workflows page, click the create-vm-from-form workflow to go to its details page.

   3. On the Workflow details page, to retrieve the details for a particular execution, click the appropriate execution ID.

      The Execution state should be Succeeded and, in the Input pane, you should see the VM specifications that you requested.

   gcloud

   Retrieve a list of your workflow's execution attempts:

   gcloud workflows executions list create-vm-from-form

   The result should be similar to the following:

   NAME: projects/918619793306/locations/us-central1/workflows/create-vm-from-form/executions/d8947ecb-9ccb-4ea1-ba10-e5c10414e3be
   STATE: SUCCEEDED
   START_TIME: 2023-01-24T15:07:40.404741791Z
   END_TIME: 2023-01-24T15:07:55.649373625Z

10. Confirm that a new VM was created as expected:

    Console

    In the Google Cloud console, go to the VM instances page.

    Go to VM instances

    Your VM instance should be listed with a status indicating that it is running.

    gcloud

    Retrieve an aggregate list of all VMs in all zones for a project:

    gcloud compute instances list

    Your VM instance should be listed with a status indicating that it is running.

    For more information about the gcloud compute instances list command, see its reference page.