如果 A 列中的值为真，则将 C 列设置高一 [英] If value is true in Column A, set Column C one higher
本文介绍了如果 A 列中的值为真，则将 C 列设置高一的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在编写一个脚本，该脚本使用 A 列中的一列复选框、B 列中的名称列表，然后是 A 列中的复选框为 B 列中的每个名称检查了多少次.
我让脚本在整个范围 A 中运行，查找True"语句(选中的框)并将结果应用到 C 列中的单元格......但它工作得不太正确.而不是增加单元格 C 中的值，我只能让它将值设置为 1，或者将值设置为第 1 + 1 列的整个结果字符串.
这是目前为止的脚本:
function setValues() {//获取您要使用的工作表.var ss = SpreadsheetApp.getActiveSpreadsheet();var sheet = ss.getSheetByName("Sheet1");//获取整个范围，并从中获取您需要的任何值.例如:范围值var range = sheet.getRange("A1:A10");var range2 = sheet.getRange("C1:C10");var range2values = range2.getValues();var rangevalues = range.getValues();//循环遍历范围结果for (var i in rangevalues) {for (var j in rangevalues) {//获取当前单元格的x,y位置.var x = parseInt(j, 10) + 1;var y = parseInt(i, 10) + 1;//设置规则逻辑如果(范围值[i][j] == 1){//设置单元格sheet.getRange(y,x+2).setValue(range2values+1);}}}}脚本将有更多内容来告诉它何时运行并在每次运行脚本后清除复选框，但是一旦我弄清楚这部分，我将在最后添加:
>How do I increment each cell in column C by 1 only if the box is checked next to the corresponding name?
解决方案
数组结构:
A1:A4 rangeValues 看起来像这样:
rangeValues[0] 是数组:
<代码>[A1]
rangeValues[1] 是数组:
<代码>[A2]
rangeValues[0][0] 是元素/值:
A1rangeValues[0][1] 是值:
未定义A1:B4 rangeValues 看起来像这样:
现在，
rangeValues[0] 是数组:
[A1,B1]rangeValues[0][0] 是元素/值:
A1rangeValues[0][1] 是元素/值:
B1数组按行索引，然后从零开始的列.
对于 A1:A10 范围值，您需要对数组中的每个值循环 10 次.使用时，
for (var i in rangevalues) {for (var j in rangevalues) {您实际上不必要地循环了 10*10 次:如果您使用 Logger.log("rangevalues["+i+"]["+j+"] 是:"+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] 是:[--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] 是:[--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] 是:[--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] 是:[--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] 是:[--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] 是:[--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] 是:[--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框被选中[--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() 代替.
修改后的脚本:
//@OnlyCurrentDoc函数 setValues() {//获取您要使用的工作表.var ss = SpreadsheetApp.getActiveSpreadsheet();var sheet = ss.getSheetByName("Sheet1");//获取整个范围，并从中获取您需要的任何值.例如:范围值var range = sheet.getRange(A1:A10");var range2 = sheet.getRange(C1:C10");var range2values = range2.getValues();var rangevalues = range.getValues();//循环遍历范围结果for (var i in rangevalues) {//for (var j in rangevalues) {Logger.log("rangevalues["+i+"]["+0+"] is:"+rangevalues[i][0]);//添加//获取当前单元格的x,y位置.//var x = parseInt(j, 10) + 1;//var y = parseInt(i, 10) + 1;//设置规则逻辑if (rangevalues[i][0] == true) {//修改//设置单元格range2values[i][0] += 1;//直接给range2values加1Logger.log(range2values);//新增}}range2.setValues(range2values);//新增;将修改后的 range2values 设置回 range2(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: 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);
}
}
}
}
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:
A1
rangeValues[0][1] is value:
undefined
A1: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:
A1
rangeValues[0][1] is element/value:
B1
Arrays 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:undefined
Since 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:
//@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)
}
Further Reading:
- Arrays
- Array Iteration
- Basic Reading
- TroubleShooting
- Best Practices
- Edit Trigger
- Loop Benchmarks
- Loop differences
这篇关于如果 A 列中的值为真，则将 C 列设置高一的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
