Sync Excel data to Firebase Realtime Database || 2022

Step 1 : Create project in Firebase
Open Firebase : firebase.googl...
Click on “Get started” and create new project.
Once you create new project, click “Build” and select “Realtime Database”
Follow the instruction as shown in the video.
Step 2: Create google sheet
Open google sheet
Create new file and insert the data.
Publish the google to the web as shown in the video.
Once it is published, click “Extension” and select “Apps Script”
Step 3: Connect to firebase
Replace the code.gs file with : github.com/ani...
Click on project setting and select “Show "appsscript.json" manifest file in editor” as shown in the video and replace appsscript.json with : github.com/ani...
Select “Initialize” near to Debug as shown in the video and follow the instruction to grant permission.
Check the firebase “Realtime Database” you can see the excel data imported to firebase.

[@rose-7263]

Thank you! Solved my issue like a charm, thanks!! Learning lots more from you!

[@alissonalbuquerque5716]

This video is wonderful! You are to be congratulated, however I made a small adjustment to the code so that it goes through all the tabs on import.
function getEnvironment() {
var environment = {
spreadsheetID: "replace with your keys",
firebaseUrl: "replace with your keys/",
};
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]);
}

function importSheet(sheet) {
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);
}
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;
}
}

[@krizz5825]

Can use yhis method to send data from google sheet whose value depend on the input from an app which is also enabled using another app script

[@Human-wd2zj]

Good job, keep helping others

[@FutureV1bot]

Can we connect flutter app text fields to excel and then to firebase?

[@ErcanÇÖMEN-j9d]

merhaba . Sayfa1 ve spreadsheetID adında bir klasör oluşturmasını istemiyorum. Benim id excell tablosundaki ilk sütun olmasını istiyorum nasıl yapabilirim. Teşekkürler

[@anishintech]

You can simply rename the “sheet1”
1. Navigate to the Sheet Tab:
• Open your Google Sheets document.
• Locate the sheet tab at the bottom of the screen corresponding to the sheet you want to rename (e.g., Sheet1).
2. Double-Click on the Sheet Name:
• Double-click directly on the existing name of the sheet (Sheet1). This action will make the sheet name editable.
3. Enter the New Name:
• Once the sheet name is editable, type in the new name you want to assign to the sheet.
4. Press Enter:
• After entering the new name, press the “Enter” key on your keyboard. This action will confirm and save the new name.
After doing rename it will create the folder with the same name that you renamed for “sheet1”

[@NemesisLex]

Thanks!
But:
1) Do you know how to import boolean from Excel? Now it makes a string out of "true" or "false".
2) Is there any possibility to create an random ID for every item? I do not wand to type one in every excel row ... and empty fields are making troubles.

[@Belegendaryy]

sorry, i have a question. How do you add the library of FirebaseApp when 5:02? I have a problem that i can use id to add FirebaseApp libraries.

[@anishintech]

Here is the file you can copy from the file:
github.com/anish-gyawali/Ionic-firebase-excel/blob/1645e424909122dd8a7e75b69697ca81e5e0b1a3/appscript.json

[@rihem2502]

hello thank u for this video i have a question i want to do import and export at the same time but it did not work when i change somthing in firebase it does not change in the excel can u help me with that

[@flashnewspoint]

Nice one, worked like a magic.

[@carbajal4508]

excellent synchronization of sheet to firebase
but from firebase to sheet it doesn't sync
¿some example?

[@OscarGonzález-t1t]

same question

[@Azmiriyadin]

Thanks for tutorial bro.. Good tutorial... ❤️❤️

[@Gabriel-oo6bo]

Thank you bro, much love! It works. August 2023

[@cristianjimenez4026]

10x bro, worked like a charm!

[@jezzahokuzai1113]

why is the appscript not working for me? is it because my database url is .app?

[@berkkemal5363]

Hi, I have a sheet presenting cities and universities and so on. But when I add a new university with same city name it only shows the last university row in same city. Do you know how to fix that?

[@berkkemal5363]

I added list number on the left and it solved my problem but if you have a better option I would appreciate to know. Thanks for the awesome video.

[@khalilahmed6788]

Sir if there are multiple excel tables the how we define url of deffrent tables

[@anishintech]

Hi
To execute this script with multiple spreadsheet IDs, you can modify the getEnvironment function to return an array of objects, each with a spreadsheetID and a firebaseUrl property.
`function getEnvironment() {
var environments = [
{
spreadsheetID: "SPREADSHEET_ID_1",
firebaseUrl: "FIREBASE_URL_1",
},
{
spreadsheetID: "SPREADSHEET_ID_2",
firebaseUrl: "FIREBASE_URL_2",
},
// add more objects here for additional spreadsheet IDs
];
return environments;
}`
You can then loop over the array of environments and execute the writeDataToFirebase function for each environment:
`// Initialize
function initialize(e) {
var environments = getEnvironment();
environments.forEach((environment) => {
writeDataToFirebase(environment.spreadsheetID);
});
}`
This will execute the script for each spreadsheet ID in the array, using the corresponding firebaseUrl for each one.
Alternatively, you can modify the writeDataToFirebase function to accept both the spreadsheetID and the firebaseUrl as arguments and pass them both to the importSheet function
`function writeDataToFirebase(sheetID, firebaseUrl) {
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], firebaseUrl);
SpreadsheetApp.setActiveSheet(sheets[i]);
}
}
// Import each sheet when there is a change
function importSheet(sheet, firebaseUrl) {
// rest of the function remains the same
var firebaseUrl = firebaseUrl + sheet.getParent().getId() + "/" + name;
// rest of the function remains the same
}`

[@khalilahmed6788]

6:17:08 AM Error Attempted to execute initialize, but could not save.

[@khalilahmed6788]

Sir it gives me error when i initialize the function

[@khalilahmed6788]

please help

[@mohamedalighediri5224]

Man thank you so much

[@malhaarshikhare9607]

I added an excel file to the previously created project but it is not showing even after i followed your steps sincerely.Please please help and respond asappp

[@abuadzkauwais8154]

Thanks so much bro, but i want to make a sheet1 as a bucket of rtdb firebase, can you help me?

[@vinodhans498]

how to add multiple table with data using google sheet to firebase

[@mohamedayad70]

how to connect microsoft excel with firebase ? also what if we want to add multiple files ?

[@sabatstudio4550]

do you know how to export text.exe file to firebase by automatically?

[@OscarGonzález-t1t]

good day sir, how to send the data to excel viceversa?

[@kuprxcreation1165]

Can it, combined with koldular to load db

[@anishintech]

Hi, If you want to use Koldulan instead, you would need to modify the code to use Koldulan's API for database operations instead of Firebase's API.
I have not done personally but i believe you need to modify the code to use Koldulan's API like :
-Replace FirebaseApp with Koldulan API functions for database operations such as setting and retrieving data.
-Replace the Firebase URL with the Koldulan database URL.
-Use Koldulan's API key for authentication.
-Replace the data structure used by Firebase with the one used by Koldulan.
-Test and verify the modified code works as expected.

[@MOHAMADARDI]

Thanks for your tutorial 👍

[@MaryJoyAntong]

Thank you so much! God Bless!

[@bonfaceosuka]

Wow , amazing tutorial.
How can we convert the data in the firebase to visual analytical data? Like in terms of graphs and charts .

[@anishintech]

I can make tutorials on how can we visualize firebase data but there are documentation one of them is
chartbrew.com/blog/visualize-your-firebase-realtime-database-with-chartbrew/amp/
and you can try exploring chart Js.

[@Channel_Pendidikan_Real]

It cannot save appsscript.json

[@DomOikos]

Google sheet is not Excel ...

[@dulanjadilshan5529]

I can't choose initialize function. It always shows myFunction. How I solve that?

[@christianperalta2917]

How I fixed it was by hitting Run, which should give you an error. Then go back and Initialize should appear.

[@Knock_Out_25]

I want your help sir...after signup the data is shown in firebase database but after refreshing it the data get disapper ....plz give the solution for it what to do ani how to do....plz

[@anishintech]

May I know what you are refreshing? also can check every step that you followed and you are not missing any steps. Also, I recommend you to check privacy if user has to access the data or not. Also it might be overwritten.

[@Knock_Out_25]

Tell him after following your steps I connected firebase to Android studio
and when I take data from Android studio to firebase that data taken from Excel vanishes from the firebase after the refresh , how to solve this? What steps to take to keep data coming from both Excel and Android studio into the firebase (login page)

[@khalilahmed6788]

i want to import multiple tables

[@anishintech]

Hi you can modify writeDataToFirebase function to loop through all the sheets in the spreadsheet and call the importSheet function for each sheet as follows:
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]);
}
}

[@alissonalbuquerque5716]

Hey, I made some modifications to the code and maybe this can help you
function getEnvironment() {
var environment = {
spreadsheetID: "replace with your keys",
firebaseUrl: "replace with your keys/",
};
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]);
}

function importSheet(sheet) {
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);
}
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;
}
}

[@rizckir.m3524]

​@@alissonalbuquerque5716 r u there? i need helps too @anishintech
how to remove child value of name sheet "Sheet1" in firebase from spreadshet
so, after value "SheetID", the next is "Anish" (name, age, country), next Anish1 (name, age, country)?

[@fenbilim_net]

so good

[@SharmaJiDe]

can i change the name of sheet1

[@anishintech]

Yes

[@rizckir.m3524]

r u there? i need helps too @anishintech
how to remove child value of name sheet "Sheet1" in firebase from spreadshet
so, after value "SheetID", the next is "Anish" (name, age, country), next Anish1 (name, age, country)?@@anishintech

[@ismaildjennane8013]

many thanks

[@ismaildjennane8013]

please , Can you show us how to retrieve database from Firebase to Google Sheets?

[@anishintech]

Check th-cam.com/video/dOwZ5gzl9wI/w-d-xo.html

[@areebasaleem7216]

Thanks

[@Madii-yo5st]

do not work

[@Peace73107]

hey can u please help me after some time when i try to change the data it doesnot change the data and when i rerun the initalize function it shows an error TypeError: data[0][j].split is not a function
importSheet @ Code.gs:77
writeDataToFirebase @ Code.gs:49
initialize @ Code.gs:39

[@anishintech]

Hi
Such error likely caused by a cell in the first row (data[0]) of the sheet not containing a string value. The split method can only be called on strings, so if the value in the cell is not a string, the error will be thrown.
Try to check your assign function:
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];
}
//Here I am making sure that the value in the first row is a string before calling the split method on it
if (typeof keyPath[lastKeyIndex] === "string") {
obj[keyPath[lastKeyIndex]] = value;
} else {
obj[keyPath[lastKeyIndex].toString()] = value;
}
}
I hope this helps!

[@Peace73107]

@@anishintech thanks bro it works

[@Ops_pops]

Error: Unauthorized request.
FirebaseApp_._sendAllRequests @ Code.gs:612
FirebaseApp_._buildAllRequests @ Code.gs:469
baseClass_.setData @ Code.gs:289
Review potential runtime incompatibilies @ Migrating to the V8 runtime
*In AppScript after initialising* can anyone help thanks in advance.

[@anishintech]

"Error: Unauthorized request" means the script is not authorized to access the Google Sheets and Firebase. You need to add the required authorization to the script.
You can do this by following these steps:
-Go to the Google Script project editor
-Click on the "File" menu and select "Project properties"
-Select "Script properties"
-Add a new property with the key "google_sheets_id" and value as the ID of the Google Sheets
-Add another property with the key "firebase_url" and value as the Firebase URL
After this, try running the script again.
If it still does not work, you can check the authorization scopes required by the script in the "File" menu, under "Project properties", "Scopes". Make sure that the required scopes are authorized in the Google Developer Console.

[@atharvaangre8783]

Hi there I am getting an error can you please help me solving it...
Error: Invalid data; couldn't parse key beginning at 1:170. Key value can't be empty or contain $ # [ ] / or .
importSheet @ Attendace.gs:86
writeDataToFirebase @ Attendace.gs:49
initialize @ Attendace.gs:39
I am having multiple field in my Spreedsheet i.e.
StudentID | Data | Time | First Name | Last Name | Phone Number | Address

[@RSHCM]

me too

[@King-ul4nb]

Were you able to resolve the issue??

[@alaukipatel3088]

me too

[@suyashtripathi3104]

​@@King-ul4nb have u got the solution ?

[@suyashtripathi3104]

​ @King-ul4nb have u got the solution ?

[@Madii-yo5st]

do not work