I’m trying to connect my Google Sheet to Firebase-RTDB as i want to transfer my data to proper database (due to gigantic amouth of data) and fetch them through google sheet based on first column value.
The code works, but the performance is not very satisfying at all. It takes about 20 seconds, even with the If statement catching whether the cell is empty before doing a loop. If i don’t do that it will take a whooping 60 seconds just for 80 rows. Which is not good at all.
function getAllData_ID33() {
//Read sheet
var ss = SpreadsheetApp.openById('**MY_GOOGLESHEET_ID**');
let sheet = ss.getSheetByName('Test')
let startRow = 2
//Get Range
let numRow = sheet.getDataRange().getLastRow();
let lastCols = sheet.getDataRange().getLastColumn();
let dataRange = sheet.getRange(startRow, 1, numRow, lastCols);
let celldata = dataRange.getValues();
//connect_link
//ID33_Headoffice
var ID33_HEADOFFICE_DATABASE = "**MY_FIREBASE_LINK**";
//if first and fourth column is not empty, pass.
//Otherwise, start the fetching loop.
for (j = 0; j < celldata.length-1; j++) {
//row = row[column]
row = celldata[j];
let phyid = ID33_HEADOFFICE_DATABASE + "//" + row[0]
let base = FirebaseApp.getDatabaseByUrl(phyid);
let dataSet_33headoffice = [base.getData()];
let id33_rows = [], data_33_headofflice;
if (row[0] != "") {
if (row[4] == "") {
for (i = 0; i < dataSet_33headoffice.length; i++) {
//need Booking ID, Stockout-ID, Branch ID, Branch To Name, Comment
data_33_headofflice = dataSet_33headoffice[i];
//Logger.log([
//data_33_headofflice['Booking ID'],
//data_33_headofflice['Stock Out (ID)'],
//data_33_headofflice['Branch (ID)'],
//data_33_headofflice['Branch To (Name)'],
//data_33_headofflice['Comment']
//]);
id33_rows.push([
data_33_headofflice['Booking ID'],
data_33_headofflice['Stock Out (ID)'],
data_33_headofflice['Branch (ID)'],
data_33_headofflice['Branch To (Name)'],
data_33_headofflice['Comment']
]);
let id33dataRange = sheet.getRange(j + 2,2,id33_rows.length,5);
id33dataRange.setValues(id33_rows);
}
}
}
}
}
Should be noted that i’m not very familliar with Javascript/Google App Script at all. I don’t even know if Firebase is even fit for a task like this. So if you do know what’s causing it to be slow and how to improve, please explain. Thanks for all the support!
2
Answers
You need to move these two lines out of the loop:
Otherwise, you’re updating the spreadsheet after every loop iteration.
I believe your goal is as follows.
From
The code works, but the performance is not very satisfying at all.
, I understood that your script worked. From this situation, how about the following modification?Modification points:
setValues
is used in a loop, the process cost becomes high. RefdataSet_33headoffice.length
might be always 1. Because whendataSet_33headoffice.length
is 2, I thought that the row is deviated. But, in your question, you sayThe code works
. So, I guessed that the value might be 1.When these points are reflected in your script, how about the following modification?
Modified script:
Before you use this script, please enable Sheets API at Advanced Google services.
Note:
Reference: