CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Sean W
Sean W

Reputation: 407

Finding column number of a cell with specific value

I currently have a code that can get the row number of a cell that contains a specific string. Here's an example of the code: 

var ss = SpreadsheetApp.getActiveSheet();
var values = ss.getRange("B:B").getValues();
var i=j=firstrow=lastrow=0;
for(i=0;i<values.length;i++)
for(j=0;j<values[i].length;j++) {
  if(values[i][j]==5100) firstrow=i+1;
else if(values[i][j]=='EOL') lastrow=i-2;
}

I was wondering if it's possible to do something like above, but for columns, that way my script will not fall apart if an user accidentally move a column.

Upvotes: 1

Views: 5057

Answers (1)

FuzzyJulz
FuzzyJulz

Reputation: 2874

So, what are doing is using .getRange("B:B") to define that you want all rows in column B. Then using getValues() to return all of those rows as a multidimensional array(obviously this will only have one column - so you probably don't need that other for loop).

So instead you can just use .getRange(row, column) (where row and column are integers greater than 1), this way you can go through the spreadsheet one item at a time using getValue(). So you could initially look through the first row to find the column index you are after, and then look down the rows to find the data you require.

Something like this might work for you:

var ss = SpreadsheetApp.getActiveSheet();
var valueColumn;

for(i=1;i<ss.getLastColumn();i++) {
  if (ss.getRange(1,i).getValue() == "ColumnName") {
    valueColumn = i;
    break;
  }
}

//At this point I assume that we have a value in valueColumn, if not this won't work.

var values = ss.getRange(2, valueColumn, ss.getLastRow()).getValues();
var i=firstrow=lastrow=0;
for(i=0;i<values.length;i++) {
  if(values[i][0]==5100) firstrow=i+1;
  else if(values[i][0]=='EOL') lastrow=i-2;
 }

One thing to keep in mind is that arrays are 0 based where as getRange(row,column[,rows]) is 1 based.

My spreadsheet is only small, so speed impacts of doing one call and getting all data is minimal, but if you are actually using a large sheet you might find one method works faster than another.

Upvotes: 1

Related Questions