How to Store Secrets in Google Apps Script

Updated: March 2024

The 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 compromise the system, resulting in data breaches, deletions, 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, Google 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 updated data volume. There’s no universal solution; a comprehensive approach is required.

This article will explore different options for storing secrets in Google Apps Script, ranging from the least to the most secure. We’ll review the pros and cons of each approach and guide you in selecting the best method for your situation:
1. Secrets directly in the code as plain text
2. Encrypted secrets in the code
3. Script Properties
4. User Properties
5. Google Cloud Secret Manager
6. A detached script
7. Two documents, attached Script, and IMPORTRANGE
8. Do not store secrets; prompt the user every time

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.

Finally, we will discuss choosing the optimal way to store secrets.

1. Secrets Directly in the Code as Plain Text

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.

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

• It is 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 ask the user for 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.

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

 1// Retrieves secret from the Script Properties or throws an error
 2function getScriptSecret(key) {
 3  let secret = PropertiesService.getScriptProperties().getProperty(key)
 4  if (!secret) throw Error(`Secret ${key} is empty`)
 5  return secret
 6}
 7
 8
 9// Example
10const API_KEY = getScriptSecret("API_KEY")
11const 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 with edit access to the document this script is attached to.

This is a good option if you absolutely trust people with 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.

4. 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 user properties: you will need to either manually save them the first time or prompt the user for secrets, if they are not there.

 1// Retrieves or prompts and stores a secret in UserProperties
 2function getSecret(key) {
 3    const userProperties = PropertiesService.getUserProperties()
 4    let secret = userProperties.getProperty(key)
 5    if (secret) return secret
 6
 7    // If secret is not set, prompt the user to enter it
 8    const ui = SpreadsheetApp.getUi()
 9    const result = ui.prompt(
10        "Secret Management",
11        `Please enter ${key}`,
12        ui.ButtonSet.OK_CANCEL
13    )
14
15    // If user clicked "OK", save the secret to User Properties
16    if (result.getSelectedButton() === ui.Button.OK) {
17        secret = result.getResponseText()
18        userProperties.setProperty(key, secret)
19        return secret
20    } else {
21        // User clicked "CANCEL" or closed the prompt
22        throw Error(`User has not entered the secret ${key}`)
23    }
24}
25
26// Clears a secret from UserProperties
27function clearSecret(key) {
28  const userProperties = PropertiesService.getUserProperties()
29  userProperties.deleteProperty(key)
30}
31
32// Clearing secrets
33function clearSecrets() {
34  clearSecret("API_KEY")
35  clearSecret("CLIENT_ID")
36}
37
38
39// Example
40const API_KEY = getSecret("API_KEY")
41const CLIENT_ID = getSecret("CLIENT_ID")

Prompting user for the script:

Pros:

• Only you can access the secret unless the script logs it at 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 a trigger). Even if you catch it, the secrets will be compromised.
• If you need other people to run the script independently, you will have to give them the secrets.

5. Google Cloud’s Secret Manager

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

Pros:

• The secrets stored in the Manager can be reused in many different scripts. If you need to change them, you can do it in one place.
• The same level of security as storing with User Properties.

Cons:

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

6. 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. You could store the credentials in the User or Script properties in that script.

Pros:

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

Cons:

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

7. Two Documents, Attached Script, and IMPORTRANGE

Similar to the previous method, you could separate the data’s presentation from its update. To achieve this, 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 the volume of data that can be transferred in one go. According to the documentation, it is 10 MB per update. In practice, you can import up to 200,000 cells in one IMPORTRANGE. Plus, your updates can be throttled if the source sheet is too often. 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 the IFERROR function that will retry if an error occurs:

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

8. Do Not Store Secrets; Prompt User Every Time

The safest way to store secrets in Apps Script is to not 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:

 1function promptUserForSecret(key) {
 2    // Prompt the user to enter it
 3    const ui = SpreadsheetApp.getUi()
 4    const result = ui.prompt(
 5        "Secret Management",
 6        `Please enter ${key}`,
 7        ui.ButtonSet.OK_CANCEL
 8    )
 9
10    // If user clicked "OK"
11    if (result.getSelectedButton() === ui.Button.OK) {
12        return result.getResponseText()
13    } else {
14        // User clicked "CANCEL" or closed the prompt
15        throw Error(`User has not entered the secret ${key}`)
16    }
17}
18
19
20// Example
21const API_KEY = promptUserForSecret("API_KEY")
22const CLIENT_ID = promptUserForSecret("CLIENT_ID")

Pros:

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

Cons:

• You will be limited to running the script manually: it will not have the secrets when run on a time trigger.
• This method is still vulnerable to script edits: anybody with 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 you need to run the script must know the secrets explicitly.

How to Choose the 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 consider various factors. Here’s a breakdown to help guide your decision:

1. Considerations About Other People:

   • Read Access: Avoid storing secrets in plain text, if others have read access. 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 run only by you or by other users as well?

3. The 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 users update or delete data using those secrets?

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

It is essential 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 valuable insights into the various methods available for storing secrets and how to choose the best for your situation.