{var%20f='http://v.t.sina.com.cn/share/share.php?appkey=1515056452',u=z||d.location,p=['&url=',e(u),'&title=',e(t||d.title),'&source=',e(r),'&sourceUrl=',e(l),'&content=',c||'gb2312','&pic=',e(p||'')].join('');function%20a(){if(!window.open([f,p].join(''),'mb',['toolbar=0,status=0,resizable=1,width=440,height=430,left=',(s.width-440)/2,',top=',(s.height-430)/2].join('')))u.href=[f,p].join('');};if(/Firefox/.test(navigator.userAgent))setTimeout(a,0);else%20a();})(screen,document,encodeURIComponent,'','','https://www.manongdao.com/data/attach/logo/logo.png', '推荐 叛逆 的问题《Sheets Script: If value is true in Column A, set C》','https://www.manongdao.com/q-442276.html','页面编码gb2312|utf-8默认gb2312'));){kind=link}
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. How do I get each cell in column C to increment by 1 only if the box is checked next to the corresponding name?
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.
Just switch the appropriate line to
sheet.getRange(y,x+2).setValue(1+parseInt(range2values[y-1],10));That sets the value in the desired cell to one more than its original value. Notice range2values is zero-based, so we need that -1. Also range2values are strings, but we want to add 1 to the corresponding number.The code you had was setting the single cell to (range2values+1) but range2Values was an array.
Array Structure:
A1:A4
rangeValueswill look like this:rangeValues[0] is the array:
rangeValues[1] is the array:
rangeValues[0][0] is the element/value:
rangeValues[0][1] is value:
A1:B4
rangeValueswill look like this:[ [A1,B1], [A2,B2], [A3,B3], [A4,B4] ]Now,
rangeValues[0] is array:
rangeValues[0][0] is element/value:
rangeValues[0][1] is element/value:
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,
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:Since you're just getting a single column,you only need to loop over this once. In other words, the only valid
jin your loop is[0]. Everything else is undefined.Next,
Here, range2values is array. You need to add value to corresponding value. Hence, this might work:
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:
Further Reading: