Script in Google Sheets which deletes every column that doesn’t have a specific header

Posted on

Script in Google Sheets which deletes every column that doesn’t have a specific header – Whether you’re just starting out with Google Sheets or are a seasoned pro, sooner or later one of your formulas will give you a formula or script parse error message rather than the result you want. However, every Formula user, irrespective of whether it’s a beginner or expert, has invariably come across a formula parse error in Google Sheets at least once in their life (and if you haven’t. you soon will). It can be frustrating, especially if it’s a longer formula where the formula parse error may not be obvious. In this article, we will talk about some common script or formula parse errors in Google Sheets, like Script in Google Sheets which deletes every column that doesn’t have a specific header, and how to fix it.

Via a different service, I export a CSV file which I then import into Google Sheets. I then manually clean up the file in order to print out reports.

The issue is, the imported CSV file contains a bunch of columns of data which I do not need. However these columns are never in the same order, so I can’t reference the columns by a, b, c, 1, 2, 3, etc. The first row always contains the column headers, so if I can reference the columns by the text in the first row then that would be great.

All I want to do is:

  1. Import CSV
  2. Run Script which deletes all non-required columns so only the columns I need exist.

Currently my import process is:

  1. Go to shared folder in Google Docs
  2. Create new Google Sheet
  3. Import the CSV file
  4. Manually delete all columns and clean up the spreadsheet so I then print it.

I am open to various methods of doing this (even if it makes having a script make
a copy of the sheet and only putting the required columns in the new sheet), but would like to avoid having to use a 3rd party add-on or service.

Answer :

Here is a script that deletes all columns except those where the first row is one of the words listed in the “required” variable:

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

The logic is simple: get headers (the data in the first row), and loop over them, deleting whatever is not found in “required” array. There are two things that merit attention:

  1. The deletion process goes from right to left, so that deletion does not change the position of columns that are yet to be processed.
  2. JavaScript arrays are numbered starting with 0, while sheet columns are numbered starting with 1. Hence i+1 in the deleteColumn method.

Leave a Reply

Your email address will not be published. Required fields are marked *