Google Apps Script for getting column number by column name

getColumnNrByName.js

 /**
 * @desc This is an Google Apps Script for getting column number by column name
 * @author Misha M.-Kupriyanov https://plus.google.com/104512463398531242371/
 * @link https://gist.github.com/5520691
 */
function testGetColumnNrByName() {
  var sheet = SpreadsheetApp.getActiveSheet();
  Logger.log(getColumnNrByName_(sheet, '%COLUM_TITLE%'));

}

function getColumnNrByName(sheet, name) {
  var range = sheet.getRange(1, 1, 1, sheet.getMaxColumns());
  var values = range.getValues();
  
  for (var row in values) {
    for (var col in values[row]) {
      if (values[row][col] == name) {
        return parseInt(col);
      }
    }
  }
  
  throw 'failed to get column by name';
}

Hi,

Maybe is more easy set a Range Name (in spreadsheet) for a cell title, and get the column from this Range Name. Like this:

var cl = SpreadsheetApp.getActiveSheet().getRange("RangeName").getColumn();

Thanks for making my life easy and posting this.

FWIW: In order to not get the array reference number (starts at 0), but get a number that can easily be passed to other Google Sheets functions, I found it more useful to return col+1.

@flurgas I did this but only to look in a column header.
My problem is that it returns a string instead of a number!!
the corresponding string is in 4th column, it outputs "31" instead of 4

function trouverNumColonne(nomFeuille, nom) {
  var feuille = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(nomFeuille);
  var entetes = feuille.getRange(2, 1, 1, feuille.getMaxColumns()).getValues()[0]; // [0] pcq la première ligne du vecteur rectangulaire
  Logger.log(entetes);
  for (var i in entetes) {
    Logger.log(entetes[i]);
    if ( entetes[i] == nom ) {
      return parseInt(i + 1);
    }
  }
}
  

@geoffm
i is a string (you can verify that by typeof), so i + 1 will be coerced to a string and therefore parseInt is returning "31" instead of 4.
The solution will be to modify it to: parseInt(i) + 1.
Si vous cherchez encore une solution pour ce problème ;)

helped me a lot, thank you @printminion

thanks

We use this in our GAS app:

/**
 * Get column number by header name.
 *
 * @param {string} name Header name
 * @returns {number}
 */
function getHeaderCol(name) {
  const [headerRowValues] = sheet.getDataRange().offset(0, 0, 1).getValues();
  return headerRowValues.indexOf(name) + 1;
}

One advantage of the getDataRange().offset() pattern is that the array is smaller (it excludes empty cells). We can avoid looping by using indexOf(). If the header row doesn't include name, then it will return a falsy 0, which works in our use case because we want to return column numbers and not indexes.