Find string and get its column

Question

Let's say I have a lot of columns and one of them contains "impressions" string (on row 3). What I need to do is to: 1) Find the cell with "impressions" string 2) Get column number or i.e. "D" 3) Based on what I got paste a formula into i.e. D2 cell which gets AVERAGE from a range D4:D*last*

I couldn't find it anywhere so I have to ask here without any "sample" code, since I have no idea on how to achieve what I want. (3rd one is easy but I need to get that "D" first)

Derek Redfern · Accepted Answer · 2014-07-02 17:10:26Z

2

There's no way to search in Google Apps Script. Below is a function that will accomplish the first 2 parts for you (by iterating over every cell in row 3 and looking for "impressions"):

function findColumnNumber() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('Sheet1'); // insert name of sheet here
    var range = sheet.getDataRange(); // get the range representing the whole sheet
    var width = range.getWidth();

    // search every cell in row 3 from A3 to the last column
    for (var i = 1; i <= width; i++) {
        var data = range.getCell(3,i)
        if (data == "impressions") {
            return(i); // return the column number if we find it
        }
    }
    return(-1); // return -1 if it doesn't exist
}

Hopefully this will allow you to accomplish what you need to do!

edited Jul 2, 2014 at 17:10

answered Jul 1, 2014 at 22:43

Derek Redfern

1,01912 silver badges18 bronze badges

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

2 Comments

Serge insas Over a year ago

Please review your answer...This code is not searching the whole sheet at all, the range definition only gets cell A1.use getDataRange instead.

Derek Redfern Over a year ago

Serge, thanks for the correction, you're totally right. Editing now.

nanselm2 · Accepted Answer · 2021-06-15 20:51:40Z

The indexOf method allows one to search for strings:

function findColumnNumber() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet() //whatever tab the code is run on
  var data = sheet.getDataRange().getValues();
  var header_row_num = 1; // TODO: change this to whichever row has the headers. 
  var header = data[header_row_num -1]  //Remember JavaScript, like most programming languages starts counting (is indexed) at 0. For the value of header_row_num to work with a zero-index counting language like JavaScript, you need to subtract 1


  //define the string you want to search for
  var searchString = "impressions";
 
  //find that string in the header and add 1 (since indexes start at zero)
  var colNum = header.indexOf(searchString) + 1; 

  return(colNum);

Collectives™ on Stack Overflow

Find string and get its column

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related