CODEWITHSUNDEEP
javascriptgoogle-apps-script
mattmoore5553
mattmoore5553

Reputation: 13

Google AppScript check cell value and delete column

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

Upvotes: 0

Views: 250

Answers (2)

RankinJ
RankinJ

Reputation: 121

Your 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 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

Question has changed since posting this reply

Upvotes: 1

Cooper
Cooper

Reputation: 64032

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

Upvotes: 1

Related Questions