Sync a Google Sheets spreadsheet to a Firebase Realtime database

appsscript.json

{
  "timeZone": "America/Los_Angeles",
  "dependencies": {
    "libraries": [{
      "userSymbol": "FirebaseApp",
      "libraryId": "1hguuh4Zx72XVC1Zldm_vTtcUUKUA6iBUOoGnJUWLfqDWx5WlOJHqYkrt",
      "version": "29",
      "developmentMode": true
    }]
  },
  "exceptionLogging": "STACKDRIVER",
  "oauthScopes": ["https://www.googleapis.com/auth/firebase.database", "https://www.googleapis.com/auth/userinfo.email", "https://www.googleapis.com/auth/spreadsheets", "https://www.googleapis.com/auth/script.scriptapp", "https://www.googleapis.com/auth/script.external_request"],
  "executionApi": {
    "access": "DOMAIN"
  }
}

Code.gs

/**
* Copyright 2019 Google LLC.
* SPDX-License-Identifier: Apache-2.0
*/

function getEnvironment() {
 var environment = {
   spreadsheetID: "<REPLACE WITH YOUR SPREADSHEET ID>",
   firebaseUrl: "<REPLACE WITH YOUR REALTIME DB URL>"
 };
 return environment;
}

// Creates a Google Sheets on change trigger for the specific sheet
function createSpreadsheetEditTrigger(sheetID) {
 var triggers = ScriptApp.getProjectTriggers();
 var triggerExists = false;
 for (var i = 0; i < triggers.length; i++) {
   if (triggers[i].getTriggerSourceId() == sheetID) {
     triggerExists = true;
     break;
   }
 }

 if (!triggerExists) {
   var spreadsheet = SpreadsheetApp.openById(sheetID);
   ScriptApp.newTrigger("importSheet")
     .forSpreadsheet(spreadsheet)
     .onChange()
     .create();
 }
}

// Delete all the existing triggers for the project
function deleteTriggers() {
 var triggers = ScriptApp.getProjectTriggers();
 for (var i = 0; i < triggers.length; i++) {
   ScriptApp.deleteTrigger(triggers[i]);
 }
}

// Initialize
function initialize(e) {
 writeDataToFirebase(getEnvironment().spreadsheetID);
}

// Write the data to the Firebase URL
function writeDataToFirebase(sheetID) {
 var ss = SpreadsheetApp.openById(sheetID);
 SpreadsheetApp.setActiveSpreadsheet(ss);
 createSpreadsheetEditTrigger(sheetID);
 var sheets = ss.getSheets();
 for (var i = 0; i < sheets.length; i++) {
   importSheet(sheets[i]);
   SpreadsheetApp.setActiveSheet(sheets[i]);
 }
}

// A utility function to generate nested object when
// given a keys in array format
function assign(obj, keyPath, value) {
 lastKeyIndex = keyPath.length - 1;
 for (var i = 0; i < lastKeyIndex; ++i) {
   key = keyPath[i];
   if (!(key in obj)) obj[key] = {};
   obj = obj[key];
 }
 obj[keyPath[lastKeyIndex]] = value;
}

// Import each sheet when there is a change
function importSheet() {
 var sheet = SpreadsheetApp.getActiveSheet();
 var name = sheet.getName();
 var data = sheet.getDataRange().getValues();

 var dataToImport = {};

 for (var i = 1; i < data.length; i++) {
   dataToImport[data[i][0]] = {};
   for (var j = 0; j < data[0].length; j++) {
     assign(dataToImport[data[i][0]], data[0][j].split("__"), data[i][j]);
   }
 }

 var token = ScriptApp.getOAuthToken();

 var firebaseUrl =
   getEnvironment().firebaseUrl + sheet.getParent().getId() + "/" + name;
 var base = FirebaseApp.getDatabaseByUrl(firebaseUrl, token);
 base.setData("", dataToImport);
}

This is a working code for Firestore sync.

Thank you for the great script! The only thing I had to tweak was to change openById to openByUrl and it works. Has anyone found a way to include removing documents from Firestore when the respective row is removed from Sheets?

I'm using both Firestore Database and Realtime, would that be a problem?

I'm getting this error:

same problem with me to . HAve you found any solution.if so
Please share and thanks in advance.

We're sorry, a server error occurred. Please wait a bit and try again.
writeDataToFirebase @ code.gs:49

can anyone help me with this error

I'm using both Firestore Database and Realtime, would that be a problem?
I'm getting this error:

same problem with me to . HAve you found any solution.if so Please share and thanks in advance.

Did you find solution? please reply

We're sorry, a server error occurred. Please wait a bit and try again. writeDataToFirebase @ code.gs:49 can anyone help me with this error

I have the same issue.Did you solved it?

Anyone know why data is not showing up in database even after successful execution.

@edwinlee

Any suggestions as to why this has stopped working for so many people? This used to work for me, but I get the following errors, one from the library (have tried version 29 and 30) the other three from the script:

10:41:58 AM Error Error: We're sorry, a server error occurred. Please wait a bit and try again. (anonymous) @ Code.gs:297 importSheet @ Code.gs:86 writeDataToFirebase @ Code.gs:49 initialize @ Code.gs:39

I do have a very simple function that works...

function exportToFB() { var fbUrl = "https://at-3c20-default-rtdb.firebaseio.com/"; var token = ScriptApp.getOAuthToken(); var ss = SpreadsheetApp.openById('1fwfKdQJYjh7EjQYhHvoFnp-H981mSYtWA'); var sh = ss.getSheetByName('Sheet1'); var msg = sh.getRange("B2").getDisplayValue(); var base = FirebaseApp.getDatabaseByUrl(fbUrl, token); base.setData("Message", msg); }

which creates:

https://at-3c20-default-rtdb.firebaseio.com/ |__ Message: "hello world"

so the library appears to be OK, and the basic routine is working.