Speed up execution of array manipulating Google Apps Script

Question

I need this script that process a big big table of arround 20k rows, 50 columns.

As of now, the script only manages to process 10k rows before running out of time.

I've tried to optimize as much as possible but I'm afraid i've run into the limit of my habilities:

This is my code nowadays:

     function updateValues() {
      var ws1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("FINAL").getDataRange();
      var ws3List = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("WS2").getDataRange().getValues();
      var ws2List = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("WS3").getDataRange().getValues();
      var results = new Array;
      var data1 = ws1.getValues();
      var data1Length = data1.length;
      var ws2Length = ws2List.length;
      var ws3Length = ws3List.length;
      var data1LengthHorizontal = data1[1].length;
      for ( var i = 2; i < data1Length; i++ ) {
        var iterHotel = data1[i][0];
        results[i-2] = [];
        for ( var nn=0; nn < ws2Length; ++nn ){
          if (iterHotel == ws2List[nn][2]){
            var ws2Code = ws2List[nn][4];
            break;
          }
        }
// First Vlookup
        var ws3Check;
        for ( var nn=0; nn < ws3Length; ++nn ){
          if (iterHotel == ws3List[nn][0]){
            ws3Check = true;
            break;
          }
          ws3Check = false;
        }
// Second Vlookup
        var score;
        var countries = data1[0];
        if (ws3Check) {score = aCalculator1(ws2Code);} else {score = aCalculator2(ws2Code);}
        for ( var i1 = 2; i1 < data1LengthHorizontal; i1++){
          if (data1[0][i1] == "ALL" || countries[i1].indexOf(data1[i][1]) > -1) {results[i-2][i1-2] = score;} 
          else {results[i-2][i1-2] = 1;}      
        }
      }
      var toCopy = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("FINAL").getRange(3, 3, data1Length - 2, data1LengthHorizontal - 2); //HardCoded Starting Point
      toCopy.setValues(results);
    }

    function aCalculator1(ws2Code) {
        switch (ws2Code) {
          case 3:
            return 75;
          case 2:
            return 75;
          case 1:
            return 95;
          case 0:
            return 1;
          default:
            return 1;  
        }
    }

    function aCalculator2(ws2Code) {
        switch (ws2Code) {
          case 3:
            return 10;
          case 2:
            return 10;
          case 1:
            return 10;
          case 0:
            return 1;
          default:
            return 1;  
        }
    }

What do you think? Can you see stuff to be tweaked? How should I approach this problem? Maybe splitting chunks?

PS: Sorry If I messed up some variables while changing their names to post em.

Cheers,

Adelin · Accepted Answer · 2016-09-02 15:30:48Z

1

Well, whenever you make calls towards google libraries, costs you time.

Which means that if you manage to somehow get these 2 lines out of the loop, you will most likely succeed.

var toCopy = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("FINAL").getRange(3, 3, data1Length - 2, data1LengthHorizontal - 2); //HardCoded Starting Point
      toCopy.setValues(results);

Inside the loop try to work only with arrays, remember indexes, line rows, etc. and then call getRange and setValues outside the loop, according to the data you saved inside the loop.

answered Sep 2, 2016 at 15:30

Adelin

8,2706 gold badges40 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Paul T. Over a year ago

I'm calling that at the end, outside the loop. The problem is in the loops themselves, I reach only 10k / 19k (I tested logging the variable i of my main loop)

Adelin Over a year ago

Ah, I see. @PaulT. Can you please make a copy of the spreadsheet and share it to us? I'd like to reproduce the problem on my side and then try to fix it. If it contains sensitive data, just replace it with random text. The problem is not obvious (to me)

Sam Scholefield Over a year ago

Large operations that exceed execution timeout can be broken into sections and worked through using time based triggers. As you are working with an array you can use script properties to store an array position so that when the script fires again, using the time based trigger, it begins processing the array where it left off previously.

Collectives™ on Stack Overflow

Speed up execution of array manipulating Google Apps Script

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related