Skip to content

Instantly share code, notes, and snippets.

@edwinlee
Last active February 21, 2024 10:43
Show Gist options
  • Star 51 You must be signed in to star a gist
  • Fork 23 You must be signed in to fork a gist
  • Save edwinlee/85ac9033a133d056a8ded6b74f27f30f to your computer and use it in GitHub Desktop.
Save edwinlee/85ac9033a133d056a8ded6b74f27f30f to your computer and use it in GitHub Desktop.
Sync a Google Sheets spreadsheet to a Firebase Realtime database
{
"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"
}
}
/**
* 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);
}
@griogar
Copy link

griogar commented Feb 7, 2022

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

@Kapazza
Copy link

Kapazza commented Apr 16, 2022

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?

@mklnchk
Copy link

mklnchk commented Jun 19, 2022

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

@MattBuilt1
Copy link

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?

@kshivakumar-code
Copy link

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

I'm getting this error: Screen Shot 2021-07-28 at 11 14 25 PM

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

@Akhil-2003
Copy link

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

@Krizzzty
Copy link

Krizzzty commented Feb 8, 2024

I'm using both Firestore Database and Realtime, would that be a problem?
I'm getting this error: Screen Shot 2021-07-28 at 11 14 25 PM

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

Did you find solution? please reply

@Krizzzty
Copy link

Krizzzty commented Feb 8, 2024

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

I have the same issue.Did you solved it?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment