Using Firestore in Apps Script

When using Apps Script, sometimes the CacheService and PropertiesService do not match the requirements of the project – perhaps there a need for a longer ttl or storing many more values. In these cases, Firestore can be used!

Setup

To use Firestore in Apps Script, you will need to enable the Firestore API in the Google Cloud Console.
You will also need to add the following scopes to your Apps Script project:

{
  "oauthScopes": [
    "https://www.googleapis.com/auth/datastore",
    "https://www.googleapis.com/auth/script.external_request"
  ]
}

Finally, you will need to set the Cloud project id in the Apps Script settings.
Create a collection named kv in Firestore so the examples below will work.

This post is going to be using the Firestore REST API with OAuth access tokens via ScriptApp.getOAuthToken(). Alternatively, you could use a service account.

UrlFetchApp and the Firestore REST API

The UrlFetchApp can be used to make requests to the Firestore REST API. I wrap the UrlFetchApp in two function layers to make it easier to use with the OAuth token and handle errors. The first is a simple wrapper to add the OAuth token to the request header.

/**
 * Wraps the `UrlFetchApp.fetch()` method to always add the 
 * Oauth access token in the header 'Authorization: Bearer TOKEN'.
 * 
 * @params {string} url
 * @params {Object=} params
 * @returns {UrlFetchApp.HTTPResponse}
 */
function fetchWithOauthAccessToken__(url, params = {}) {
  const token = ScriptApp.getOAuthToken();
  const headers = {
    Authorization: `Bearer ${token}`,
    "Content-type": 'application/json',
  };
  params.headers = params.headers ?? {};
  params.headers = { ...headers, ...params.headers };
  return UrlFetchApp.fetch(url, params);
}

I didn’t evaluate the performance impacts of repeated ScriptApp.getOAuthToken() calls.

The second function layer is a wrapper to handle errors and parsing that I included as part of the Firestore class I created (more later).

class Firestore {
  // ... omitted
  fetch(url, options) {
    options = {
      ...options,
      muteHttpExceptions: true
    }
    const response = fetchWithOauthAccessToken__(url, options);
    if (response.getResponseCode() < 300) {
      return JSON.parse(response.getContentText());
    } else {
      throw new Error(response.getContentText());
    }
  }
}

Firestore class for Apps Script

To abstract some of the common methods, I created a Firestore class. This class is not meant to be a complete wrapper of the Firestore REST API, but rather a starting point.

Below is the .patch() method as an example which transforms the payload to JSON and passes it to the .fetch() wrapper method.

class Firestore {
  // ... omitted
  /**
   * @params {string} documentPath
   * @params {Object=} params Include parameters such as `updateMask`, `mask`, etc
   * @params {Object=} payload
   */
  patch(documentPath, params = {}, payload) {
    return this.fetch(
      this.url(documentPath, params), 
      { method: Methods.PATCH, payload: JSON.stringify(payload) }
    );
  }
}

I also included a url method to generate the Firestore REST API url and include any parameters. This method is used by the other methods to generate the url.

class Firestore {
  /**
   * @params {string} projectId
   * @params {string} [databaseId="(default)"]
   */
  constructor(projectId, databaseId = "(default)") {
    this.basePath = `https://firestore.googleapis.com/v1/projects/${projectId}/databases/${databaseId}/documents`
  }
  // ... omitted
  /**
   * @params {string} documentPath
   * @params {Object=} params Include parameters such as `updateMask`, `mask`, etc
   */
  url(documentPath, params = {}) {
    return encodeURI([
      `${this.basePath}${documentPath}`, 
      Object.entries(params).map(([k, v]) => `${k}=${v}`).join("&")
      ].join("?"));
  }
}

This could be extended as necessary for queries, collections, etc.

Firestore typed documents

When using the Firestore REST API, documents are represented with a JSON object containing their types. Below is an example of a document with a nested object and array.

{
  "fields": {
    "name": {
      "stringValue": "John Doe"
    },
    "age": {
      "integerValue": "30"
    },
    "address": {
      "mapValue": {
        "fields": {
          "street": {
            "stringValue": "123 Main St"
          },
          "city": {
            "stringValue": "New York"
          },
          "state": {
            "stringValue": "NY"
          },
          "zip": {
            "stringValue": "10001"
          }
        }
      }
    },
    "hobbies": {
      "arrayValue": {
        "values": [
          {
            "stringValue": "hiking"
          },
          {
            "stringValue": "biking"
          }
        ]
      }
    }
  }
}

I didn’t bother with wrapping and unwrapping this, but a helper function could do this for you. See this GitHub library, grahamearley/FirestoreGoogleAppsScript/Document.ts for an example implementation.

Usage of the Apps Script Firestore class

Below is an example of using the Firestore class to patch, get, and delete a document in a collection I had already created named kv.

function main() {
  const db = new FirestoreService(PROJECT_ID, DATABASE_ID);
  const doc = {
    fields: {
      foo: {
        stringValue: "test"
      }
    }
  };
  console.log(db.patch("/kv/test", {}, doc,));
  console.log(db.get("/kv/test"));
  console.log(db.delete("/kv/test"));
}

This outputs the following:

10:30:56 AM   Notice  Execution started
10:30:57 AM   Info    { name: 'projects/OMITTED/databases/(default)/documents/kv/test',
  fields: { foo: { stringValue: 'test' } },
  createTime: '2024-01-08T21:52:09.794036Z',
  updateTime: '2024-01-10T18:30:57.728011Z' }
10:30:58 AM   Info    { name: 'projects/OMITTED/databases/(default)/documents/kv/test',
  fields: { foo: { stringValue: 'test' } },
  createTime: '2024-01-08T21:52:09.794036Z',
  updateTime: '2024-01-10T18:30:57.728011Z' }
10:30:58 AM   Info    {}
10:30:58 AM   Notice  Execution completed

Future experiments with Firestore in Apps Script

Use Firestore rules for segmenting user data
Use Firestore as a larger cache than the CacheService
Use a service account instead of OAuth access tokens

You may want to consider using the library FirestoreGoogleAppsScript instead of the code in this post. It is a more complete wrapper of the Firestore REST API, however there is a balance to using an incomplete external library vs writing a small amount of code yourself as demonstrated here.

Complete code

const PROJECT_ID = "OMITTED"; // Update this
const DATABASE_ID = "(default)"; // Maybe update this
/**
 * @readonly
 * @enum {string}
 */
var Methods = {
  GET: "GET",
  PATCH: "PATCH",
  POST: "POST",
  DELETE: "DELETE",
};
/**
 * Wrapper for the [Firestore REST API] using `URLFetchApp`.
 * 
 * This functionality requires the following scopes:
 *  "https://www.googleapis.com/auth/datastore",
 *  "https://www.googleapis.com/auth/script.external_request"
 */
class FirestoreService {
  /**
   * @params {string} projectId
   * @params {string} [databaseId="(default)"]
   */
  constructor(projectId, databaseId = "(default)") {
    this.basePath = `https://firestore.googleapis.com/v1/projects/${projectId}/databases/${databaseId}/documents`
  }
  /**
   * @params {string} documentPath
   * @params {Object=} params Include parameters such as `updateMask`, `mask`, etc
   */
  get(documentPath, params = {}) {
    return this.fetch(
      this.url(documentPath, params),
      { method: Methods.GET }
    );
  }
  /**
   * @params {string} documentPath
   * @params {Object=} params Include parameters such as `updateMask`, `mask`, etc
   * @params {Object=} payload
   */
  patch(documentPath, params = {}, payload) {
    return this.fetch(
      this.url(documentPath, params),
      { method: Methods.PATCH, payload: JSON.stringify(payload) }
    );
  }
  /**
   * @params {string} documentPath
   * @params {Object=} params Include parameters such as `updateMask`, `mask`, etc
   * @params {Object=} payload
   */
  create(documentPath, params = {}, payload) {
    return this.fetch(
      this.url(documentPath, params),
      { method: Methods.POST, payload: JSON.stringify(payload) }
    );
  }
  /**
   * @params {string} documentPath
   * @params {Object=} params Include parameters such as `updateMask`, `mask`, etc
   */
  delete(documentPath, params = {}) {
    return this.fetch(
      this.url(documentPath, params),
      { method: Methods.DELETE}
    );
  }
  /**
    * @params {string} documentPath
    * @params {Object=} params Include parameters such as `updateMask`, `mask`, etc
    */
  url(documentPath, params = {}) {
    return encodeURI([
      `${this.basePath}${documentPath}`, 
      Object.entries(params).map(([k, v]) => `${k}=${v}`).join("&")
      ].join("?"));
  }
  /**
   * @params {string} documentPath
   * @params {Methods} method
   * @params {Object} options
   * @params {Object=} params Include parameters such as `updateMask`, `mask`, etc
   */
  fetch(url, options) {
    options = {
      ...options,
      muteHttpExceptions: true
    }
    const response = fetchWithOauthAccessToken__(url, options);
    if (response.getResponseCode() < 300) {
      return JSON.parse(response.getContentText());
    } else {
      throw new Error(response.getContentText());
    }
  }
}
/**
 * Wraps the `UrlFetchApp.fetch()` method to always add the 
 * Oauth access token in the header 'Authorization: Bearer TOKEN'.
 * 
 * @params {string} url
 * @params {Object=} params
 * @returns {UrlFetchApp.HTTPResponse}
 */
function fetchWithOauthAccessToken__(url, params = {}) {
  const token = ScriptApp.getOAuthToken();
  const headers = {
    Authorization: `Bearer ${token}`,
    "Content-type": 'application/json',
  };
  params.headers = params.headers ?? {};
  params.headers = { ...headers, ...params.headers };
  return UrlFetchApp.fetch(url, params);
}
function main() {
  const db = new FirestoreService(PROJECT_ID, DATABASE_ID);
  const doc = {
    fields: {
      foo: {
        stringValue: "test"
      }
    }
  };
  console.log(db.patch("/kv/test", {}, doc,));
  console.log(db.get("/kv/test"));
  console.log(db.delete("/kv/test"));
}

Blog