CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
jsaff1902
jsaff1902

Reputation: 19

How to I slowdown Google script

My Google Script produces data which I put into my "sheet6". Sheets then processes this data, with a result I want to keep track of for each iteration (k). I get my resulting ("passing") data back to my script, but this is taking a lot of time each iteration. I know that using getValue like this is bad practice. I think a better way would be to keep track of any "data changes" in sheets first, before passing anything back to my script. The problem is the script executes quickly, but Google sheets calculation process is pretty slow. Is there some way I can pass data from my script to sheets, wait, allowing sheets to do it's calculations before scripts moves on to the next scenario?

function RunScenarios() {
  function shuffleArray(array) {
    for (var i = array.length - 1; i > 0; i--) {
        var j = Math.floor(Math.random() * (i + 1));
        var temp = array[i];
        array[i] = array[j];
        array[j] = temp;
    }
}
  var aa = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Run Program");
  var scenarios = aa.getRange('H19').getValue();
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet6");
  var range = ss.getRange("b14:av14");
  var m = 0
  var picks = [];
  for(var k = 1; k <= scenarios; k++) {

    if (m = 47) {
      m = 0
    }
  for(var j = 1; j < 48; j++) {
      var array1 = [1+m, 2+m, 3+m, 4+m, 5+m, 6+m, 7+m, 8+m, 9+m, 10+m, 11+m, 12+m, 13+m, 14+m, 15+m, 16+m, 17+m, 18+m, 19+m, 20+m];
  shuffleArray(array1);
  picks = addToPicks(array1,picks);
   m = m + 1
  }
      ss.getRange(6, 2, 1, 47).setValues([picks]);  // passing all the picks to the sheet
   picks = [];
    var passingscenarios = aa.getRange('passing').getValue();  // getting the result from the sheet takes a lot of time
//    Eventually, I want to keep track of how many passingscenarios I've got here before exiting

}
    var spreadsheet = SpreadsheetApp.getActive();
      spreadsheet.getRange('A1').activate();
      spreadsheet.getSheetByName('Chart4').showSheet()
     .activate();
      spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Chart4'), true);

}

function addToPicks(arr,picks) {
    if (picks.indexOf(arr[0]) === -1) {
        picks.push(arr[0])
        return picks
    }
    arr.shift();
    return addToPicks(arr,picks);
}

Upvotes: 0

Views: 329

Answers (1)

Wicket
Wicket

Reputation: 38356

To insert a pause in Apps Script use Utilities.sleep(milliseconds) but first you may also add SpreadsheetApp.flush() in order to be certain that the changes made by the script have been passed to the spreadsheet.

Upvotes: 2

Related Questions