Google Apps脚本运行速度很慢
问题描述:
我有以下代码,它从Firebase数据库中提取数据并填充Google表单文档中的数据。 该过程每行需要大约1秒,并且有1000行。Google Apps脚本运行速度很慢
我的代码效率不是很高,还是有更好的方法在谷歌表格上逐行填写信息?
function getAllData() {
var firebaseUrl = "url-here";
var base = FirebaseApp.getDatabaseByUrl(firebaseUrl);
var data = base.getData();
var iterator = 0
var obje = []
//Logger.log(data)
for(var i in data) {
iterator++;
if (iterator > 5) {
break
}
if (data[i] !== null) {
Logger.log(data[i]);
obje.push(data[i])
}
}
var range = SpreadsheetApp.getActiveSheet().getRange("A5:K5");
range.setBackground("red");
//Set Column Names
SpreadsheetApp.getActiveSheet().getRange('A5').setValue('Key');
SpreadsheetApp.getActiveSheet().getRange('B5').setValue('Time');
SpreadsheetApp.getActiveSheet().getRange('C5').setValue('Location');
SpreadsheetApp.getActiveSheet().getRange('D5').setValue('Participant_1');
SpreadsheetApp.getActiveSheet().getRange('E5').setValue('Participant_2');
SpreadsheetApp.getActiveSheet().getRange('F5').setValue('Category');
SpreadsheetApp.getActiveSheet().getRange('G5').setValue('team1Score');
SpreadsheetApp.getActiveSheet().getRange('H5').setValue('team2Score');
SpreadsheetApp.getActiveSheet().getRange('I5').setValue('gameDetails');
var number = 5
Logger.log(obje.length);
for (var i in obje) {
Logger.log('Hello' + i)
number++
if (i % 2 === 0) {
var range = SpreadsheetApp.getActiveSheet().getRange("A" + number + ":K" + number);
range.setBackground("lightblue");
}
else {
var range = SpreadsheetApp.getActiveSheet().getRange("A" + number + ":K" + number);
range.setBackground("lightgreen");
}
//Populate Row Data
SpreadsheetApp.getActiveSheet().getRange('A' + number).setValue(obje[i].Key);
SpreadsheetApp.getActiveSheet().getRange('B' + number).setValue(obje[i].Time);
SpreadsheetApp.getActiveSheet().getRange('C' + number).setValue(obje[i].Location);
SpreadsheetApp.getActiveSheet().getRange('D'+ number).setValue(obje[i].Participant_1);
SpreadsheetApp.getActiveSheet().getRange('E'+ number).setValue(obje[i].Participant_2);
SpreadsheetApp.getActiveSheet().getRange('F'+ number).setValue(obje[i].Category);
SpreadsheetApp.getActiveSheet().getRange('G'+ number).setValue(obje[i].team1Score);
SpreadsheetApp.getActiveSheet().getRange('H'+ number).setValue(obje[i].team2Score);
SpreadsheetApp.getActiveSheet().getRange('I'+ number).setValue(obje[i].gameDetails);
}
}
答
如果您的数据是在2d数组中,那么这应该工作。 不,这不会工作。我去了解了Firebase Today的一些情况,现在我看到它是一个对象数组,所以我会弄清楚如何完成工作并回来修正它。我猜我必须在时间和地点做一条线。
function getAllData()
{
var firebaseUrl = "url-here";
var base = FirebaseApp.getDatabaseByUrl(firebaseUrl);
var data = base.getData();//I assume this is correct I have not used firebase
var ss=SpreadsheetApp.getActive();
var sh=ss.getActiveSheet();
var headers=['Key','Time','Locaton','Participant_1','Participant_2','Category','teams1Score','team2Score','gameDetails'];
sh.appendRow(headers);
sh.getRange(2,1,data.length,headers.length).setValues(data);
}
这是工作
下面是一个example from another contributor一个链接,但我不能得到那个工作。所以,我修改了它下面一点:
function writeMySheets() {
var ss = SpreadsheetApp.getActive();
var sheet = ss.getActiveSheet();
var firebaseUrl = "https://PROJECTID.firebaseio.com/";
var secret = "SECRET";
var base = FirebaseApp.getDatabaseByUrl(firebaseUrl,secret);
var data = base.getData();
var keys = Object.keys(data);
var sheetRow = [];
var entryKeys;
var firstTime=true;
for (index in keys) {
sheetRow = [];
entryKeys = Object.keys(data[keys[index]])
for (i in entryKeys) {
sheetRow.push(data[keys[index]][entryKeys[i]]);
}
//Logger.log(sheetRow);
if(firstTime)
{
sheet.appendRow(entryKeys);//Prints column headers
firstTime=false;
}
sheet.appendRow(sheetRow);
}
}
这似乎从,我认为我从来没有得到工作的QuickStart tutorial教程数据的作品。
+0
谢谢!我想出了另一种方式! –
getRange的每个调用都会读取并在您的情况下写回文件。你需要尽量减少这些电话。请参阅[优化电子表格操作](http://googleappsscript.blogspot.com/2010/06/optimizing-spreadsheet-operations.html)上的Googls Apps Script博客文章 –