How to Store Secrets in Google Apps Script

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:

1. 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.

2. 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?

3. 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.?

4. 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.