CODEWITHSUNDEEP
javascriptgoogle-apps-script
Rob Campbell
Rob Campbell

Reputation: 63

speeding up processing on sheets add-on

I am making an add on that processes content from a Google Sheets doc and returns it (for now) to new sheet in the spreadsheet. The first iteration works but is deadly slow. I think it's because it processes the data sheet one line at a time. So I'm rewriting it to ingest all the data and then process the array it lives in.

Problem is the amount of data appears to be prohibitive. I can see this in Logger output, in execution transcript, and in stackdriver logging; it gets to about line 35 (11 columns) before it runs out of room.

here is the original version (takes an insane amount of time): ''' var compareDate = selDate;

var bulletin = ss.getSheetByName(reportSheetName);
var responses = ss.getSheetByName("Calendar");

var lastRow = responses.getLastRow(); //get last row of data sheet
var lastBull = bulletin.getLastRow();  //get last row of bulletin sheet
var nextBullRow = lastBull+1;
var nextBullItem = bulletin.getRange(nextBullRow,1);

for(var i = 2; i <= lastRow; i++) {
  var row = responses.getRange(i, 1, 1, 11).getValues();

  var dateA = new Date((row[0][4]).valueOf());
  var eventType = row[0][0].valueOf();
  var eventCalDate = new Date(row[0][4].valueOf());
  var eventEvent = row[0][3].valueOf();
  var eventOpp = row[0][7].valueOf();
  var eventLocation = row[0][8].valueOf();

  var eventMonth = eventCalDate.getMonth();
  var eventDate = eventCalDate.getDate();
  var eventYear = eventCalDate.getYear();
  var eventShortDate = eventMonth+"/"+eventDate+"/"+eventYear;

  var start = new Date(thisWeekSt);
  var end = new Date(thisWeekEnd);


  /*var masterDate = new Date(compareDate).valueOf();
  var thisCat = row[0][3];
  var bullItem = row[0][4]; */
  var dateMatch = false;
  if(dateA > start && dateA < end) {
  dateMatch=true;  

    bulletin.getRange(nextBullRow,1,nextBullRow,6).setFontWeight("normal");

  bulletin.getRange(nextBullRow,typeCol).setValue(eventType);
  bulletin.getRange(nextBullRow,dateCol).setValue(eventShortDate);
  bulletin.getRange(nextBullRow,timeCol).setValue(eventType);
  bulletin.getRange(nextBullRow,eventCol).setValue(eventEvent);
  bulletin.getRange(nextBullRow,oppCol).setValue(eventOpp);
  bulletin.getRange(nextBullRow,locCol).setValue(eventLocation);
  nextBullRow++;

  Logger.log(bulletin.getRange(nextBullRow-1,6).getValue());

  if(bulletin.getRange(nextBullRow-1,6).getValue()=="Hollister") {
    bulletin.getRange(nextBullRow-1,1,nextBullRow-1,6).setFontWeight("bold");
    Logger.log("boop!");
  }

'''

And here's the new version, which doesn't have the digital appetite for all the data; ''' var compareDate = selDate;

var bulletin = ss.getSheetByName(reportSheetName);
var responses = ss.getSheetByName("Calendar");

var lastRow = responses.getLastRow(); //get last row of data sheet
var lastBull = bulletin.getLastRow();  //get last row of bulletin sheet
var nextBullRow = lastBull+1;
var nextBullItem = bulletin.getRange(nextBullRow,1);

var allData = responses.getRange(2, 1, responses.getLastRow(), 11).getValues();  


for(var i = 2; i <= allData.getLastRow(); i++) {
  var row = allData.getRange(i, 1, 1, 11).getValues();
  Logger.log(row);

'''

Let me know if you want the whole function, I'll edit.

The question is: how can I speed data processing from the sluggish pace it is at currently. (Thanks Cooper!)

Upvotes: 0

Views: 37

Answers (2)

Thorsten Staerk
Thorsten Staerk

Reputation: 1156

Had the same issue today. My solution was to duplicate the sheet and then work on the copy:

sheet=SpreadsheetApp.getActiveSpreadsheet()
copy=sheet.duplicateActiveSheet()

after duplicating, the copy is the active sheet, so you can directly work on it, e.g. delete rows like that:

sheet.deleteRows(1,2)

Upvotes: 0

Boomer
Boomer

Reputation: 61

In your for loop you are calling getValues each iteration of your loop which looks like the issue. You've already got the data in your call above.

Try: 

for(var i = 0; i <= allData.getLastRow(); i++) {
  var row = allData[i];
  Logger.log(row);

Upvotes: 2

Related Questions