How to use a column header to reference a cell in Google Apps Script Spreadsheet

Question

I have several Google Sheets that I connect and update cells between them. Right now I have to use R1C1 or A1 type references to define getting or setting cells based on specific columns.

If a new column is added, all those references are now off.

Row one of each sheet has column headers as values in those cells.

Can I reference a cell in a format such as [columnHeader]5 for the cell in that column, fifth row?

I thought of setting each individual column heading as a 'named range', but I am stuck at being able to reference a cell using [named range]5.

I suppose I could use some method of dynamically defining 100 variables to the then current column numbers (R1C1) format (in all the sheets) and then try to use those pseudo-header variables in my cell references. But I will probably run the scripts 100's of times a day and that horrible inefficiency hurts the engineer in me.

Thanks in advance.

chuck

Answer 1

I use a small helper function that returns the column index as a number that I can use in getRange(row,col)

It is very basic and goes like this :

function getColByName(name){
  var headers = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getDataRange().getValues().shift();
  var colindex = headers.indexOf(name);
  return colindex+1;
}

test like this :

function testgetColByName(){
  Logger.log(getColByName('header string'));  
}