Google script - Exceeded maximum execution time ,

问题:

google script spreadsheet Novice

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 ) ?

回答1:

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

回答2:

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