I currently have a project where i have to loop through and check 3 different cell values. If the values contain #VALUE1 then it is supposed to delete the column. I am not sure what I am doing wrong. I keep getting an error and column never deletes. Also I get a loop error that I can not figure out.
function scoreToGrade() {
var cellsChecked = ['AF2', 'AG2', 'AH2'];
var sheet = SpreadsheetApp.getActiveSheet();
for (var i = 0; i < cellsChecked.length; i++) {
var value = SpreadsheetApp.getActiveSheet()
.getRange(cellsChecked[i])
.getDisplayValues();
console.log(value);
if (value == '#VALUE!') {
// do something
console.log('Not blank ' + value);
sheet.deleteColumn(cellsChecked + 1);
} else {
// do something else
console.log('is another value ' + value);
}
}
}
the log is as follows
3:41:23 PM Notice Execution started
3:41:23 PM Info [ [ 'TR' ] ]
3:41:23 PM Info is BlankTR
3:41:23 PM Info [ [ 'F' ] ]
3:41:23 PM Info is BlankF
3:41:23 PM Info [ [ '#VALUE!' ] ]
3:41:23 PM Info Not blank #VALUE!
3:41:23 PM Error
Exception: Cannot convert 'AF2,AG2,AH21' to int.
scoreToGrade @ Code.gs:115
You error appears to be in the line you are using to delete the columns
sheet.deleteColumn(cellsChecked+1);
You are passing an array of cells to this function instead of the single cell that that the method expects. Try passing in a specific array item similar to what you did when getting the value of the cell earlier in your script.
sheet.deleteColumn(cellsChecked[i]);
Reference: https://developers.google.com/apps-script/reference/spreadsheet/sheet#deletecolumncolumnposition
Try this:
function scoreToGrade() {
var cellsChecked = ['AF2', 'AG2', 'AH2'];
var sheet = SpreadsheetApp.getActiveSheet();
for (var i = 0; i < cellsChecked.length; i++) {
var value = sheet.getRange(cellsChecked[i]).getDisplayValue();
console.log(value);
if (value == '#VALUE!') {
// do something
console.log('Not blank ' + value);
sheet.deleteColumn(sheet.getRange(cellsChecked[i]).getColumn());
} else {
// do something else
console.log('is another value ' + value);
}
}
}