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

如何通过递增仅当框下检查，以相应的名字吗?在C列中的每个小区解决方案 数组结构: 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] ]现在，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].其他所有内容均未定义.下一步，sheet.getRange(y,x+2).setValue(range2values+1);在这里，range2values是数组.您需要将值添加到相应的值.因此，这可能会起作用:sheet.getRange(y,x+2).setValue(range2values[i][j]+1);但是，使用setValue()并不是一个好习惯，因为您将价值100次写入电子表格中，这非常昂贵.始终使用批处理操作-创建一个输出数组，并改用setValues().修改后的脚本://@OnlyCurrentDocfunction 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)}进一步阅读: 数组 数组迭代 基础阅读 故障排除 最佳做法 编辑触发器 基准测试 循环差异I'm working on a script that uses a column of checkboxes in Column A, a list of names in column B, then a running total of how many times the checkboxes in column A have been checked for each name in column B.I have the script running through the entire range A, looking for "True" statements (boxes that are checked) and applying the results to the cells in column C ... but it doesn't work quite right. Rather than increasing the value in cell C, I can only get it to set the value to 1, or to set the value to the entire string of result for column 1 + 1.Here is the script so far: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: rangevaluesvar range = sheet.getRange("A1:A10");var range2 = sheet.getRange("C1:C10");var range2values = range2.getValues();var rangevalues = range.getValues();//Loops through range resultsfor (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); } } }}There will be a little more to the script to tell it when to run and to clear out the checkboxes after each time the script is run, but those I will add at the end once I have this part figured out:How do I increment each cell in column C by 1 only if the box is checked next to the corresponding name? 解决方案 Array Structure:A1:A4 rangeValues will look like this:[[A1],[A2],[A3],[A4] ]rangeValues[0] is the array:[A1]rangeValues[1] is the array:[A2]rangeValues[0][0] is the element/value: A1rangeValues[0][1] is value: undefinedA1:B4 rangeValues will look like this:[[A1,B1],[A2,B2],[A3,B3],[A4,B4] ]Now,rangeValues[0] is array:[A1,B1]rangeValues[0][0] is element/value: A1rangeValues[0][1] is element/value: B1Arrays are indexed by rows and then columns starting with zero.For A1:A10 rangeValues, You'll need to loop 10 times over each value in array.When you use, for (var i in rangevalues) { for (var j in rangevalues) {You're essentially looping 10*10 times unnecessarily: If you use Logger.log("rangevalues["+i+"]["+j+"] is:"+rangevalues[i][j]) inside this loop,You'll get these logs: [--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:undefinedSince you're just getting a single column,you only need to loop over this once. In other words, the only valid j in your loop is [0]. Everything else is undefined.Next, sheet.getRange(y,x+2).setValue(range2values+1);Here, range2values is array. You need to add value to corresponding value. Hence, this might work: sheet.getRange(y,x+2).setValue(range2values[i][j]+1);However, It's not good practice to use setValue() as you'll be writing value to the spreadsheet 100 times,which is costly. Always use batch operations- Create a output array and use setValues() instead.Modified Script://@OnlyCurrentDocfunction 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)}Further Reading:ArraysArray IterationBasic ReadingTroubleShootingBest PracticesEdit TriggerLoop BenchmarksLoop differences 这篇关于如果A列中的值为true，则将C列设置为更高的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！