G Suite Solutions — Apps Script Powered Workflow Automation

7 min readMar 12, 2020

I recently had the privilege of speaking at SheetsCon on the topic of Automation with Apps Script. During the presentation, I walked through a simple approval workflow for a hypothetical small business to demonstrate the power of Apps Script to automate workflow inside G Suite. I also showcased modern ECMAScript features supported by the new Apps Script V8 Javascript runtime.

Following is an overview of the workflow covered in my presentation along with install instructions for trying it out in your G Suite domain.

Workflow Automation Use Case

Let’s assume you’re a small business owner using G Suite.

You’re a savvy entrepreneur and recognize you can automate your employee purchase request management (streamlining both request generation and request approval) to save you time and better leverage your G Suite investment.

Diagram 1 outlines your requirements for purchase request generation.

Diagram 1 — Purchase Request Generation Requirements

Diagram 2 outlines your requirements for purchase request approval.

Diagram 2 — Purchase Request Approval Requirements

Diagram 3 outlines the G Suite assets you’ll employ to automate this workflow.

Diagram 3 — Purchase Request Workflow G Suite Assets

All workflows are comprised of Triggers and Actions.

Workflow triggers are catalysts which initiate workflow and generally fall into one of three categories:

Date triggers
Time triggers
Event triggers

Workflow actions are the sequence steps needing to be performed to complete the work.

Diagram 4 outlines the simple and installable Apps Script triggers used to perform this particular workflow automation and the corresponding actions performed by Apps Script when the triggers occur.

Diagram 4 — Apps Script Workflow Triggers & Actions

Now let’s turn our attention to how Apps Script powers this workflow.

Workflow Configuration

Diagram 5 — Workflow Menu added by Apps Script

Apps Script facilitates workflow configuration by taking advantage of the simple onOpen() trigger to build and populate a workflow menu inside the Purchase Request Management Google Sheet when the Sheet is first opened (shown in Diagram 5).

Additional Apps Script is associated with each workflow menu option when the menu is created and you, the business owner, use this menu to trigger the associated Apps Script to perform the following workflow configuration:

Associate the Purchase Request Google Form submission destination with the Purchase Request Management Sheet (creating a new Form Responses tab in the Sheet)
Create the installable Apps Script Form Submit trigger
Format the Purchase Request Management Google Sheet > Form Responses tab and add additional purchase request approval fields

Request Generation

Once the workflow is configured, employees are able to submit purchase requests to the Purchase Request Google Form.

Upon each purchase request submission, the Purchase Request Management Google Sheet > Form Responses tab is populated with a new row representing the request submission and triggers Apps Script to:

Make a copy of the Purchase Request Google Doc template
Move the newly copied Generated Request Google Doc to the generated requests Google Drive folder
Replace existing template markers in the Generated Request Google Doc with actual purchase request data
Share the Generated Request Google Doc with the employee who submitted the request along with their supervisor
Update the request entry in the Purchase Request Management Sheet > Form Responses tab to add the generated request document URL along with a ‘last update’ timestamp
Send a Gmail notification to you, the business owner, along with the employee and their supervisor

These actions are depicted in Diagram 6 above.

Request Approval

You, the business owner, receive a Gmail notification for each new purchase request and are able to open the Purchase Request Management Google Sheet and make the appropriate approval updates using the corresponding approval status fields added during configuration.

You then employ the Sheet’s workflow menu to trigger Apps Script to:

Update the approval status in the Generated Request Google Doc
Modify the ‘last update’ timestamp in the Purchase Request Management Google Sheet > Form Responses tab for the given request
Send a Gmail notification to you, the business owner, along with the employee and their supervisor

These actions are depicted in Diagram 7 above.

Now let’s step through how to configure and use this workflow.

Purchase Request Workflow — Getting Started

STEP 1: Install Workflow Assets in Google Drive

Login to Google Drive.
Create a Google Drive folder to hold the workflow assets and name the new folder: Purchase Requests
Make a copy of each of the following workflow assets and move them to the newly created workflow folder. Click the Use Template button on each asset URL to copy the asset to Google Drive.

WORKFLOW ASSETS
👉 Purchase Request Google Form
👉 Purchase Request Management Google Sheet — Apps Script workflow code included
👉 Generated Purchase Request Google Doc Template

Create a single subfolder inside the Purchase Requests folder to hold the generated purchase request documents giving the subfolder the name: Generated Requests

Diagram 8 — Purchase Request Workflow Assets in Google Drive Folder

When Step 1 is complete, the Purchase Requests folder should reflect Diagram 8.

👉 IMPORTANT: To ensure the workflow operates correctly, the Purchase Requests assets folder should contain no files other than the workflow assets copied or created in Step 1.

👉 IMPORTANT: Any modifications to the workflow assets or Apps Script code other than described in this blog post could result in the workflow failing to operate correctly.

STEP 2: Enable Apps Script V8 Runtime

The Apps Script powering this workflow takes advantage of ECMAScript features afforded by the new Apps Script V8 Javascript runtime.

To enable the Apps Script V8 runtime:

Open the Purchase Request Management Google Sheet from the Purchase Requests folder.
Open the Apps Script editor by selecting Tools > Script editor from the Sheet menu.
Select the Run > Enable new Apps Script runtime powered by V8 from the editor menu (if not already enabled).
Save the script and close the editor.

👉 For an alternate method of enabling the V8 runtime, see the documentation.

STEP 3 (Optional): Update Employee Information to Reflect Your G Suite Domain

Optionally update the employee information in the Purchase Request Management Sheet > Employee tab as appropriate for your G Suite domain. Be sure to keep the existing four columns for employee data to ensure the workflow operates correctly.

👉 This workflow is designed to operate with the current demonstration employee data but document sharing and email notifications will not occur without valid email addresses.

👉 If you choose to use your own employee data, be sure to update the Purchase Request Google Form > Requester Name drop down field to match the employee names used.

STEP 4: Configure & Enable Workflow

Open the Purchase Request Management Google Sheet and wait for the Purchase Request Workflow menu to appear in the toolbar
Select the Purchase Request Workflow > Configure > Setup Workflow Config menu and wait for the Purchase Request Management Google Sheet > Config tab to be populated with workflow asset URLs and for the Purchase Request Management Google Sheet > Form Responses tab to be added.

👉 The first time the Purchase Request Workflow menu is accessed, the script will prompt for authorization. Complete the authorization flow by following the Google authorization prompts.

👉 IMPORTANT: If the Config tab is not populated with workflow asset URLs after authorization, re-run the Purchase Request Workflow > Configure > Setup Workflow Config menu.

Next, select the Purchase Request Workflow > Configure > Setup Request Sheet and wait for the Form Responses tab to be formatted and the Form Submit trigger to be configured.

Congratulations! The workflow is configured and ready for use.

STEP 5: Submit Requests to Purchase Request Form

Employees can now submit purchase requests to the Purchase Request Google Form.

👉 If timestamps do not accurately reflect the workflow timezone, ensure both the Purchase Request Management Google Sheet and Apps Script timezones are configured appropriately. See this documentation for instructions on updating the Google Sheet timezone. The Apps Script timezone can be changed by opening the Sheet’s Apps Script editor (Tools > Script editor menu) and altering the timezone under the Apps Script editor File > Project properties menu.

Congratulations! The workflow is generating new employee purchase requests and sending Gmail notifications.

STEP 6: Approve Submitted Purchase Requests

Open the Purchase Request Management Google Sheet > Form Responses tab
Locate the purchase request to be approved
Modify the request approval status and add any approval comments
Select the entire request approval row (click the row number to select the row)
With the request row highlighted, select the Purchase Request Workflow > Configure > Update Request menu

Congratulations! The workflow updates request approvals and sends approval status update Gmail notifications.

Important Workflow Notes

This workflow is meant to demonstrate G Suite workflow automation with Apps Script and should not be deployed in a production environment without further development and testing.
This workflow uses Google Drive “My Drive”. Code modifications are need to enable it to work with Shared drives.
Check the Apps Script Dashboard for execution errors if this workflow does not work as expected.

Looking for the code? See the TSWorkflow project on Github.

Looking for other G Suite workflow solutions? Check out the G Suite Solutions Gallery.

Need help automating workflow inside your G Suite domain? Get in touch

Follow me on Twitter , Medium and Github.