I have a Google App Script that is supposed to read each row, see if Column C is marked "true", and if true data validation needs to be removed from all columns in that row. The example code below is not working. I am hoping someone can help educate me as to why.
function skl(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet2 = ss.getSheetByName('Savings Register');
var columnO2 = sheet2.getRange(2, 1, sheet2.getLastRow()-1, 1);
var oValues2 = columnO2.getValues();
for (var i = 0; i < oValues2.length; i++) {
if (oValues2[i][2] == 'TRUE') {
sheet2.getRange(i + 2, 1, 1, 9).setDataValidation(null);
}
}
}
If I change it to if (oValues2[i][0] == 'TRUE') the code functions when column A is true... but my criteria is column C. Shouldn't this code work with column C being the number 2?
var columnO2 = sheet2.getRange(2, 1, sheet2.getLastRow()-1, 1);
Your range is essentially specifying "A2:A", therefore column C is not included.
Here's a solution:
function myFunction() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Savings Register');
const data = sheet.getSheetValues(2, 1, sheet.getLastRow()-1, sheet.getLastColumn());
const targetRows = data.filter(i => i[2] === true);
for (let row of targetRows) {
const index = data.findIndex(i => i === row)+2;
sheet.getRange(index, 1, 1, 9).setDataValidation(null);
}
}
If you have any issues please let me know!