Sync data between Google Firestore and Google Sheets using Cloud Functions/Admin SDK

Question

While using Cloud Firestore as data backend, I need to share some data collections with non-tech site managers (editors, sales teams, etc.). Also, I wish to give these people access to edit the data stored in Cloud Firestore.

Google Sheets is a very familiar tool with site managers which can save me time in developing a CRUD admin panel like the interface from scratch for data updating and viewing.

This Stack Overflow answer shows how to send data using cloud function and levels deep, and this Github library can get data from Firestore using Google Apps Script (I wish to do it using Cloud Functions or Firebase Admin SDK), but I am still trying to figure out how to make an end-to-end Sheets based interface.

Please guide if there are any better alternatives to achieve the same objective. I'm facing some difficulties switching from SQL databases and Django auto-created admin interfaces to the Firebase-Firestore NoSQL world.

Answer 1

I understand that you want to be able to call a Cloud Function from a Google Sheet in order to build an "end-to-end Sheets based interface" for Firestore.

You can use the UrlFetchApp Class to make a request to fetch the URL of an HTTP Cloud Function.

You Apps Script code would be like:

function callSimpleHTTPCloudFunction() {

 const url = "https://xxxxxxxx.cloudfunctions.net/simpleHttp";

  response = UrlFetchApp.fetch(url, {
      method: 'get'
    })

  respObj = JSON.parse(response.getContentText());
  Logger.log(respObj);

}

While your Cloud Function would be like:

exports.simpleHttp = functions.https.onRequest((req, res) => {
  res.send({ msg: 'simpleHttp' });
});

This is a very simple example of Cloud Function, but you can adapt this Cloud Function to read and write data from/to Firestore. Have a look at this official video for a starting point: https://www.youtube.com/watch?v=7IkUgCLr5oA&t=1s&list=PLl-K7zZEsYLkPZHe41m4jfAxUi0JjLgSM&index=3

---

Now, if you want to authenticate your users in such a way you can control who can access your data through the Cloud Function, it is going to be a bit more complex.

There is an official Cloud Function Sample which shows "how to restrict an HTTPS Function to only the Firebase users of your app": https://github.com/firebase/functions-samples/tree/master/authorized-https-endpoint

As explained in the code comments: "The Firebase ID token needs to be passed as a Bearer token in the Authorization HTTP header like this: Authorization: Bearer <Firebase ID Token>. When decoded successfully, the ID Token content will be added as req.user."

So you need, in your Apps Script code, to generate a Firebase ID Token for the Firebase user. For that we will use the Firebase Auth REST API. In this example we will use the email of the user authenticated in the Google Sheet (Session.getActiveUser().getEmail()) as the Firebase User Name.

As explained in the doc, to call the Firebase Auth REST API, you need to obtain a Web API Key for your Firebase project, through the project settings page in your Firebase admin console.

The following Apps Script function will do the job:

function getToken() { {

  const userName = Session.getActiveUser().getEmail();
  const pwd = 'xyz' //For example get the password via a prompt. 
  //This is NOT the password of the account authenticated with Google Sheet, but the password of the Firebase user. In this example, the emails are the same but they are different accounts. 

  const verifyPasswordUrl = "https://www.googleapis.com/identitytoolkit/v3/relyingparty/verifyPassword?key=[API_KEY]" //Replace with your Web API Key

  const payload = JSON.stringify({"email":userName,"password": pwd,"returnSecureToken": true});

  const verifyPasswordResponse = UrlFetchApp.fetch(verifyPasswordUrl, {
        method: 'post',
        contentType: 'application/json',
        muteHttpExceptions: true,
        payload : payload
 });

 const token = JSON.parse(verifyPasswordResponse.getContentText()).idToken;
 return token;

} 

Then, still in Apps Script, you use the token in the call to the Cloud Function, as follows:

function callSecuredHTTPCloudFunction() {

  const authHeader = {"Authorization": "Bearer " + getToken()};

  const url = "https://us-central1-<yourproject>.cloudfunctions.net/securedHttp/";

  const response = UrlFetchApp.fetch(url, {
      method: 'get',
      headers: authHeader,
      muteHttpExceptions: true,
    });

  Logger.log(response);
  //Here do what you want with the response from the Cloud Function, e.g. populate the Sheet

}

The Cloud Function code would be as follows, adapted from the official example.

const functions = require('firebase-functions');
const admin = require('firebase-admin');
admin.initializeApp();

const cors = require('cors')({
  origin: true
});    
const express = require('express');
const cookieParser = require('cookie-parser')();

const app = express();

// Express middleware that validates Firebase ID Tokens passed in the Authorization HTTP header.
// The Firebase ID token needs to be passed as a Bearer token in the Authorization HTTP header like this:
// `Authorization: Bearer <Firebase ID Token>`.
// when decoded successfully, the ID Token content will be added as `req.user`.
const validateFirebaseIdToken = (req, res, next) => {
  console.log('Check if request is authorized with Firebase ID token');

  if (
    !req.headers.authorization ||
    !req.headers.authorization.startsWith('Bearer ')
  ) {
    console.error(
      'No Firebase ID token was passed as a Bearer token in the Authorization header.',
      'Make sure you authorize your request by providing the following HTTP header:',
      'Authorization: Bearer <Firebase ID Token>'
    );
    res.status(403).send('Unauthorized');
    return;
  }

  let idToken;
  if (
    req.headers.authorization &&
    req.headers.authorization.startsWith('Bearer ')
  ) {
    console.log('Found "Authorization" header');
    // Read the ID Token from the Authorization header.
    idToken = req.headers.authorization.split('Bearer ')[1];
    console.log(idToken);
  } else {
    // No cookie
    res.status(403).send('Unauthorized');
    return;
  }
  admin
    .auth()
    .verifyIdToken(idToken)
    .then(decodedIdToken => {
      console.log('ID Token correctly decoded', decodedIdToken);
      req.user = decodedIdToken;
      return next();
    })
    .catch(error => {
      console.error('Error while verifying Firebase ID token:', error);
      res.status(403).send('Unauthorized');
    });
};

app.use(cors);
app.use(cookieParser);
app.use(validateFirebaseIdToken);
app.get('/', (req, res) => {
  res.send(`Your email is  ${req.user.email}`);
});

// This HTTPS endpoint can only be accessed by your Firebase Users.
// Requests need to be authorized by providing an `Authorization` HTTP header
// with value `Bearer <Firebase ID Token>`.
exports.securedHttp = functions.https.onRequest(app);

---

You can very well write a similar function with a POST and a payload in order to send data from the Google Sheet to the Cloud Function and then write to Firestore.

Finally, note that you could implement the same approach for calling, from the Google Sheet, the Firestore REST API instead of calling Cloud Functions.