Question

TypeError：无法读取未定义的属性‘length’（第 13 行，文件“Code”）

2020-08-02

208

javascript google-sheets-macros

这是我第一次使用 javascript，我只是在 google 表格中乱搞。

目前，我有一个 2D 数组，其中包含第一个位置的所有工作表的名称和第二个位置中这些工作表内的一些范围，我正在尝试遍历数组并将范围内的所有空格更改为 0。我已经到了脚本将工作表的第一个范围更改为 0 的地步，但最后 2 个工作表除外

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

如果你想访问工作表进行测试，我会将其发布在下面

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

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

Answer 1

我不确定您在这里到底想做什么，但也许您会发现下面的语法更具可读性。

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

有关参考，请参阅 map 和 forEach 。

这篇文章可能无法回答原始问题，但它肯定会有所帮助。

Answer 2

我想我现在明白了：

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

我编写了一个函数 zeroRanges ，您可以使用 zeroRanges(sheets) 将 sheets 传递给该函数。这样您就可以重复使用它。
复制代码时，取消注释使用 SpreadsheetApp 的 2 行 - 我在这里将它们注释掉，以便我们可以在控制台日志中看到该函数正在运行（希望注释的代码是正确的）。
您可以注释掉/删除 console.log 行。