Finding a Value in a Column

Question

I have the following which works great for finding a header value:

var lastCN = formResponsesSht.getLastColumn();
var data = formResponsesSht.getRange(1,1,1,lastCN).getValues();//Get 2D array of all values in row one
var data = data[0];//Get the first and only inner array
var statusCN = data.indexOf('Status') + 1;

Now I would like to find a value on a specific column. In this case B.

var lastRN = formResponsesSht.getLastRow();
var data = formResponsesSht.getRange(1,2,lastRN,1).getValues();//Get 2D array of all values in row one
var data = data[0];//Get the first and only inner array
var statusCN = data.indexOf('Status') + 1;

Why does this not work and is it possible to use the same strategy of indexOf?

ADW · Accepted Answer · 2020-01-29 17:21:21Z

1

If your sheet looked like this:

Then the output of

var data = formResponsesSht.getRange(1,2,lastRN,1).getValues();

is: [[a], [b], [c], [d], [e], [f], [g], [h], [i]]

And

var data = data[0];

gets you: [a], i.e. the first cell in that column.

.getValues() fetches data as an array where the "first and inner array" is an array of all the values in that row.

Edit: For finding the index of 'Status' in a column of data, try:

  for (var d = 0; d < data.length; d++) {
    if (data[d].indexOf('Status') > -1) {
      var statusCN = d + 1;  
      break;
    }
  }
  Logger.log(statusCN);

edited Jan 29, 2020 at 17:21

answered Jan 29, 2020 at 17:14

ADW

4,2471 gold badge17 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

DanCue Over a year ago

so because the data was in a row before it worked because all of the values were in one array, correct? Like this?: [[a,b,c,d,e,f,g,h,i],[]]

DanCue Over a year ago

Is there a proper way of doing this?

ADW Over a year ago

Sorry, I missed answering that in the first go. :-) Have added the snippet now. Hope this helps.

DanCue Over a year ago

Got it. A for loop. Thanks for the quick response. I may be searching for more than one value but I think I can figure that one out on my own.

ADW Over a year ago

You may also want to try using textFinder. Might be more efficient.

|

Collectives™ on Stack Overflow

Finding a Value in a Column

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related