How to step through google script

Question

I am trying to write a Google Sheets script macro. How do I step through it to understand why the time is being exceeded? My problem is the loop, when I set the iteration max(scenarios) to 46, the code seems to run fine, taking about 1-2 seconds. When the max is set to 47, it dies with max execution time exceeded (4-5 minutes). Whats going on?

function testing() {

  var aa = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Run 
  Program");

  var scenarios = aa.getRange("H19").getValue();



  for (i = 1; i <= scenarios; i++){

  var ss = 
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet6");

  var range = ss.getRange("b6:u6");
  var min = 1  ;
  var max = 20 ; 
  var numbers = []
    for (var i = min; i <= max; i++) {             
      numbers.push(i);
  }  


  shuffleArray(numbers)
  var counter = 0;
  for (var x = 1; x <= range.getWidth(); x++) {
    for (var y = 1; y <= range.getHeight(); y++) {
      range.getCell(y, x).setValue(numbers[counter]);
      counter++;
    }
  }
  var range = ss.getRange("v6:ao6");
  var min = 21  ;
  var max = 40  ;
  var numbers = []
    for (var i = min; i <= max; i++) {
     numbers.push(i);
  }
  shuffleArray(numbers)
  var counter = 0;
  for (var x = 1; x <= range.getWidth(); x++) {
    for (var y = 1; y <= range.getHeight(); y++) {
      range.getCell(y, x).setValue(numbers[counter]);
      counter++;
    }
  }
  var range = ss.getRange("ap6:at6");
  var min = 41  ;
  var max = 45  ;
  var numbers = []
    for (var i = min; i <= max; i++) {
     numbers.push(i);
  }
  shuffleArray(numbers)
  var counter = 0;
  for (var x = 1; x <= range.getWidth(); x++) {
    for (var y = 1; y <= range.getHeight(); y++) {
      range.getCell(y, x).setValue(numbers[counter]);
      counter++;
    }
  }
  }
//  function Chart4() {
  var spreadsheet = SpreadsheetApp.getActive();
      spreadsheet.getRange('A1').activate();
      spreadsheet.getSheetByName('Chart4').showSheet()
     .activate();
      spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Chart4'), 
true);
};

Answer 1

Range.getCell() call can be expensive sometimes and should be avoided. Use Range.getValues() and iterate over the two dimensional array. Also, instead of using active spreadsheet open spreadsheet by Id for unit testing. This way you will be able to debug/run from GAS editor directly. GAS editor usually catches such performance issues in the code and displays warnings pointing to the line number in the code.

Answer 2

Your code needs a lot of help. Here's a tip to get you started.

As others have mentioned, you should call .getValues() on the whole range, which gives you a 2 dimensional array of cell values [[A1value,B1value,...],[A2val,B2val...],...]. You should grab the width and length first and assign to a variable. Reference that variable instead of calling the API in the loop conditions. In fact, since you know the ranges ahead of time, you should define all the ranges you'll be needing outside of the main for loop, as well as the spreadsheet (ss):

var aa = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Run Program");
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet6");
var range1 = ss.getRange("b6:u6");
var range2 = ss.getRange("v6:ao6");
for (i = 1; i <= scenarios; i++) { ...

Scripts start taking a long time if you make repeated calls to the API.

Not sure exactly what your macro is trying to do, but here is a template for running this kind of loop and how you should think about it.

var range = ss.getRange(A1-style-address);
var values = range1.getValues();

var width = range1[0].length;
var height = range1.length;

var new_values = new Array(); 

for (var i = 0; i < height; i++) {
    for (var j = 0; j < width; j++) {

       //do something with values[i][j], like push to new_values

    }
}
//something like:
//var new_range = ss.getRange(different A1-style address) 
//new_range.setValues(new_array)  

You'll have to make sure that the new array contains values in a 2 dimensional array that has the same dimensions as the range you're putting them into or it will throw an error.

I'll leave it to you to figure out how to modify the new_values array inside the loop to make sure it is the right size.

Here's a link to an overview of arrays in javascript if this is new for you, and here's some array documentation.