DEV Community: Dataful.Tech

Access Google Cloud Secret Manager via Google Apps Script

Dataful.Tech — Wed, 01 May 2024 21:04:32 +0000

There are many ways to store secrets, such as tokens, passwords, and API keys, in Google Apps Script, but they are not created equal. Some are safer than others. One way to deal with this challenge is to store the secrets externally and access them on demand.

GCSecretManager (GitHub) is a Google Apps Script library that allows you to store secrets in Google Cloud Secret Manager. The library also works as a storage for SecretService library. Let's look at three ways to use it.

If you find this library useful, please give the repository a star and share the link with others.

Use Library Directly

You can use the library directly without initializing an instance:

// Get the latest version of the secret
const secretLatest = GCSecretManager.get("secret-key", {
  project: "project-id",
});

// Get the latest version of the secret
const secretV2 = GCSecretManager.get("secret-key", {
  project: "project-id",
  version: 2,
});

// Instead of the config, specify project via chaining:
const anotherSecretV3 = GCSecretManager.setProject("project-id")
  .setVersion(3)
  .get("another-secret-key");

// Set secret. A new one will be created if it doesn't exist
// or, if it does, a new version for the existing one.
GCSecretManager.set("secret-key", "secret-value", { project: "project-id" });

// Directly call the Secret Manager API

// Get the latest version of the secret
const oneMoreSecretLatest = GCSecretManager.getSecret(
  "project-id",
  "one-more-secret-key"
);

// Create a new secret
GCSecretManager.createSecret("project-id", "new-secret-key");
// Create a new version of a secret
GCSecretManager.createSecretVersion(
  "project-id",
  "new-secret-key",
  "new-secret-value"
);

Create an Instance

You can create an instance to provide the configuration only once and use it multiple times:

// Initialize
const MANAGER = GCSecretManager.init({ project: "project-id" });

// You can also use chaining to initialize the manager
const MANAGER = GCSecretManager.init().setProject("project-id");

// Retrieve the latest secret version
const secret = MANAGER.get("secret-key");

// Set a secret
MANAGER.set("secret-key", "secret-value");

// The direct methods will work the same way as in the examples above
const oneMoreSecretLatest = MANAGER.getSecret(
  "project-id",
  "one-more-secret-key"
);

As a SecretService Storage

GCSecretManager can also work as a storage layer for the SecretService library, combining their benefits:

const storage = GCSecretManager.init({ project: "project-id" });
const SECRETS = SecretService.init({ storage });

const secretValue = SECRETS.getSecret("API_KEY");

Contributions are welcome. Feel free to submit pull requests or issues on GitHub.

SecretService: Library for Storing Secrets in Google Apps Script

Dataful.Tech — Tue, 12 Mar 2024 12:11:16 +0000

Google Apps Script is an excellent tool for connecting various services via API. Access to API requires storing secrets: tokens, passwords, and other secrets. There are many ways to store secrets in Google Apps Script, but most of them are challenging and have drawbacks. The most popular way to store secrets is still saving them directly in the code: it might be okay for a small one-off script that only you have access to, but it doesn’t cut it when we are talking about real-life implementations.

Solution

We created SecretService (GitHub), an open-source Google Apps Script library, to solve this problem. It is designed to simplify storing secrets and add various quality-of-life improvements.

These are the main features of SecretService:

Choose a storage for the secrets:
- Any Properties instance from your script
- Custom secret storage, like Google Cloud Secret Manager
Different modes in case of a missing secret:
- Silent: do nothing, return null.
- Strict: throw an error.
- Interactive: prompt the user for a missing secret.

If you find this library useful, please give the repository a star and share the link with others.

Examples

There are several ways to use SecretService. Check out the repository for the complete documentation and more examples.

Store Secrets in UserProperties

Generally, the safest out of the box place to store secrets is UserProperties: they are accessible only to the user who runs the script, and only in the script you mean.

Important: regardless of what the official documentation, says the user properties of the owner of a Google Sheets document are accessible to anyone via a custom function. (why Google, why?)

const SECRETS = SecretService.init({
  storage: PropertiesService.getUserProperties()
});

// Only once:
const API_KEY = SECRETS.setSecret("API_KEY", "value");

const API_KEY = SECRETS.getSecret("API_KEY");

Interactive Mode

If your Google Apps Script is attached to a spreadsheet/document/form/slides, you can use interactive mode that will prompt the user to enter the secret if it was not set before:

const SECRETS = SecretService.init({
  storage: PropertiesService.getUserProperties(),
  scriptContainer: SpreadsheetApp,
  mode: "interactive",
});

const API_KEY = SECRETS.getSecret("API_KEY");

User prompt:

If the user clicks Cancel or closes the prompt, SecretService will throw an error.

Contributions are welcome. Feel free to submit pull requests or issues on GitHub.

FetchApp: UrlFetchApp with Retries

Dataful.Tech — Thu, 04 Jan 2024 22:56:14 +0000

Google Apps Script is often used to pull data from various services via HTTP requests. However, these requests sometimes fail due to network or service issues. The default behavior of UrlFetchApp is to throw an exception, which you have to catch. Otherwise, the script execution will be interrupted.

We often need more: send the request again instead of failing. There is no built-in way to do retries in Apps Script.

Solution

To solve this problem and not copy-and-paste code snippets from project to project, I created FetchApp (GitHub) -- an open-source Google Apps Script library.

Its main features are:

Optional Retries: Depending on the response code received.
Delay Strategies: Choose between linear or exponential delay between retries.
Custom Callbacks: Implement callbacks on failed attempts for tailored actions and logic.
Enhanced Type Hints: Improved hints for UrlFetchApp's params argument.
Automatic Logging: Logs failed attempts automatically.

I use this library in many projects in production, especially where I have to communicate with unreliable third-party APIs, or there is a chain of related requests that I do not want to repeat if one of them randomly fails.

If you find this library useful, please give the repository a star and share the link with others.

Features, Use Cases, Examples

There are multiple ways and use cases to use FetchApp.

Drop-in Replacement for UrlFetchApp

FetchApp is designed to work as a drop-in replacement for UrlFetchApp. Caveat: FetchApp sets muteHttpExceptions: true in params unless explicitly specified otherwise.

// `url` and `params` are defined elsewhere

// regular UrlFetchApp
const response1 = UrlFetchApp.fetch(url, params);

// FetchApp without configuration is a pass-through to UrlFetchApp
const response2 = FetchApp.fetch(url, params);

// FetchApp with retries and delay enabled
const config = {
  maxRetries: 5,
  successCodes: [200],
  delay: 500,
};
const response3 = FetchApp.fetch(url, params, config);

// If there are no `params`, pass an empty object
const response4 = FetchApp.fetch(url, {}, config);

Configurable Client

If you need to use FetchApp multiple times, you can initiate a client to reuse the configuration:

// FetchApp with retries and delay enabled
const config = {
  maxRetries: 5,
  retryCodes: [500, 502, 503, 504],
  delay: 500,
};

const client = FetchApp.getClient(config);

// All client's fetch calls will use this config
const response1 = client.fetch(url, params);

// Partially modify the config for a specific request
const response2 = client.fetch(url, params, { successCodes: [200] });

Success or Retry Response Codes

FetchApp retries requests depending on the response code received in two different modes:

successCodes: deem responses with these codes successful and return the response. If provided, retryCodes are ignored.
retryCodes: requests with these codes are not successful; retry.

Examples:

const response1 = FetchApp.fetch(
  url,
  params,
  {
    successCodes: [200],  // Everything else leads to retries
    maxRetries: 3,
  },
)

const response2 = FetchApp.fetch(
  url,
  params,
  {
    retryCodes: [500, 502, 503],  // Everything else is deemed successful
    maxRetries: 3,
  },
)

const response3 = FetchApp.fetch(
  url,
  params,
  {
    successCodes: [200],  // Takes priority over retryCodes
    retryCodes: [500, 502, 503],  // Ignored
    maxRetries: 3,
  },
)

Delay Between Requests

FetchApp supports constant or exponential delay between retries:

const response1 = FetchApp.fetch(
  url,
  params,
  {
    successCodes: [200],  // Everything else leads to retries
    maxRetries: 3,
    delay: 300,  // Constant delay of 300ms after each request
  },
)

const response2 = FetchApp.fetch(
  url,
  params,
  {
    successCodes: [200],  // Everything else is deemed successful
    maxRetries: 3,
    // Exponential delay of 1, 2, 4, 8, etc. seconds
    delay: 1000,
    delayFactor: 2,
  },
)

const response2 = FetchApp.fetch(
  url,
  params,
  {
    successCodes: [200],  // Everything else is deemed successful
    maxRetries: 10,
    // Exponential delay of 1, 2, 4, 8, 10 seconds.
    delay: 1000,
    delayFactor: 2,
    maxDelay: 10000,  // Limit delay to maximum 10 seconds
  },
)

Throw Exceptions via Callbacks

If you need to throw an exception if all attempts fail, you can do it via a onAllRequestsFailure callback:

// Throw an exception
const throwException = ({ retries, url }) => {
  throw new Error(`All ${retries + 1} requests to ${url} failed`);
};

const config = {
  successCodes: [200],
  maxRetries: 5,
  delay: 500,
  onAllRequestsFailure: throwException,
};

const response = FetchApp.fetch(url, params, config);

Send Notifications via Callbacks

One of the difficulties with Apps Script is that it functions as a black box unless you add logging and notifications to the script. With the onRequestFailure and onAllRequestsFailure callbacks, you can send notifications about failed requests.

// Define the `sendNotification` function elsewhere

// Send notification if access is denied, maybe because the credentials expired
const accessDenied = ({ url, response }) => {
  const responseCode = response.getResponseCode();
  if ([401, 403].includes(responseCode)) {
    sendNotification(`Received ${responseCode} when accessing ${url}`);
  }
};

// Send a notification if all attempts failed
const allAttemptsFailed = ({ retries, url }) => {
  throw new Error(`All ${retries + 1} requests to ${url} failed`);
};

const config = {
  successCodes: [200],
  maxRetries: 5,
  delay: 500,
  onRequestFailure: accessDenied,
  onAllRequestsFailure: allAttemptsFailed,
};

const response = FetchApp.fetch(url, params, config);

Autocomplete and Type Hints

FetchApp comes with JSDoc declarations that enable type hints and autocomplete in Google Apps Script IDE, both for UrlFetchApp's params and for FetchApp's config:

Unfortunately, due to the IDE limitations, rich autocomplete doesn't work when you attach FetchApp as a library (as opposed to copying the code). Nevertheless, you still have the description of possible options in the type hints:

Contributions are welcome. Feel free to submit pull requests or issues on GitHub.

How to Append Multiple Rows in Google Sheets with Apps Script

Dataful.Tech — Wed, 03 Jan 2024 23:27:30 +0000

It is often necessary to append multiple rows to a sheet in Google Sheets via Apps Script. Unfortunately, the built-in function Sheet.appendRow() can append only one row at a time.

Of course, you could loop through the rows and append them one by one like this:

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Some Sheet")
const rows = [...]

rows.forEach(row => sheet.appendRow(row))

However, this approach has several drawbacks:

It is slow: each append operation needs time, which is manageable when appending 2-3 rows but becomes inefficient with 100 or more.
Each append will trigger formulas that use this range making it even slower and delaying next append operations. further slowing down the process and delaying subsequent append operations. In cases with abundant data/formulas, the delay can stretch into tens of seconds or even minutes.
There's no guarantee that the script will successfully append all rows. Each Apps Script operation, especially interacting with external resources, is prone to random failures. It's crucial to consider: if the script fails on line X, what will be the state of the data. Reducing the number of operations enhances reliability.

Better Solution: Custom Function

The following function implements a more efficient solution, which appends all rows in a single operation—drastically improving speed and reliability:

/**
 * Appends rows to the given sheet with the specified values.
 *
 * @param {SpreadsheetApp.Sheet} sheet - The sheet to which rows will be appended.
 * @param {Array<Array<*>>} rows - A 2D array containing the values to be appended. Each inner array represents a row, and each element within an inner array represents a cell value.
 */
const appendRows = (sheet, rows) => {
  // Early exit if there are no values to append
  if (rows.length === 0) return

  // Get the range where the new rows should be appended:
  // starting from the row following the last row with data, column 1,
  // with dimensions matching the size of 'values'
  sheet.getRange(
    sheet.getLastRow() + 1,
    1,
    rows.length,
    rows[0].length         
  ).setValues(rows)  // Set the values in the specified range
}

Utilizing this function is straightforward:

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Some Sheet")
const rows = [...]

appendRows(sheet, rows)

The preceding JSDoc annotation provides a helpful message and type hints (albeit without IDE validation) when utilizing the function.

Optimizing the process of appending multiple rows in Google Sheets is crucial for maintaining efficient and reliable script operations. While the built-in Sheet.appendRow() function serves its purpose for appending single rows, the appendRows function shared above vastly improves performance and reliability when dealing with multiple rows.

By reducing the number of operations and avoiding formula triggers on each append, this function significantly speeds up the process, especially in sheets with a large amount of data or formulas. This example demonstrates the importance of fine-tuning Google Apps Script operations to better handle larger datasets, ensuring your scripts run smoothly and reliably.

How to Store Secrets in Google Apps Script

Dataful.Tech — Thu, 21 Dec 2023 20:15:41 +0000

Security of any IT system relies on many factors, including the security of credentials, API keys, and other essential details collectively known as "secrets." If leaked, these secrets can lead to the system being compromised, resulting in data breaches, deletion, scams, and other adverse effects.

This concern is particularly relevant for Google Apps Script. First, these scripts often need access to external systems, requiring you to store secrets within the script itself or somewhere close. Second, Apps Scripts are frequently linked to shared documents, automatically granting access to the script. Without proper care, these secrets may be exposed.

Storing secrets safely in Apps Script is complex. It relies on various surrounding factors, such as how the script is run, who has access to the document, and the volume of data updated. There's no universal solution; a comprehensive approach is required.

In this post, we'll explore different options for storing secrets in Apps Script, ranging from the least to most secure. We'll review the pros and cons of each approach and guide you in selecting the best method for your situation. To highlight the vulnerabilities of each option, we'll assume that any user with access to the credentials could be malicious. While this may seem drastic, it's a vital consideration, as even trustworthy users' accounts can be hacked, leading to unauthorized access to other systems.

Options for Storing Secrets in Apps Script

Secrets Directly in the Code

The simplest way to store secrets is to do it in the code as plain text:

const API_KEY = "not really a secret"

Pros:

Quick and simple.

Cons:

No security. Anyone with read permissions to the script or document the script is attached to, can access the secret.

While convenient, this method is only suitable if you alone have access to the script. Even then, it's risky.

Encrypted Secrets in the Code

Encrypting secrets and prompting the user for a "password" to decrypt them is another option. However, Apps Script lacks built-in cryptographic functionality, so external libraries like crypto-js are needed. Here is an example of how to use this library in Apps Script.

Pros:

More secure than plain text storage.

Cons:

The script must decrypt the secret at runtime, where it can be logged or otherwise compromised by anybody who has edit permissions.
Since the encrypted secret is accessible to anyone with read permissions, the perpetrator could save it and brute-force the password.
Since you are asking the user the password every time, you cannot schedule the script to run automatically.
Prompting the user for a password every time will encourage the user to store it less securely.

Overall, this method is complex and has many drawbacks. Plus there are more appealing alternatives.

Script Properties

Google Apps Script provides a PropertiesService that allows you to store values attached to a user, script, or document (if there is an attached document). In the documentation Google even mentions that Script Properties are typically used to store "the username and password for the developer's external database". Let's see how it can work.

// Retrieves secret from the Script Properties or throws an error
function getScriptSecret(key) {
  let secret = PropertiesService.getScriptProperties().getProperty(key)
  if (!secret) throw Error(`Secret ${key} is empty`)
  return secret
}


// Example
const API_KEY = getScriptSecret("API_KEY")
const CLIENT_ID = getScriptSecret("CLIENT_ID")

You (and everybody else with edit access) can see and manage the script properties in the settings:

Pros:

Only script editors will have access to the secrets.

Cons:

All script editors will have access to the secrets. Editors include all accounts that have edit access to the document where this script is attached to.

This is a reasonably good option if you absolutely trust the people who have edit access. If a script is attached to a document, you could also use document properties, although they do not offer any additional benefits and will not be visible in the settings.

User Properties

Similar to the Script Properties, you could store secrets in User Properties. These properties are accessible only to the user who created them. There is no interface to edit them: you will need either to manually save them the first time or prompt the user for secrets, if they are not there:

// Retrieves or prompts and stores a secret in UserProperties
function getSecret(key) {
    const userProperties = PropertiesService.getUserProperties()
    let secret = userProperties.getProperty(key)
    if (secret) return secret

    // If secret is not set, prompt the user to enter it
    const ui = SpreadsheetApp.getUi()
    const result = ui.prompt(
        "Secret Management",
        `Please enter ${key}`,
        ui.ButtonSet.OK_CANCEL
    )

    // If user clicked "OK", save the secret to User Properties
    if (result.getSelectedButton() === ui.Button.OK) {
        secret = result.getResponseText()
        userProperties.setProperty(key, secret)
        return secret
    } else {
        // User clicked "CANCEL" or closed the prompt
        throw Error(`User has not entered the secret ${key}`)
    }
}

// Clears a secret from UserProperties
function clearSecret(key) {
  const userProperties = PropertiesService.getUserProperties()
  userProperties.deleteProperty(key)
}

// Clearing secrets
function clearSecrets() {
  clearSecret("API_KEY")
  clearSecret("CLIENT_ID")
}


// Example
const API_KEY = getSecret("API_KEY")
const CLIENT_ID = getSecret("CLIENT_ID")

Prompting user for the script:

Pros:

Only you will have access to the secret unless the script logs it at the runtime.
You can schedule the script to run periodically under your account.

Cons:

If other users can edit the script, they can log the secret when you run the script from your account (either manually or on trigger). Even if you catch it, the secrets will be compromised.
If you need other people to run the script on their own, you will have to provide them with the secrets.

Google Cloud’s Secret Manager

You could use Google Cloud Secret Manager to store the secrets and retrieve them from the Apps Script. The setup is more involved and deserves its own post. Here we will look at its pros and cons.

Pros:

You can re-use the secrets stored in the Manager in many different scripts. And, if you need to change them, you can do it in one place.
Same level of security as storing with User Properties.

Cons:

Requires https://www.googleapis.com/auth/cloud-platform scope which is very broad. More about permission scopes and their security implications.
Other users will not be able to run the script unless you grant them access to the secrets storage which lessens security.
This method is more difficult to set up both on the script and Google Cloud sides.
Google Cloud Secret Manager is a paid product (pricing).

A Detached Script

If many people need access to a document that needs to be updated with a script, you could use a completely detached script so only you or a very limited group will have access to it. In that script you could store the credentials either in the User or Script properties.

Pros:

It is one of the most secure options: only people who have edit access to the script can compromise the secrets.

Cons:

To update a document from a detached script, you will have to grant the script very wide permissions scopes like https://www.googleapis.com/auth/spreadsheets which would allow the script to do anything with your documents. This is not the safest approach. You can read about security scopes here.
From the script you cannot manipulate the user interface of the document: show alerts, custom menus, etc.
You will have to rely mostly on time scheduled script triggers. You cannot easily run the remote script from the document, for example, to do a manual update. You could still do it by using a script attached to a document and running the remote script via API or script.run. However, both things require a rather involved setup.

Two Documents, Attached Script, and IMPORTRANGE

Similar to the previous method, you could separate presentation of the data from its update. To achieve it, create two documents: one for presentation and another for storing raw data. They will be connected via an IMPORTRANGE function so Google will manage updating data between the documents.

Pros:

It is one of the most secure options: only people with edit access to the second document/script could compromise the secrets.
The update script does not need extra permissions.

Cons:

You will still have to rely mostly on scheduled runs of the script.
You will be subject to IMPORTRANGE limitations: there is a limit on volume of data that can be transferred in one go. According to the documentation it is 10 MB per update. In practice it usually means that you can import up to 200,000 cells in one IMPORTRANGE. Plus, if the source sheet is updated too often, your updates can be throttled. Read more on how to deal with IMPORTRANGE limitations.
Sometimes IMPORTRANGE errors out due to random factors. To mitigate it you will need to wrap it in IFERROR function that will retry if an error occurs:

=IFERROR(
    IMPORTRANGE("<document>", "<range>"),
    IMPORTRANGE("<document>", "<range>")
)

Do Not Store Secrets, Prompt User Every Time

The safest way to store secrets in Apps Script is not to store them at all. Each time the user runs a script, you could ask them for the secret. The code is similar to the one above where we were storing the secrets in User Properties:

function promptUserForSecret(key) {
    // Prompt the user to enter it
    const ui = SpreadsheetApp.getUi()
    const result = ui.prompt(
        "Secret Management",
        `Please enter ${key}`,
        ui.ButtonSet.OK_CANCEL
    )

    // If user clicked "OK"
    if (result.getSelectedButton() === ui.Button.OK) {
        return result.getResponseText()
    } else {
        // User clicked "CANCEL" or closed the prompt
        throw Error(`User has not entered the secret ${key}`)
    }
}


// Example
const API_KEY = promptUserForSecret("API_KEY")
const CLIENT_ID = promptUserForSecret("CLIENT_ID")

Pros:

The secret is not stored anywhere and can only leak if the script logs or otherwise records the it.

Cons:

You will be limited to running the script manually: it will not have the secrets when run on time trigger.
This method is still vulnerable to script edits: anybody who has edit access can modify the script and intercept the secret.
Forcing users to enter the secret every time, nudges them to store it somewhere accessible which can be less secure than doing it with other options.
Every user who you needs to run the script has to explicitly know the secrets.

How to Choose Optimal Way to Store Secrets

As you can see, there are multiple ways to store secrets in Apps Script. There is no one-size-fits-all approach. When choosing a way to store secrets in Apps Script, it's essential to take into account various factors. Here's a breakdown to help guide your decision:

Considerations About Other People:
- Read Access: If others have read access, avoid storing secrets in plain text. Anyway, it is better to avoid it altogether.
- Edit Access: If others can edit the document or script, assess trust levels. Even trusted individuals can have their accounts compromised.
Script Use Case:
- Run Type: Whether the script runs on schedule or manually will impact your options for storing secrets.
- User Interaction: If the script needs to be run manually, will it be only you or other users as well?
Functionality of the Script:
- How much and what kind of data will the script transfer? Can it be imported into a separate document and transferred with an IMPORTRANGE?
- Does the script need to interact with the user interface: alerts, prompts, custom menus, etc.?
Consequences of Compromised Secrets:
- The more damaging the result, the more paranoid you should be when storing the secrets.
- What data will the leaked secrets grant access to?
- Do you track access with those secrets?
- Can you revoke or invalidate those secrets?
- Can user update or delete data using those secrets?

This is a chart that illustrates common scenarios and questions when choosing a way to store secrets in Apps Script.

It is important to always keep security of your Apps Script projects in mind and storing secrets is an integral part of this issue. Hopefully, this guide has provided you with valuable insights into the various methods available for storing secrets and how to choose the best for your situation.

Secure Your Data: Understand and Manage Authorization Scopes in Google Apps Scripts

Dataful.Tech — Tue, 19 Dec 2023 14:57:38 +0000

If you ever used Apps Scripts or add-ons for Google Drive/Docs/Sheets, let alone developed them, you have engaged with authorization scopes, whether you're familiar with the term or not. For instance, the first time you run a new script you have to grant it specific permissions:

Sometimes you may even need to ignore Google's security warnings indicating the potential risks associated with running a script:

In this article, we'll delve into understanding what authorization scopes are, their importance, and how to audit and revoke them. This information is particularly crucial if you frequently use scripts: you might wish to revoke permissions that are no longer relevant. In another post we will see how to properly set scopes a script needs.

Understanding Authorization Scopes and Their Importance

Authorization scopes define what a script can do on your behalf concerning your data and account on Google. Security is the primary reason to be concerned about them. To illustrate, let's compare two different scopes:

"See, edit, create, and delete all your Google Sheets spreadsheets" (scope https://www.googleapis.com/auth/spreadsheets) - This allows the script full access to all spreadsheets you can access. Any action it performs is as though you did it yourself, including creating, reading, modifying, or deleting anything.

"View and manage spreadsheets that this application has been installed in" (scope https://www.googleapis.com/auth/spreadsheets.currentonly) - The script will only have access to the document where it was installed.

If you work alone and don't share your documents and scripts with anyone, the distinction might not seem significant, and it may seem reasonable to grant broader permissions. However, even in this scenario, it's advisable to use a more restrictive scope.

Suppose you shared a spreadsheet that includes an attached script with another editor. The script is also shared, and the editor can modify it, possibly to copy data or entire documents when you execute the script. Furthermore, if the script is set to run automatically (e.g., periodically via a time trigger), it could execute under your name unexpectedly.

Such instances can lead to data leaks and losses, with potential business and legal implications. A compromised account is all it takes for a security breach, and with the rise of cybercrime, it's essential to stay vigilant and minimize risks early on.

Another challenging aspect of scopes is that it's not straightforward to view the permissions you've granted and revoke them. We'll explore the different options in the following section.

Auditing and Revoking Scopes

Several tools allow you to see the permissions you've granted and potentially detect any unusual activities. Google continually enhances these mechanisms, so the actual details may differ slightly from you we will see below.

Security Checkup

In your account's Security section, you can find the Security Checkup. This tool highlights potential issues, such as scripts from unverified developers or scripts with dangerously permissive authorization scopes.

Here you can see the permissions you've granted to a given script:

Third-party Apps & Services

Again in the Security section, you can find Third-party apps & services. It is similar to the Security Checkup, lets you filter by permission type or application (script) name:

Then you can review permissions for each of the apps and decide whether to revoke them:

Review Triggers

Triggers enable scripts to execute without your direct involvement, such as at specific times. However, this feature presents a risk: if someone else can modify the script, they can make it do anything within the script's authorization scopes. You can review all your account's triggers here and make necessary adjustments or deletions.

While removing triggers doesn't change the scopes, it reduces the risk that the scope can be misused.

Going Through Scripts Manually

The script.google.com portal displays all the scripts/projects you've created or have been given access to. This method is a bit more tedious but helpful for in-depth reviews or identifying specific scripts.

Each Apps Script project has an overview section where you can see scopes requested by the scripts. However, there you cannot see whether you granted those permissions. For that you need to go to either Security Checkup or Third-party apps & services in the account settings (more on that below).

Another place where you might be able to see permissions is the manifest file. However, by default it is hidden and doesn't contain the permissions. More on the manifest file here.

Review Script Executions

This section allows you to see recent script executions under your account, access the relevant project, view logs, and more. This can be useful for a more in-depth review.

Remember, scopes are a vital aspect of your data security. While Google provides several tools for auditing and revoking them, it's ultimately up to you to use them wisely and minimize the risk of breaches. Stay vigilant, regularly review your permissions, and remain proactive in maintaining your data's safety.

Authorization Scopes in Google Apps Script: Convenience vs. Security

Dataful.Tech — Sun, 17 Dec 2023 14:16:05 +0000

Authorization scopes are a crucial element of any Apps Script, as they determine what your script is allowed to do. More importantly, they set the boundaries for the script, providing security (more on that here). That is why it is vital to understand how to properly set the required scopes and which scopes to use.

How to set scopes in Apps Script

There are two ways to identify the permission scopes required for a script: automatically and manually (explicitly).

Setting Scopes Automatically

In automatic mode, when you run the script, Google checks if the script uses functions that require specific permissions, such as UrlFetchApp, SpreadsheetApp (often they end with "App"). If it does, Google will ask you for the permissions required to work with those functions. This is very convenient for the developer, yet it does compromise security.

In the example above, Google will ask you to grant the script two permissions: full access to all your spreadsheets (SpreadsheetApp) and access to external services (UrlFetchApp):

Access to external services is acceptable -- there is no other way to achieve the same result, and on its own, it is difficult to misuse. However, full access to all spreadsheets is too broad. In most cases, you want to write data to the sheet the script is attached to. Thus, you only need access there, which would be much safer. There is a special permission for that which needs to be set manually.

Setting Scopes Manually (Explicitly)

The best security practice is to request as narrow permissions as possible, which would still be enough to do the job. In this case, if anything goes wrong (whether it's a bug in the code or a security breach), the scope of the problem will be limited. To do this with Google Apps Script, you need to identify the required permissions explicitly.

Every Apps Script project has an appscript.json manifest file, which is hidden by default (I guess this is to promote bad security practices). To make it accessible, you need to open the project's settings and enable Show "appsscript.json" manifest file in editor:

After that, the manifest file will appear in the list of files and will always be at the top. By default, it will contain only basic details of the project:

To set the proper authorization scopes, you need to add the oauthScopes property with the values. Following the example above, to require permission to access external services and the spreadsheet the script is attached to, the file should look as follows:

{
  "timeZone": "America/Toronto",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets.currentonly",
    "https://www.googleapis.com/auth/script.external_request"
  ]
}

Important: If you set permissions explicitly, you must provide an exhaustive list of permissions the script needs. Otherwise, performing an action that you do not have permission for will cause an error.

What Scopes to Use

There are three types of authorization scopes: regular, sensitive (containing user data like email, name, etc.), and restricted (examples). Depending on your use case, you might need to go through a special authorization process to use sensitive or restricted scopes. If you have fewer than 100 users of a given script, you usually do not have to do this, but you will be subject to the unverified app screen.

You can find a list of most of the scopes for many Google services here.

Let's look at the most commonly used scopes:

Access only to the document the script is attached to -- a must-have and the easiest way to make your sheets safer. These scopes are so safe that on their own, they do not require a security warning (and with this trick you can work with the data from other sheets without more extensive permissions):
- Sheets: https://www.googleapis.com/auth/spreadsheets.currentonly
- Docs: https://www.googleapis.com/auth/documents.currentonly
- Slides: https://www.googleapis.com/auth/presentations.currentonly
Read-only scopes. These are the next best choice if you need to read data from documents other than the one where the script is installed.
- Sheets: https://www.googleapis.com/auth/spreadsheets.readonly
- Docs: https://www.googleapis.com/auth/documents.readonly
- Slides: https://www.googleapis.com/auth/presentations.readonly
- Drive: https://www.googleapis.com/auth/drive.readonly
Complete access to all documents of a given type. These are some of the most dangerous scopes possible and are highly discouraged unless absolutely necessary. Even then, use them with security precautions.
- Sheets: https://www.googleapis.com/auth/spreadsheets
- Docs: https://www.googleapis.com/auth/documents
- Slides: https://www.googleapis.com/auth/presentations
- Drive: https://www.googleapis.com/auth/drive
Making requests to external services via UrlFetchApp: https://www.googleapis.com/auth/script.external_request
Getting the email of the user running the script via Session.getActiveUser().getEmail(): https://www.googleapis.com/auth/userinfo.email
Accessing the user interface (UI): https://www.googleapis.com/auth/script.container.ui. This is mostly for complex UI interactions when creating custom interfaces. It does not include simple alerts and custom menus -- you can do those without this permission.
Creating and deleting triggers, working with user's permissions via ScriptApp: https://www.googleapis.com/auth/script.scriptapp. Use this scope with caution, as it allows the creation of triggers, and triggers can run the script in your name without your knowledge. You can manage existing triggers here.

We have reviewed two ways to set permission scopes. The main takeaways:

Always set scopes explicitly, without relying on auto-detection which is often too permissive. It takes a minute to set up in the very beginning but makes our lives easier and safer later.
Use as narrow scopes as possible.
Bonus point: learn how to audit scopes you have already granted.