如果 A 列中的值为 true，则将 C 列设置高一

2024-04-25

我正在编写一个脚本，该脚本使用 A 列中的一列复选框、B 列中的名称列表，然后是针对 B 列中的每个名称检查 A 列中的复选框的总次数。

我让脚本运行整个 A 范围，寻找“True”语句（选中的框）并将结果应用到 C 列中的单元格......但它工作得不太正确。我只能将其设置为 1，或者将值设置为第 1 + 1 列的整个结果字符串，而不是增加单元格 C 中的值。

这是到目前为止的脚本：

function setValues() {
//Get the sheet you want to work with. 
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Sheet1");
//Grab the entire Range, and grab whatever values you need from it. EX: rangevalues
var range = sheet.getRange("A1:A10");
var range2 = sheet.getRange("C1:C10");
var range2values = range2.getValues();
var rangevalues = range.getValues();
//Loops through range results
for (var i in rangevalues) {
 for (var j in rangevalues) {
 //Get the x,y location of the current cell.
     var x = parseInt(j, 10) + 1;
     var y = parseInt(i, 10) + 1;
 //Set the rules logic
    if (rangevalues[i][j] == 1) {
    //Set the cell 
       sheet.getRange(y,x+2).setValue(range2values+1);
   }
  }
 }
}

脚本中还会有更多内容来告诉它何时运行并在每次运行脚本后清除复选框，但一旦我弄清楚这部分，我将在最后添加这些内容：

如何将 C 列中的每个单元格增加1仅当选中相应名称旁边的框时？

数组结构：

A1:A4 rangeValues看起来像这样：


[
[A1],
[A2],
[A3],
[A4]
   ]

rangeValues[0] 是数组：

[A1]

rangeValues[1] 是数组：

[A2]

rangeValues[0][0] 是元素/值：

A1

rangeValues[0][1] 是值：

undefined

A1:B4 rangeValues看起来像这样：


[
[A1,B1],
[A2,B2],
[A3,B3],
[A4,B4]
     ]

Now,
rangeValues[0] 是数组：

[A1,B1]

rangeValues[0][0] 是元素/值：

A1

rangeValues[0][1] 是元素/值：

B1

数组按行索引，然后按列索引，从零开始。

对于 A1:A10 rangeValues，您需要对数组中的每个值循环 10 次。当你使用时，

for (var i in rangevalues) {
 for (var j in rangevalues) {

你基本上不必要地循环了 10*10 次：如果你使用Logger.log("rangevalues["+i+"]["+j+"] is:"+rangevalues[i][j])在此循环内，您将获得以下日志：


[--Time--:473] rangevalues[0][0] is:false
[--Time--:473] rangevalues[0][1] is:undefined
[--Time--:474] rangevalues[0][2] is:undefined
[--Time--:475] rangevalues[0][3] is:undefined
[--Time--:476] rangevalues[0][4] is:undefined
[--Time--:476] rangevalues[0][5] is:undefined
[--Time--:477] rangevalues[0][6] is:undefined
[--Time--:478] rangevalues[0][7] is:undefined
[--Time--:479] rangevalues[0][8] is:undefined
[--Time--:479] rangevalues[0][9] is:undefined
[--Time--:480] rangevalues[1][0] is:
[--Time--:481] rangevalues[1][1] is:undefined
[--Time--:481] rangevalues[1][2] is:undefined
[--Time--:482] rangevalues[1][3] is:undefined
[--Time--:483] rangevalues[1][4] is:undefined
[--Time--:484] rangevalues[1][5] is:undefined
[--Time--:484] rangevalues[1][6] is:undefined
[--Time--:485] rangevalues[1][7] is:undefined
[--Time--:486] rangevalues[1][8] is:undefined
[--Time--:486] rangevalues[1][9] is:undefined
[--Time--:487] rangevalues[2][0] is:false
[--Time--:488] rangevalues[2][1] is:undefined
[--Time--:488] rangevalues[2][2] is:undefined
[--Time--:489] rangevalues[2][3] is:undefined
[--Time--:490] rangevalues[2][4] is:undefined
[--Time--:490] rangevalues[2][5] is:undefined
[--Time--:491] rangevalues[2][6] is:undefined
[--Time--:492] rangevalues[2][7] is:undefined
[--Time--:492] rangevalues[2][8] is:undefined
[--Time--:493] rangevalues[2][9] is:undefined
[--Time--:494] rangevalues[3][0] is:
[--Time--:495] rangevalues[3][1] is:undefined
[--Time--:495] rangevalues[3][2] is:undefined
[--Time--:496] rangevalues[3][3] is:undefined
[--Time--:497] rangevalues[3][4] is:undefined
[--Time--:498] rangevalues[3][5] is:undefined
[--Time--:498] rangevalues[3][6] is:undefined
[--Time--:499] rangevalues[3][7] is:undefined
[--Time--:500] rangevalues[3][8] is:undefined
[--Time--:501] rangevalues[3][9] is:undefined
[--Time--:501] rangevalues[4][0] is:
[--Time--:502] rangevalues[4][1] is:undefined
[--Time--:503] rangevalues[4][2] is:undefined
[--Time--:503] rangevalues[4][3] is:undefined
[--Time--:504] rangevalues[4][4] is:undefined
[--Time--:505] rangevalues[4][5] is:undefined
[--Time--:506] rangevalues[4][6] is:undefined
[--Time--:506] rangevalues[4][7] is:undefined
[--Time--:507] rangevalues[4][8] is:undefined
[--Time--:507] rangevalues[4][9] is:undefined
[--Time--:508] rangevalues[5][0] is:
[--Time--:509] rangevalues[5][1] is:undefined
[--Time--:509] rangevalues[5][2] is:undefined
[--Time--:510] rangevalues[5][3] is:undefined
[--Time--:511] rangevalues[5][4] is:undefined
[--Time--:512] rangevalues[5][5] is:undefined
[--Time--:512] rangevalues[5][6] is:undefined
[--Time--:513] rangevalues[5][7] is:undefined
[--Time--:514] rangevalues[5][8] is:undefined
[--Time--:515] rangevalues[5][9] is:undefined
[--Time--:515] rangevalues[6][0] is:
[--Time--:516] rangevalues[6][1] is:undefined
[--Time--:517] rangevalues[6][2] is:undefined
[--Time--:517] rangevalues[6][3] is:undefined
[--Time--:518] rangevalues[6][4] is:undefined
[--Time--:519] rangevalues[6][5] is:undefined
[--Time--:519] rangevalues[6][6] is:undefined
[--Time--:520] rangevalues[6][7] is:undefined
[--Time--:521] rangevalues[6][8] is:undefined
[--Time--:522] rangevalues[6][9] is:undefined
[--Time--:522] rangevalues[7][0] is:
[--Time--:523] rangevalues[7][1] is:undefined
[--Time--:524] rangevalues[7][2] is:undefined
[--Time--:524] rangevalues[7][3] is:undefined
[--Time--:525] rangevalues[7][4] is:undefined
[--Time--:525] rangevalues[7][5] is:undefined
[--Time--:526] rangevalues[7][6] is:undefined
[--Time--:527] rangevalues[7][7] is:undefined
[--Time--:527] rangevalues[7][8] is:undefined
[--Time--:528] rangevalues[7][9] is:undefined
[--Time--:529] rangevalues[8][0] is:
[--Time--:529] rangevalues[8][1] is:undefined
[--Time--:530] rangevalues[8][2] is:undefined
[--Time--:531] rangevalues[8][3] is:undefined
[--Time--:531] rangevalues[8][4] is:undefined
[--Time--:532] rangevalues[8][5] is:undefined
[--Time--:533] rangevalues[8][6] is:undefined
[--Time--:533] rangevalues[8][7] is:undefined
[--Time--:534] rangevalues[8][8] is:undefined
[--Time--:535] rangevalues[8][9] is:undefined
[--Time--:535] rangevalues[9][0] is:true //A10 box is checked
[--Time--:536] rangevalues[9][1] is:undefined
[--Time--:536] rangevalues[9][2] is:undefined
[--Time--:537] rangevalues[9][3] is:undefined
[--Time--:538] rangevalues[9][4] is:undefined
[--Time--:538] rangevalues[9][5] is:undefined
[--Time--:539] rangevalues[9][6] is:undefined
[--Time--:539] rangevalues[9][7] is:undefined
[--Time--:540] rangevalues[9][8] is:undefined
[--Time--:541] rangevalues[9][9] is:undefined

由于您只得到一列，因此只需循环一次。换句话说，唯一有效的j在你的循环中是[0]。其他一切都是未定义的。

Next,

sheet.getRange(y,x+2).setValue(range2values+1);

这里，range2values 是数组。您需要将值添加到相应的值中。因此，这可能有效：

sheet.getRange(y,x+2).setValue(range2values[i][j]+1);

但是，使用 setValue() 并不是一个好的做法，因为您将向电子表格写入 100 次值，这会导致成本高昂。始终使用批处理操作 - 创建输出数组并使用setValues()反而。

修改后的脚本：

//@OnlyCurrentDoc
function setValues() {
  //Get the sheet you want to work with. 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");
  //Grab the entire Range, and grab whatever values you need from it. EX: rangevalues
  var range = sheet.getRange("A1:A10");
  var range2 = sheet.getRange("C1:C10");
  var range2values = range2.getValues();
  var rangevalues = range.getValues();
  //Loops through range results
  for (var i in rangevalues) {
    // for (var j in rangevalues) {
    
    Logger.log("rangevalues["+i+"]["+0+"] is:"+rangevalues[i][0]);//Added
    
    //Get the x,y location of the current cell.
    //    var x = parseInt(j, 10) + 1;
    //    var y = parseInt(i, 10) + 1;
    //Set the rules logic
    if (rangevalues[i][0] == true) { //Modified
      //Set the cell 
      range2values[i][0] += 1; //Directly add 1 to range2values
      Logger.log(range2values);//Added
    }
  }
  range2.setValues(range2values);//Added; Set the modified range2values back to range2(Column C)
}

进一步阅读：

Arrays https://www.w3schools.com/js/js_arrays.asp
数组迭代 https://www.w3schools.com/js/js_array_iteration.asp
基础阅读 https://developers.google.com/sheets/api/samples/reading
故障排除 https://developers.google.com/apps-script/guides/support/troubleshooting
最佳实践 https://developers.google.com/apps-script/guides/support/best-practices
编辑触发器 https://developers.google.com/apps-script/guides/triggers/#onedit
循环基准 https://gist.github.com/tanaikech/848aeafaac1ec676900bb78e3ce220b6
循环差异 https://stackoverflow.com/questions/500504/why-is-using-for-in-with-array-iteration-a-bad-idea

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

Arrays

multidimensionalarray

googleappsscript

googlesheets