Google AppScript check cell value and delete column-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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);
    }
  }
}