2

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.

1 Answer 1

0

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);
    }
  }
}
1
  • 1
    Brilliant! thank you so much. Your insight has just solved three other sheets Vs JavaScript misunderstandings I had. Jan 12, 2021 at 15:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.