Create a new folder in Google Drive

Create a new folder in Google Drive. This folder is used to store yourspreadsheet. By setting up a permission for the shared folder, your workflowis allowed to write to the spreadsheet.

1. Go todrive.google.com.
2. ClickNew> New folder.
3. Enter a name for the folder.
4. ClickCreate.
5. Right-click your new folder and selectShare.

6. Add the email address for the Compute Engine default serviceaccount.

   This gives the service account access to the folder. When you associatethe service account with your workflow, the workflow will have editaccess to any file in the folder.Learn more about sharing files, folders & drives.

7. Select theEditor role.

8. Clear theNotify people checkbox.

9. ClickShare.

Create a spreadsheet using Google Sheets

When you create a spreadsheet through Google Sheets, it's saved inGoogle Drive. By default, the spreadsheet is saved to your root folder onDrive. There's no option to create a spreadsheet directlywithin a specified folder using the Google Sheets API. However, thereare alternatives, including moving the spreadsheet to a specific folder afteryou create it, as is done in this example. For more information, seeWork with Google Drive folders.

1. Go tosheets.google.com.

2. ClickNew.

   This creates and opens your new spreadsheet. Every spreadsheet has a uniquespreadsheetId value,containing letters, numbers, hyphens, or underscores. You can find thespreadsheet 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 columnG,Approved?, is used to initiatecallbacks in the workflow.

5. Move the spreadsheet to the Google Drive folder that you createdpreviously:

   1. In the spreadsheet, selectFile> Move.
   2. Navigate to the folder you created.
   3. ClickMove.

You can also use theGoogle Sheets API connectorto create a spreadsheet. Note that when using the connector, thespreadsheetId can be retrieved from theresp 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 editGoogle Sheets. Most scripts designed for Sheets manipulatearrays to interact with the cells, rows, and columns in a spreadsheet. For anintroduction to using Apps Script with Google Sheets, seetheCustom function quickstart.

1. Create an Apps Script project from Google Sheets:

   1. Open your Sheets spreadsheet.
   2. SelectExtensions> Apps Script.
   3. In the script editor, clickUntitled project.
   4. Give your project a name and clickRename.

   Your script is nowboundto your spreadsheet, which gives the script special abilities to alter theuser interface or respond 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 Google Sheets just like a built-in function. Custom functions arecreated using standard JavaScript. Create a function:

   1. Open your Apps Script project.
   2. ClickEditorcode.
   3. A script file appears as a project file namedCode.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:

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

   5. Click Savesave.

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. ClickTriggersalarm.
   3. ClickAdd Trigger.
   4. In theAdd Trigger for YOUR_PROJECT_NAME dialog, configure thetrigger:
      1. In theChoose which function to run list, selecthandleEdit.
      2. In theChoose which deployment should run list, selectHead.
      3. In theSelect event source list, selectFrom spreadsheet.
      4. In theSelect event type list, selectOn edit.
      5. In theFailure notification settings list, selectNotify me daily.
   5. ClickSave.

   6. If you receive a prompt to choose a Google Account, select the appropriateaccount, and then clickAllow.

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

4. An Apps Script projectmanifest 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. ClickProject Settingssettings.
   3. Select theShow "appsscript.json" manifest file in editor checkbox.
   4. ClickEditorcode.
   5. The manifest file appears as a project file namedappsscript.json. Toedit the file, select it.

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

   7. Click Savesave.

   Note: To protect your project settings, hide the manifest when you are doneediting by clearing theShow "appsscript.json" manifest file in editorcheckbox.

Deploy a workflow that writes to a spreadsheet and uses callbacks

Deploy a workflow that executes, pauses, and then resumes when a callback isapproved through a spreadsheet. The workflow writes to a Sheetsspreadsheet using theGoogle Sheets API connector.

Test the end-to-end flow

Execute the workflow to test the end-to-end flow. Executing a workflow runs thecurrent workflow definition associated with the workflow.