How to avoid "invoked too many times per second" error

Question

I made a Google Apps Script, but often I get this error: Script invoked too many times per second in this Google user account.

This is the script:

function papersControl(tipo, col, prev_cell, quant) {

    var cell = 0;
    var gender = tipo.split(" ");

    if (tipo == col) {
        cell = parseInt(prev_cell,10) + parseInt(quant,10);
        return cell;
    }

    if ( (col == "Special Paper") && (gender[0] == "Certificate") && (gender[2] != "Voucher") ) {
        cell = parseInt(prev_cell,10) + parseInt(quant,10);
        return cell;
    }

    if ( (col == "Special Paper 2") && (gender[0] == "Certificate") && (gender[2] == "Vouncher") ) {
        cell = parseInt(prev_cell,10) + parseInt(quant,10);
        return cell;    
    }

    else
        return prev_cell;
}

I'd like to freeze the past recorded values in a cell in order to reduce the number of "times per second" that the script invokes Google servers, since I don't need to compute anymore. How can I do this​​?

Answer 1

Don't use a custom formula--instead process all the values into an array then have the array written to the sheet. This will not adjust as your spreadsheet changes, but you should be able to modify this to look for the last value using a combination of the match and len functions in the sheet to id the starting and ending rows.

function straightToText() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var sheet = ss[0].getRange(1, 1, 9999, 1).getValues(); // get first sheet, a1:a9999
  var results = [];
  for (var i = 0; i < sheet.length; i++) {
    var hash = hash256(sheet[i][0]); // hash256 is the custom formula in this case
    results.push(hash);
  }
  for (var j = 0; j < results.length; j++) {
    var row = j+1;
    ss[0].getRange(row, 3).setValue(results[j]);  // write output to c1:c9999 as text
  }  
}

This works for a sheet processing 10k rows with no errors, where use of the custom formula in the sheet itself (i.e., =hash256(a1)) produces an error after ~500 rows.

Answer 2

In my case, I reduced the number of function calls using if statement. It is basically a conditional call to function. e.g.

if(Col6="","",myFunction()) //function is called only when the column6 is not empty.

Answer 3

GSG's answer is good, I have added something to that approach, that is createTextFinder , and can search over 249000 cell :

 var tosearch = request.parameter.uId; 
 var sheetSearchRange = sheet.getRange(2, 8, 249638, 1); //  h2:h249638   
 var tf = sheetSearchRange.createTextFinder(tosearch);
 var all = tf.findAll();
 for (var i = 0; i < all.length; i++) {
    //Logger.log('The sheet %s, cell %s, has the value %s.', all[i].getSheet().getName(), all[i].getA1Notation(), all[i].getValue());
        

            flag = 1;
  }