Find Column Number By Column Header Name

What Does This Code Snippet Do?

This getColumnByName() code snippet is a fast and efficient way to quickly identify the Column Index of a specific keyword/term. It does this by using the .indexOf() method which quickly finds the position within an Array that matches the lookup value you specify.

For example:

// If you have this array: let array = ['apple', 'orange', 'banana', 'blueberry', 'grape'] // And you want to find the position of 'banana' - you can say let bananaIndex = array.indexOf('banana') // Output: 2

Notice how the output is 2 instead of 3 though. That's because arrays always start from position 0 instead of position 1. In our code snippet, we adjust that for you so that it's easier to utilize with the rest of your Google Apps Script code. You'll see how in the sections below!

How to Use This Code Snippet

To use this code snippet, you'll need 3 parameters:

1. The Sheet Object
2. The name of the column you want (aka the keyword/term)
3. The row number of the row where your table's header row is located

The Sheet Object is something you'll get by using the SpreadsheetApp library in Google Sheets. Feel free to check out our code snippet for getting all sheets with Google Apps Script for info on how to reference all of you sheets in one go.

The name of the column you want will be passed through as a string/text. Make sure that it's an exact match to the column headers for the table you're referencing - it helps to copy/paste the text so you don't have a typo!

Lastly, the row number for your table's column headers is literally how it sounds. It's the row number for your column headers. So if it's in row 1 in your table, it's 1. If it's in row 2, it's 2.

Let's take this example for instance!

If you wanted to find the column index for NAME, you'd call the getColumnByName() code snippet like this:

getColumnByName(sheet, 'NAME', 1) // Output: 2

Great Uses for This Code

This code snippet is super helpful when you're working with a Google Sheet were the columns might get switched around from time to time OR if you're working with multiple tables where the column positions aren't always the same and you don't want to keep track of the column indexes for each one.

It's also really helpful when you're manipulating the data within the Google Sheet because we're returning the adjusted column index for you, so you can plug it right into your .getRange() calls without any transformations.

For example, If you were updating the Prices of the table of fruits above and wanted to grab all of the current prices. You could call the code snippet right from the same line as you're setting the range and values. Like so:

sheet.getRange(2, getColumnByName(sheet, 'PRICE', 1)), sheet.getLastRow(), 1).getValues() // Output: ['$1.99','$3.99','$0.79','$2.79','$3.49','$2.50','$4.99','$5.99','$8.99']