Exceeded maximum execution time when trying to create a matrix

Question

I try to create a matrix , if the array is a small database everything works fine, of course if it exceeds 800 lines and more rests on the error "You have exceeded the maximum allowed run time ." Not effectively create a matrix :

var s = SpreadsheetApp.getActiveSheet(); //List
var toAddArray = []; //Greate Arr
  for (i = 1; i <= s.getLastRow()+1; ++i){ //Start getting Value
  var numbr = s.getRange(i,4); //detect range
   var Valus = numbr.getValues().toString(); //get value
    
    //filter value
  var newznach = Valus.replace(/\-/g, "").replace(/[0-9][0-9][0-9][0-9][0-9][a-zA-Zа-яА-Я][a-zA-Zа-яА-Я]/g, "").replace(/[a-zA-Zа-яА-Я][a-zA-Zа-яА-Я]/g, "");
  toAddArray.push([i.toFixed(0),Valus,newznach]); //add to array 0- Row numb, 1- Value, 2- "filtered" value
  }

toAddArray =

{
Row, Value, NewValue - filtered
Row, Value, NewValue - filtered
Row, Value, NewValue - filtered
...
}

Can I somehow get an array of the same the other way ( faster, easier ) ?

answers already covered here: developers.google.com/apps-script/best_practices — Zig Mandel
– Zig Mandel, Commented Sep 23, 2016 at 0:06

Robin Gertenbach · Accepted Answer · 2016-09-20 07:25:03Z

2

You're doing a call to getValues every row, that eats a lot of performance.
It is better to do one big call to have all the data and then go through it sequentially.

var s = SpreadsheetApp.getActiveSheet();
var data = s.getRange(1,4, s.getLastRow()).getValues();

var toAddArray = data.map(function(row, i) {
  var Valus = row[0].toString();
  var newznach = Valus.
    replace(/\-/g, "").
    replace(/[0-9][0-9][0-9][0-9][0-9][a-zA-Zа-яА-Я][a-zA-Zа-яА-Я]/g, "").
    replace(/[a-zA-Zа-яА-Я][a-zA-Zа-яА-Я]/g, "");

  return [i.toFixed(0), Valus, newznach];
});

answered Sep 20, 2016 at 7:25

Robin Gertenbach

10.9k3 gold badges30 silver badges38 bronze badges

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

2 Comments

Антон Смольный Over a year ago

Thank you, too, realized that the trouble is that the GetValue performed in each cycle . My crutch was in order to make it over the cycle, like: var Valus = numbr.getValues().toString().split(","); toAddArray.push([i.toFixed(0),Valus[i-1],newznach]); But you have no doubt, code more correct and beautiful

Антон Смольный Over a year ago

With your code , run-time of 0.254 seconds for 1,500 lines of the table

Max Makhrov · Accepted Answer · 2016-09-20 07:29:07Z

1

this code:

var Valus = numbr.getValues().toString();

slows you down because you read data from the sheet in a loop.

Try reading data once into array and then work with it:

var data = s.getDataRange().getValues();

And then work with data, in a loop. This sample code log each cell in active sheet:

function logEachCell() {
  var s = SpreadsheetApp.getActiveSheet();
  var data = s.getDataRange().getValues();

  // loop each cell
  var row = [];
  for (var i = 0; i < data.length; i++) {
    row = data[i];
    for (var j = 0; j < row.length; j++) {
      Logger.log(row[j])      
    } 
  }
}

answered Sep 20, 2016 at 7:29

Max Makhrov

18.9k6 gold badges65 silver badges88 bronze badges

Collectives™ on Stack Overflow

Exceeded maximum execution time when trying to create a matrix

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related