Google Apps Script runs very slow

Question

I have the following code that pulls data from firebase database and fills out the data in a google sheets document. The process takes around 1 second per row, and there are 1000 rows.

Is my code not very efficient, or is there a better way to fill out information row by row on a google sheet?

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

Each call of getRange reads and in your case writes back to the file. You need to minimize those calls. See this Googls Apps Script blog post on Optimizing Spreadsheet Operations — Karl_S

Cooper Cooper · Accepted Answer · 2017-08-14T20:12:40

If your data is in a 2d array then this should work. Nope this won't work. I went and learned something about Firebase Today and I see now that it's an array of objects so I'll figure out how to make the work and come back and correct it. I'm guessing I'll have to do it a line at and time.

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

This is working

Here's a link to an example from another contributor but I couldn't get that to work. So I modified it a little below:

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

And this seems to works on the tutorial data from the QuickStart tutorial that I thought I'd never get to work.

Google Apps Script runs very slow

1 Answers

This is working