Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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

share|improve this question
    
Can you add this pieces of script that perform those actions? –  Jacob Jan Tuinstra Aug 6 '13 at 11:19
    
Hi, @JacobJanTuinstra, I added one of the scripts. Thanks for any help! –  Pythonista's Apprentice Aug 6 '13 at 14:11
    
Can you share the file with us? –  Jacob Jan Tuinstra Aug 9 '13 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. –  Glen Little Aug 11 '13 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. –  Jacob Jan Tuinstra Aug 13 '13 at 18:35
add comment

2 Answers

up vote 3 down vote accepted

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.

share|improve this answer
add comment

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.
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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