5

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

enter image description here

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

5
  • Can you add this pieces of script that perform those actions? Aug 6, 2013 at 11:19
  • Hi, @JacobJanTuinstra, I added one of the scripts. Thanks for any help! Aug 6, 2013 at 14:11
  • Can you share the file with us? Aug 9, 2013 at 7:16
  • 1
    Is "papersControl" a function call in each cell of those cells? Could you show the formula in a cell? You may be able to make a script that you manually run that replaces the formulas with their values. Aug 11, 2013 at 22:23
  • 1
    If you don't give extra information, we can't provide you a solution and that would be a waste of your reputation. Aug 13, 2013 at 18:35

3 Answers 3

5

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.

1
  • thanks I have used your approach and find about 249638 cell, with the combination of createTextFinder. Dec 5, 2020 at 13:48
1

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.
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.