Search code examples
google-sheetsgoogle-sheets-macros

Bombing out of google script due to an non declared array position

I have a very large 2 dimensional array (for this case lets just make it 20 rows x 5 columns). If I am on the nth row, I have to make check to see if the nth+1 row (lets say column 3) is less than the nth row. The problem is that the nth+1 row (data[nth+1] = []) has not been created so the script bombs.

(1) is there a way of telling if a row in an array has been created? or

(2) is there a way of telling the nth+1 array location has not been created? I tried isError_() but that just bombed script.

Example:

function tester() {
  var data = [ [] , [] ];  
  data[1] = [];  
  data[1][1] = 10;  
  for (i=1 ; i < 5 ; i++) {   
    if (data[1][1] < data[i+1][1]) { Browser.msgBox("You have a problem", Browser.Buttons.OK);}
  }  
}

Also: does anyone have a better suggestion for a script editor than the google sheets script editor. It seems to be light years behind EXCELS VBA editor? EXCEL lets you pick the 3 or 4 variables you want to see and not the total list of all variable, which you then have to search through. Also, as you move through an function using breaks, the list of variable keeps changing and moving variable names around, quite confusing. Thank you for any suggestions.

Solution

  • Array length:

    You can use the length property of the Array objects to limit the amount of iterations in your loop based on the length of your outer array, data.

    You would have to change this:

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

    To this:

    for (var i = 1 ; i < data.length - 1; i++) {

    Note:

    • If you initialize the loop with var i = 1, the first element in data gets ignored (the array indexes start at 0). Change that to var i = 0 if you want to avoid this.

    Reference: