TypeError: Cannot read property 'length' of undefined (line 13, file "Code")

1
votes

This is my first time working with javascript and I'm just messing around in google sheets.

Currently, I have a 2D array which contains all of the names of the sheets in the first position and some ranges within those sheets in the second position, I'm trying to step through the array and change all of the spaces within the ranges to 0. I've got it to a point where the script changes the first ranges of the sheets to 0 barring the last 2 sheets for some reason

function CompButton(){
  var event = 0
  var sheets = [
    ['Rebel_Outpost',"D6:D14","D18:D23","I6:I13","I17","I21:I23","N6:N22","N26:N34","S6:S21","S26:S31"],
    ['Advanced_Rebel',"D6:D9","D13","D17","D21:D25","I6:I20","I24","I28","N6:N20"],
    ['Market',"D7:D16","D20:D23","J7:J12","J16:J18","J22:J24","P7:P17","P21:P29"],
    ['Civilian_Shops',"D6:D18","D22:D36","I6:I12","I16","I20:I26","I30:I32","N6:N15","S6:S23","S30:S36"],
    ['Ground_Vehicles',"D7:D13","D17:D26","D30:D34","G7:G13","G17:G26","G30:G34"],
    ['Air_Vehicles',"D7:D22","G7:G22"],
    ['Water_Vehicles',"D7:D11","G7:G11"] 
  ];
  while (event < 7){
    for (var i = 1; i < sheets[event][i].length; i++){
      var sheet = SpreadsheetApp.getActive().getSheetByName(sheets[event][0]); 
      sheet.getRange(sheets[event][1]).setValue(0);
      event++;
    }
  }
}

https://gyazo.com/3f59c6fda652681667010058092c8ae7

if u want access to the sheet to test ill post it below

https://docs.google.com/spreadsheets/d/1bv6Fn6oKw4KaEM-xke0dOd87nVw2xk3W42Nnc1sKdJQ/edit?usp=sharing

https://script.google.com/d/12LMcV_meIdGgKANTeQjPcfMr2wC2ZdlZp_jxPRTAyCLTR8kJUHDuBbmu/edit?mid=ACjPJvE8t2PeFTxeOvw1nJ3Wif_B9lFXlFGCTluqR7XBLSsC9zWuQYo-ou_c1cJYw1MUP2njhNE-aZMqJ0NaJBspj91BQfD_p-o5n3JK6WgK2LZ_Sbjba5cTSN-qDOB9M5tYJy0KtE5DABw&uiv=2

2
javascriptgoogle-sheets-macros
You have only 7 array elements not 9, so change it to while(event < 7) – rksh1997
true, I added extra just in case something was wrong with it – Callam Wibrow
And you are incrementing event inside for loop, not outside it – rksh1997
It should be inside while, but outside for – rksh1997
What @rksh1997 mentioned is the critical error—you need to move event++ out of the for loop. – Matt F.

2 Answers

0
votes

I'm not sure what exactly you're trying to do here but perhaps you'll find the below syntax more readable.

sheets.map(sheet => {
    sheet.map((el, index) => {
        if(index !== 0) {
            // Perform operation on el
        }
    })
})

For reference see map and forEach.

This post might not answer the original question but it certainly would help as an alternative.

0
votes

I think I got it now:

var sheets = [
  ['Rebel_Outpost',"D6:D14","D18:D23","I6:I13","I17","I21:I23","N6:N22","N26:N34","S6:S21","S26:S31"],
  ['Advanced_Rebel',"D6:D9","D13","D17","D21:D25","I6:I20","I24","I28","N6:N20"],
  ['Market',"D7:D16","D20:D23","J7:J12","J16:J18","J22:J24","P7:P17","P21:P29"],
  ['Civilian_Shops',"D6:D18","D22:D36","I6:I12","I16","I20:I26","I30:I32","N6:N15","S6:S23","S30:S36"],
  ['Ground_Vehicles',"D7:D13","D17:D26","D30:D34","G7:G13","G17:G26","G30:G34"],
  ['Air_Vehicles',"D7:D22","G7:G22"],
  ['Water_Vehicles',"D7:D11","G7:G11"] 
];

function zeroRanges(sheetsArray) {
  for(var sheetItem of sheetsArray) {
    var sheetName=sheetItem[0];
    console.log("Working on sheet \""+sheetName+"\"");
    for(var rangeItemIndex=1; rangeItemIndex<sheetItem.length; rangeItemIndex++) {
      var range=sheetItem[rangeItemIndex];
      console.log("--Zeroing range\""+range+"\"");
      // uncomment next 2 lines
      // var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName); 
      // sheet.getRange(range).setValue(0);
    }
  }
}

zeroRanges(sheets);

I wrote a function, zeroRanges that you pass the sheets to by using zeroRanges(sheets). This way you can re-use it.
When you copy the code, uncomment the 2 lines that use the SpreadsheetApp - I've commented them out here so we can see in the console log that the function is working (hoping the commented code is correct).
You can comment-out/remove the console.log lines.