Trigger a workflow from Google Sheets  |  Workflows  |  Google Cloud (2024)

Create a form to request VM instances

Create a form that can be used to submit a request to create aCompute Engine virtual machine (VM) instance. By default, when youcreate a form through Google Forms, it's saved inGoogle 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 saveyour form responses in that spreadsheet. The spreadsheet is saved inDrive.

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

5. Click Create.

   The linked spreadsheet opens.

   See Also

   How to Create a Workflow

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

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

   The spreadsheet should appear similar to the following:

   

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

Extend Google Sheets using Apps Script

Apps Script lets you programmatically create, read, and editSheets. Most scripts designed for Sheets manipulatearrays to interact with the cells, rows, and columns in a spreadsheet. For anintroduction to using Apps Script with Sheets, seethe 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 orrespond when the spreadsheet is opened.

   A script project represents a collection of Apps Scriptfiles and resources. Code files in a script project have a .gsextension.

2. You can use Apps Script to write custom functions that youcan use in Sheets just like a built-in function. Custom functions arecreated 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 readsthe data in your spreadsheet and passes it along as input to a workflowexecution:

      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 theWelcomepage of the Google Cloud console.

   6. Click Save save.

3. Apps Scriptinstallable triggerslet a script project execute a specified function when certain conditions aremet, 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 thetrigger:
      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 appropriateaccount, and then click Allow.

      This allows your Apps Script project to see, edit,create, and delete your Sheets spreadsheets; and to connect toan external service.

4. An Apps Script project manifest fileis a JSON file that specifies basic project information thatApps Script needs to run a script successfully. Note that theApps Script editor hides manifest files by default to protect your Apps Scriptproject 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. Toedit the file, select it.

   6. The oauthScopes field specifies an array of strings. To set theauthorization scopesyour 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 seethe 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 isapproved. The workflow calls theCompute Engine API connectorto create a Compute Engine VM instance based on the specificationscollected through the form.

Test the end-to-end flow

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

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, seeCompute 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 Workflowspage.

      Go to Workflows

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

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

      The Execution state should be Succeeded and, in the Inputpane, 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-e5c10414e3beSTATE: SUCCEEDEDSTART_TIME: 2023-01-24T15:07:40.404741791ZEND_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 isrunning.

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

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