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);
}

Everything is working fine but i need to change the name

How did you fix it?

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

I'm getting this error:

It's useful! i got it .thanks you.

This is a working code for Firestore sync.

It was inspired by those additional materials:

• https://levelup.gitconnected.com/import-data-from-google-sheets-to-firestore-using-google-apps-script-b6f857f82a2
• https://github.com/grahamearley/FirestoreGoogleAppsScript

Enjoy!

 var environment = {
   spreadsheetID: "",
   email: "",
   key: "",
   projectId: ""
 };
 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 = {};

 var email = getEnvironment().email;
 var privateKey = getEnvironment().key;
 var projectId = getEnvironment().projectId;
 var firestore = FirestoreApp.getFirestore (email, privateKey, projectId);

 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]);
   }

   firestore.updateDocument(name + "/" + data[i][0],dataToImport[i]);
 }

}```

Hey thanks for the code. I'm a bit new to AppsScript. Can you tell me about the code:
initialize(e)

This is a special reserved trigger functions? I could not find it anywhere on Apps Script documentation.

I want to sync test sheet only.

change you writeDataToFirebase(sheetID)() function accordingly

function writeDataToFirebase(sheetID) {
var ss = SpreadsheetApp.openById(sheetID);
SpreadsheetApp.setActiveSpreadsheet(ss);
createSpreadsheetEditTrigger(sheetID);
var sheets = ss.getSheets();
importSheet("test");
SpreadsheetApp.setActiveSheet("test");
}
/if this doesn't work, try removing double quotes from the parenthesis/

I tried to modify this part of the code but without success, every time I modify other page it shows on firebase too. Any idea on how to do that?

ReferenceError: "FirebaseApp" is not defined. (line 90, file "Code")

I'm using the same code with my own spreadsheet_id and firebase_url
pls help

For anyone getting that line 49 error.

Not really sure what worked for me, did a bunch of things so you can replicate them:

1. Grabbed the url for sharing from Google sheet and paste it in spreadsheetID: "/" and added that ' / ' at the end of the link. And yes, paste the entire url, not only the id.
2. Changed both openById to openByUrl.

Happy coding!

Any advice if there is a way to manipulate what u parse from a specific field.

I have complete excel tables with investments and need to parse only some fields to graphically represent data.

• there are a lot of tables but they are uniform, meaning, the data is always in the same field (F3 for example to write in a var called 'money')?

I'm getting a Type error: Could not find split function in object Fri 12/29/1899 23:50:39 GMT-0000 (GMT). (line 82, file "Code"). Any ideas?

The "writeDataToFirebase" function needs to be tweaked for SpreadSheets with multiple sheets, otherwise it will not import that last one. Inside the for loop, you need to set the active sheet before calling the importSheet function otherwise the active sheet is still from the previous iteration.

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++) {
   SpreadsheetApp.setActiveSheet(sheets[i]);
   importSheet(sheets[i]);
 }
}

Type error: Could not find split function in object

However, if I use the initialize function for my trigger set up, it works. But importSheet throws the above error. Any ideas?

Hi! Have you fix that error? I have the same one

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.

hello @TIMAI2

can u help me to use google sheet to connect realtime database firebase?

@ixn3rd3mxn

Perhaps see here:

https://stackoverflow.com/questions/65862681/cannot-add-firebase-library-to-apps-script

@ixn3rd3mxn I needed this functionality, and created one here: https://github.com/jeymichael/GoogleSheets_Firestore
Check it out.

Very good @jeymichael , but can you do one for the firebase realtime database?