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);
}
@rutvij0027
Copy link

TypeError: FirebaseApp.getDatabaseByUrl is not a function (line 85, file "Code")

@rutvij0027
Copy link

TypeError: FirebaseApp.getDatabaseByUrl is not a function (line 85, file "Code")

anyone help me to sort this out

@parthpandey2000
Copy link

TypeError: FirebaseApp.getDatabaseByUrl is not a function (line 85, file "Code")

anyone help me to sort this out

Send screenshot of the error alongwith code you've used

@chawakasung
Copy link

Script function not found: mainDismiss

What's going on?

@lf2foce
Copy link

lf2foce commented Dec 8, 2020

does not work anymore, please help!

@zaid757
Copy link

zaid757 commented Dec 31, 2020

how to make it not nested data . anyone can help

@parthpandey2000
Copy link

parthpandey2000 commented Jan 1, 2021 via email

@zaid757
Copy link

zaid757 commented Jan 2, 2021

Yeah how to make the id not outside like the others set data?

@bishalkc
Copy link

bishalkc commented Jan 14, 2021

And if the problem continues, email me at parthpandey2000@gmail.com, I've a different method too write data in firebase using sheets On Mon, 25 May, 2020, 11:04 am Parth Pandey, parthpandey2000@gmail.com wrote:

Do not include (/) or() in your sheet ID On Sat, 23 May, 2020, 11:41 am Ethan-Chew, @.***> wrote: > @Ethan-Chew commented on this gist. > ------------------------------ > > I have tried both, however it still does not work > > [image: Screen Shot 2020-05-14 at 8 55 43 PM] > https://user-images.githubusercontent.com/44517446/81936786-4db6bf80-9625-11ea-9ae0-62902386e76f.png > > Does anyone know what happened here? I checked that the last trailing > slash of my firebase URL was included, but it did not work. > > Does anyone know how to fix this? I am also getting the same issue > > — > You are receiving this because you commented. > Reply to this email directly, view it on GitHub > https://gist.github.com/85ac9033a133d056a8ded6b74f27f30f#gistcomment-3315143, > or unsubscribe > https://github.com/notifications/unsubscribe-auth/APOKMSXEKDRG53LTUFAZH6DRS5SJFANCNFSM4J3CMMQA > . >

Error: Unauthorized request. (line 297, file "Code", project "FirebaseApp")
I'm getting this error......please help

I am getting the similar error, not sure what I am doing wrong.

I have using the same email on both spreadsheet and firebase, and i have read/write true in database rule. Any help will be highly appreciated

Screen Shot 2021-01-13 at 7 37 35 PM
Screen Shot 2021-01-13 at 7 42 16 PM

@rabinovich2006
Copy link

My code was working perfectly and was fetching data to the firbase realtime database. but now something happened.
and is not fetching anymore and I get error Exception: Invalid argument: id writeDataToFirebase @ Code.gs:49
I did not make any-changes and code was working just fine, all the sudden stoped fetching.
I ch

  1. ecked rules on RT database changed to "true" for write and read.
  2. checked all suggestions here: have a backslash in after Rt link, tried changing ID to URL, tried updating my spreadsheet ID.
  3. I am using the same email for both firebase and google spreadsheets.

Thanks

@SPrzemek
Copy link

Hello,
after I try to sync it (step 5) I get this error
image
PS. It's the same google account and all links are alright.

@SPrzemek
Copy link

Hi, I just had to go back to the new console to run the script and is worked. I went through all the steps (permissions, etc) but I don't see any data in the database. Anybody had this issue?

@sspataro
Copy link

hi, thank you very much for your code, is it to possible to import only specifics columns instead the full sheet ? I have a sheet very big but i would to import only some columns into firebase for use them in other project. Second, my original sheet doesn't have id column and of i will add it, i must to rewrite another app, is to possible to add an auto id on firebase or in your code ? thanks for your help

@ameeruljunaidi
Copy link

Everything is working fine but i need to change the name

How did you fix it?

@ameeruljunaidi
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

@airbel
Copy link

airbel commented Aug 7, 2021

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

@randomrandom
Copy link

randomrandom commented Aug 14, 2021

This is a working code for Firestore sync.

It was inspired by those additional materials:

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

}```

@tsengchan
Copy link

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.

@msb07
Copy link

msb07 commented Aug 30, 2021

Screen Shot 2020-07-02 at 11 44 48 AM 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?

@Subodh97
Copy link

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

@fglavica
Copy link

fglavica commented Nov 8, 2021

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!

@fglavica
Copy link

fglavica commented Nov 8, 2021

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')?

@gustawklyszcz
Copy link

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?

@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