Last active
February 21, 2024 10:43
-
-
Save edwinlee/85ac9033a133d056a8ded6b74f27f30f to your computer and use it in GitHub Desktop.
Sync a Google Sheets spreadsheet to a Firebase Realtime database
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
{ | |
"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" | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
* 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); | |
} |
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?
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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.