How to use GCP service accounts with Google Apps Script projects to automate actions in G Suite

Introduction

My work associates create amazing automations in Google Apps Script that empower their own productivity. Naturally, they want to share the work with their peers; however, they struggle due to security and permissions. This tutorial will use a simple case study of generating a Google Slides presentation based on the input gathered in a Google Form. Although this case study is basic, the pattern of obtaining structured information using a form to then inject that information in a template can be extended to many use cases. This tutorial will:

• Define a service account
• Introduce a sample automation as a case study
• Provide instructions to setup the basic automation
• Provide instructions to create a GCP Project, create a service account, enable the Drive API, and create an API key
• Provide instructions to add the Google OAuth2 Library to the Apps Script project
• Provide instructions to incorporate the service account in Apps Script
• Conclude with a comparison between the basic and service account automations

Service accounts defined

As defined on cloud.google.com, a service account is a special type of Google account intended to represent a non-human user that needs to authenticate and be authorized to access data in Google APIs. Typically, service accounts are used in scenarios such as:

• Running workloads on virtual machines (VMs).
• Running workloads on on-premises workstations or data centers that call Google APIs.
• Running workloads which are not tied to the lifecycle of a human user.

Your application assumes the identity of the service account to call Google APIs, so that the users aren’t directly involved.

Case study: Create a Google Slides presentation based on Google Forms input

Sales Associates are constantly creating and re-creating Slides presentations for customer interactions. Since this article is about how to use a service account to enhance an automation, the automation is intentionally simple and is provided read-only on Apps Script for use in the next section of this tutorial.

The automation works as follows:

The basic flow is:

1. The User fills out a Google Form answering the question “What is your favorite animal”?
2. The onFormSubmit event triggers the Apps Script to run (see Simple triggers | available types of triggers for more information)
3. The Google Apps Script copies a Google Slides Template and injects an image of the favorite animal chosen in step one

SECTION ONE: How to setup the basic automation — prior to service account

The basic automation has the following files located within this Google Drive Folder:

• GCP service account presentation (Google Slides template)
• Favorite Animal Form (Google form that generates the new presentation)
• GCP Service Account Automation Case Study — before (Google Apps Script that performs the automation)
• Created slides presentations (Google drive folder to house all new presentations)

Visit github.com/daubejb/google-apps-script-service-accounts for a copy of the code, which is also displayed below:

To only test the automation, pre service account, simply:

1. Open the Favorite Animal Form
2. Enter your G Suite email address
3. Select your favorite animal from the five pre-canned responses
4. Submit the form
5. Navigate to drive.google.com on the same email address account
6. Click on Shared with me and to see the created presentation with the title formatted as “<email address> <animal>”

NOTE: An example file name is: daubejb@gmail.com Cat

To re-create the same automation on your G Suite Domain, perform the following:

1. Copy the presentation template, Apps Script file, and the Form to your Google Drive
2. Open the Apps Script file in the Editor found at script.google.com
3. Click the drop down menu to the right of the play and debug buttons and select setupTrigger
4. Click the play button to run the setupTrigger function (this step will prompt you to approve the application to act on your behalf)
5. Create a new folder on Google Drive to store the created presentations
6. Copy the Folder Id to clipboard (it can be found in the url when you have the folder open — depicted below these steps)
7. Within the Apps Script file, at the top of the file, paste the value copied in step 6 into the PARAMS.destinationFolderId
8. Repeat steps 6 and 7 for both file ids for the formId and the slidesTemplateId, both located within the PARAMS variable
9. Open and fill out the form indicating your favorite animal
10. Navigate to drive.google.com on the same email address account
11. Click on recent to see the new presentation

SECTION TWO: Create a GCP project, a service account, activate the Google Drive API, and an API key

Step one: Create a new GCP Project

1. Navigate to https://console.cloud.google.com
2. Click on the project drop down menu within the navigation bar on the top left of the site
3. Ensure that your corporate domain is selected in the Select from drop down menu (this selection will make sure that the project lives on after you get that promotion or switch roles)
4. Click on NEW PROJECT and fill in the following details then click CREATE

NOTE: Google offers new GCP users $300 in free GCP cloud credits. Many Google services are only available if you attach a valid billing account to the project. See https://cloud.google.com/billing/docs/concepts.

NOTE: It will take a little bit of time to provision the GCP Project, watch the notification section located at the top right of the page. When the project has been created, make sure to click the project in the notification section OR select the project from the Project Selection Drop Down Menu.

Step two: Create a Service Account

1. Click on the Menu Icon at the top left of the page
2. Hover over the IAM & admin menu item and select Service accounts
3. Click on + CREATE SERVICE ACCOUNT
4. Enter Favorite animal app in the Service account name text box
5. Click CREATE
6. Click CONTINUE to bypass granting the service account additional project access
7. Click + CREATE KEY and ensure that JSON is selected for the Key type
8. Click CREATE and take note of where the Private key JSON file is saved on your computer. We will need this file later in this case study
9. Click CLOSE
10. Click DONE
11. Click the Service Account Email to open the Service account details page
12. Click EDIT
13. Click the SHOW DOMAIN-WIDE DELEGATION drop down menu
14. Toggle the Enable G Suite Domain-wide Delegation check box to checked
15. Upon successful completion of Step two, you will see a green check box under the status column of the Service accounts table depicted below:

IMPORTANT: Make sure to share any and all files and folders needed as part of the automation with the service account email address. If the files are not shared, an access forbidden error will occur. In this automation, the folders and the Google Forms file are shared with “favorite-animal-app@gcp-service-account-automation-iam.gserviceaccount.com.”

NOTE: In step six above, the Favorite animal app service account was not granted any additional project access because the account does not need any special project access for the automation. This limited access follows the NIST defined security practice of least privilege. See this article: Don’t get pwned: practicing the principle of least privilege for more information.

Step three: Enable the Google Drive API

1. Click on the Menu Icon on the top left of the page
2. Hover over the APIS & Services menu item and select Dashboard
3. Click on + ENABLE APIS AND SERVICES
4. Within the search box, start typing Google Drive and then select Google Drive API
5. Click ENABLE
6. Upon successful completion of step three, note that the Google Drive API is enabled indicated by the ability to disable the API depicted below:

Step four: Create a Google Drive API key

1. Within the Google Drive API menu, click on Credentials
2. Click + CREATE CREDENTIALS and select API key
3. Copy the API key for use later in this tutorial
4. Click RESTRICT KEY
5. Within the API restrictions section, toggle the radio button selection to Restrict key
6. Within the Select APIs drop down menu, select Google Drive API
7. Click SAVE
8. Upon successful completion of step four, note the green check mark and the Google Drive API restriction located within the API Keys table displayed below:

NOTE: In step 4, 5, and 6 above, the API key was restricted to only has access to the Google Drive API — again following the best practice of least privilege.

SECTION THREE: Incorporate the service account in Apps Script

Step one: Add the OAuth Library to the apps script project

1. Open the Apps Script project from Section one of this case study
2. In the Alt menu, click Resources and select Libraries…
3. Within the Add a library text box, paste: 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF and click Add
4. Click on the Version drop down menu and select the latest version
5. Click SAVE
6. Upon the successful completion of this step, verify the addition of the library to the Apps Script project’s Manifest file, accessible at Alt menu → View → Show manifest file, shown below:

NOTE: The Project Manifest file is used to keep track of project dependencies and versions, timezones, and included functionality. To learn more visit the Manifest structure page. To learn more about the OAuth2 library, visit github.com.

Step two: Add the getOAuthService and reset function declarations and a CREDENTIALS variable to the Code.gs file

Within the Code.gs file of the apps script editor, add the following two function declarations:

Next, add the following CREDENTIALS variable:

NOTE: The private key, client email, and client id are found in the file downloaded during Section two, step 2, number 8 of this tutorial. The API key is found in Section two, step 4, number 8 of this tutorial.

Step three: Modify the existing code to use the service account

Within the Code.gs file of the apps script editor, replace the existing copyTemplate function with the following:

NOTE: This final code can be found @ github

When you fill out the Google Form, the automation will work just like it did in the basic example. Except as you can see below, the Owner of the file generated from the template is the service account named, “favorite-animal-app.”

Conclusion: Compare and contrast the basic and service account automation

Creating automations like this Form to presentation template without a service account can work reasonable well for individuals and small organizations; however, there are several drawbacks:

1. The new Presentation will be created by the owner of the Google Apps Script — a named user. This author name may not be known and/or trusted by all associates within a company; especially if the automation is provided to hundreds of thousands of employees.
2. If the script author leaves the company, these presentations could be lost if retention policies are not in place and enforced
3. The Apps Script file is also at risk if the initial author leaves the company

Even with all these drawbacks, a basic automation can save time and deliver functionality to associates; however — adding a GCP service account and hosting the solution on a Shared Drive can drastically reduce the risk of lost documents and functionality.

NOTE: I really enjoy writing these tutorials and solving problems using the creative application of technology. If you are interested in collaborating, have a challenge, or need help — contact me:

Github: https://github.com/daubejb
Twitter: https://twitter.com/jeffdaube
Website: https://jeffdaube.com
LinkedIn: https://www.linkedin.com/in/jeffreydaube/