16

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

user1489765
  • 291
  • 2
  • 12

1 Answers1

21

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'));  
}
Serge insas
  • 45,904
  • 7
  • 105
  • 131
  • Thanks Serge, I modified it to handle getting column numbers from multiple sheets by passing in the appropriate "getSheetByName" and specific column header. – user1489765 Jul 04 '15 at 18:32
  • Sorry, hit CR too soon. I wanted to add that I am accepting this as the answer. While it may not be the best (nothing against Serge) it works for me. Thanks again to Serge. – user1489765 Jul 04 '15 at 18:34
  • 4
    Ahhh, Serge; I couldn't figure why you chose the entire range and used shift(). Then I finally found a reference to "The shift() method removes the first item of an array, and returns that item." Awesome work! I was going down the path of restricting the range to only the top row (I really only wanted the headers); but your method handled that. Hats off to you! – user1489765 Jul 06 '15 at 02:30