Google-sheets – Script in Google Sheets which deletes every column that doesn’t have a header

google sheetsgoogle-apps-script

I'm trying to take an existing script but change it so that every column that is without a header is deleted.

function deleteColumns() {
  var required = ["Name", "Position", "Salary"];

  var sheet = SpreadsheetApp.getActiveSheet();
  var width = sheet.getLastColumn();
  var headers = sheet.getRange(1, 1, 1, width).getValues()[0];
  for (var i = headers.length - 1; i >= 0; i--) {
    if (required.indexOf(headers[i]) == -1) {
      sheet.deleteColumn(i+1);
    }
  }
}

I thought this might work but it doesn't

if (ISBLANK(required.indexOf(headers[i]) == -1)) {
  sheet.deleteColumn(i+1);
}

referenceError : ISBLANK is not defined.

Best Answer

Explanation:

You confuse Google Sheet Formulas like ISBLANK with Google Apps Script which uses JavaScript.

In JavaScript there is no built in method ISBLANK nor it is defined in your code and hence the error: this method is not defined.

To delete every column with an empty header, all you need to do in JavaScript is to check whether the header value is empty and if so delete the corresponding column:

if (headers[i] == '') {
    sheet.deleteColumn(i+1);
}

Solution:

Keep in mind that this script is executed on the active sheet. Namely, the sheet that is currently selected in the spreadsheet file.

function deleteColumns() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var width = sheet.getLastColumn();
  var headers = sheet.getRange(1, 1, 1, width).getValues()[0];
  for (var i = headers.length - 1; i >= 0; i--) {
    if (headers[i] == '') {
      sheet.deleteColumn(i+1);
    }
  }
}
Related Topic