How to use service accounts with Google Apps Script?

Welcome!

Recently, a logistics company approached me with a challenging data management problem. They had a massive BigQuery dataset tracking fleet deliveries across the country, and their analysts were drowning in manual work — spending hours running repetitive queries and manually transferring data into Google Sheets to generate reports.

They wanted to create a button-driven interface in Google Sheets that would let users fetch delivery data instantly, filtering by regions or specific timeframes with just a single click.

Our initial prototype hardcoded the BigQuery project details directly into the Apps Script. Hardcoding project credentials meant that anyone with access to the script could potentially run queries, access sensitive data, and potentially incur significant unexpected costs.

We replaced the hardcoded project details with service accounts for secure, controlled, and scalable access to BigQuery. To achieve this solution we used OAuth 2.0 to authenticate as the service account.

This article focuses on the authentication part using OAuth 2.0 for the service account. If you want to learn how to build the BigQuery solution, check out the link given below.

Fetch BigQuery data to Google Sheets using Google Apps Script

Sample Google Sheet

The Google Sheet that I will be using for this tutorial is a log of product user experience and content tests and the goal of the client is to fetch data based on the ID’s of the tests. The Google Sheet contains the following details:

1. ID
2. Test Name
3. Participants
4. Test Focus
5. Last Fetched

If you want to work with this sheet, click here.

Sample BigQuery Dataset

The dataset that I will be using for the tutorial is a log of product user experience and content tests.

Using OAuth to Access Service Accounts in Apps Script

In Google Apps Script, you cannot directly use a service account’s credentials for API access. Instead, you authenticate as the service account via OAuth 2.0. This is achieved by providing the service account’s private key and client email address.

This approach ensures secure and controlled access to BigQuery (and other Google APIs) within the limitations of the script’s environment.

Create Service Account and Download Credentials

To securely interact with Google Cloud APIs like BigQuery, we first need to create a service account and download its credentials. To do that, follow these steps:

1. Navigate to the Google Cloud Console and select the project you want to associate with the service account.
2. Click on the hamburger icon at the top left corner and go to IAM & Admin > Service Accounts

3. Click on Create Service Account.

4. Fill in the details:

• Service account name: Provide a descriptive name.
• Service account description (optional): Briefly explain its purpose.
• Click Create and Continue.

5. Assign Permissions

• Select a role for the service account, such as BigQuery Data Viewer, to restrict its access only to whats necessary.
• Click Continue.

6. Download the Credentials File

• After creating the service account, navigate to the Keys section in the service account details.
• Click Add Key> Create New Key.
• Select JSON as the type and click Create.
• A JSON file containing the private key and other credentials will be downloaded. Store this file securely, as it is critical for authenticating API requests.

Adding the OAuth2 Library to Apps Script

Once the service account credentials are downloaded as a JSON file, they enable secure authentication to Google APIs like BigQuery. Here’s how we utilize these credentials in our solution:

1. The JSON file contains critical details like client_email, private_key, and project_id, that are required for authentication.
2. These details are essential in generating OAuth tokens that grant access to APIs.

Since Google Apps Script does not support the direct use of service accounts, we integrate these credentials using the OAuth2 Library. To add this to your Google Apps Script project, follow these steps:

1. The library is already published as an Apps Script, making it easy to include in your project. Open up your Apps Script editor and navigate to Libraries and click on Add Library.
2. In the Script ID text box, enter the following script ID, 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF and click on Look Up.
3. Choose a version in the dropdown box and click on Add.

To know more about the OAuth2 Library check out the GitHub repository given below.

GitHub - googleworkspace/apps-script-oauth2: An OAuth2 library for Google Apps Script.

Write the Automation Script

Now that we have everything setup, its time to code.

The first step in the process would be to replace the credentials.

var JSON = {
  private_key: 'Your Private Key',
  client_email: 'serviceacount@project-id.iam.gserviceaccount.com',
  client_id: 'Your Client ID',
  user_email: 'aryanirani123@gmail.com',
};

Copy the private key and client email from the JSON credentials and paste them into the code. You can even store these details in the Google Secret Manager, To know more check out the link given below.

Secret Manager overview | Secret Manager Documentation | Google Cloud

function getOAuthService(user) {
  return OAuth2.createService("Service Account")
    .setTokenUrl('https://oauth2.googleapis.com/token')
    .setPrivateKey(CREDENTIALS.private_key)
    .setIssuer(CREDENTIALS.client_email)
    .setSubject(user)
    .setPropertyStore(PropertiesService.getScriptProperties())
    .setParam('access_type', 'offline')
    .setScope('https://www.googleapis.com/auth/bigquery https://www.googleapis.com/auth/spreadsheets')
}

This function starts by creating an OAuth2 service named "Service Account" using the OAuth2.createService() method. It then sets the token URL (https://oauth2.googleapis.com/token), which is the endpoint where Google issues OAuth2 access tokens. To authenticate, it uses the private key from the service account credentials with setPrivateKey(CREDENTIALS.private_key) and sets the issuer (the service account email) using setIssuer(CREDENTIALS.client_email).

If the service account has domain-wide delegation enabled, we can specify a user in setSubject(user), allowing the service account to act on behalf of another user. This is useful when the service account needs to perform actions as a Google Workspace user. The function then stores authentication tokens in the script's property store using setPropertyStore(PropertiesService.getScriptProperties()), ensuring tokens are securely saved and can be reused without re-authentication. The setParam('access_type', 'offline') ensures that the access token can be refreshed without user intervention. Finally, setScope() defines the necessary API permissions, allowing access to BigQuery for querying datasets and Google Sheets for reading and writing spreadsheet data.

By using this approach, we ensure that Google Apps Script can securely authenticate with BigQuery and Google Sheets without exposing sensitive credentials in the code. The OAuth2 service automatically manages access tokens, refreshing them as needed to maintain seamless API access.

function reset() {
  var service = getOAuthService();
  service.reset();
}

The reset function is a simple yet important part of managing OAuth2 authentication in Google Apps Script. It works by calling the getOAuthService() function to retrieve the OAuth2 service instance and then executing service.reset(). This command clears any stored authentication tokens and resets the service, effectively forcing a fresh authentication process the next time the service is used. This function is useful in cases where credentials need to be refreshed, such as when switching service accounts, updating permissions, or troubleshooting authentication issues.

And that’s it! You’ve now set up secure authentication using a service account in Google Apps Script. With OAuth2 handling access, your script can now connect to BigQuery, Google Sheets, or any other Google API without exposing sensitive credentials. From here, you can start running queries, automating tasks, and integrating Google services seamlessly into your workflow.

To know more about fetching BigQuery data into Google Sheets using Google Apps Script and service accounts, check out the link given below

Fetch BigQuery data to Google Sheets using Google Apps Script

Conclusion

Now that you’ve learned how to authenticate a service account in Google Apps Script, you can extend this solution further (consider integrating additional Google APIs). This approach eliminates the need for manual data transfers, reduces security risks, and ensures that API access is managed efficiently.

Whether you’re working with BigQuery, Google Drive, or other Google APIs, using OAuth2 with service accounts in Google Apps Script provides a robust and maintainable solution.

You can check out the code and various other Google Apps Script automation scripts on the link given below.

Google-Apps-Script/ServiceAccount.js at master · aryanirani123/Google-Apps-Script

Feel free to reach out if you have any issues/feedback at aryanirani123@gmail.com.