-
-
Save edwinlee/85ac9033a133d056a8ded6b74f27f30f to your computer and use it in GitHub Desktop.
| { | |
| "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); | |
| } |
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
I have the same issue.Did you solved it?
Anyone know why data is not showing up in database even after successful execution.
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 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?
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.