CODEWITHSUNDEEP
javascriptgoogle-apps-script
Gary
Gary

Reputation: 11

Google script sheets get column by name

I cannot find any google script editor code that can find the index number of a google sheet column by its name

Already searched other problems and google documentation

function myFunction() 
{
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1");
  var rowNo = sheet.getLastRow();
  var colNo = sheet.getColumnName("Avg Price Per Seat");
  sheet.getRange(rowNo, colNo).setValue("=(E"+rowNo+"/M"+rowNo+")");
}

This should calculate the two columns E and M in the column called "Avg Price Per Seat" every time it runs

Upvotes: 1

Views: 7830

Answers (3)

user7718472
user7718472

Reputation: 11

We can use findIndex too. In some cases, some columns contain the word we are reaching.

function getColumnId(sheet) {
  var data = sheet.getDataRange().getValues();
  var col = data[0].findIndex((name) => name === 'ID') + 1;
  return col;
}

Upvotes: 1

ra89fi
ra89fi

Reputation: 1245

You can use this function to get col number by col name. Just pass sheet and col name. 

function getColumnFromName(sheet, name) {
  var headers = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues()[0];
  for (var i = 0; i < headers.length; i++) {
    if (headers[i] == name) return i + 1;
  }
  return -1;
}

Use as - var rowNo = getColumnFromName(sheet, 'Column Name');

Upvotes: 0

kht
kht

Reputation: 590

You can get an array of all columns, and then use the column name to find the one you want.

var cols = sheet.getDataRange().getValues();
var colNo = cols[0].indexOf(colName);

Upvotes: 1

Related Questions