How to I slowdown Google script

Question

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

You can force a buffer flush of the spreadsheet service. That has already been asked many times here on Stack Overflow. — tehhowch, Jul 01 '18 at 15:36

Rubén · Answer 1 · 2018-07-01T17:03:18.537

2

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.

edited Jul 01 '18 at 17:03

answered Jul 01 '18 at 16:51

Rubén

34,714
9
70
166

OK, so I tried the flush(). It now allows sheets to perform the calculations before proceeding, which is great. Now I realize I have a new problem, maximum time exceeded. How to I trigger the script to restart and run until the final iteration, which might require 30 to 60 minutes? – jsaff1902 Jul 01 '18 at 18:31
1

@user184662 See [Exceeded maximum execution time in Google Apps Script](https://stackoverflow.com/q/7854573/1595451) and other related questions. – Rubén Jul 01 '18 at 18:47

How to I slowdown Google script

1 Answers1