CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsfind
Omid
Omid

Reputation: 177

How find a specific column index based on a cell value

In Google Sheet I have two sheets named STUDENTS and CLASSES in one spreadsheet file. Each of them contains a lot of rows of data under some column headers.

Column headers of STUDENTS are a subset of the column headers of CLASSES.

I want my G.A.S code to recognize the active cell in STUDENTS, then recognize the column header of that active cell, then go and find that column header among the column headers of CLASSES, and finally return the column index of that header in CLASSES.

This is the code I have written:

  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getSheetByName('Students');
  var targetSheet = spreadsheet.getSheetByName('Classes');
  var sheetColumnIndex = sheet.getActiveCell().getColumnIndex();
  var sheetColumnHeader = sheet.getRange(1,sheetColumnIndex).getValues();

  var numRows = 1 // The column headers of CLASSES are available in 1 row
  var numColumns = 22 // The number of column headers in CLASSES;

  var searchRange = targetSheet.getRange(1,1,numRows, numColumns);
  // get the values in an array
  var values = searchRange.getValues();
  // examine the values in the array

  for (var y = 0; y < values.length; y++) {
     if(values[0][y] == sheetColumnHeader){

       var columnIndexInSourceSheet = y + 1;

     };
  };

  return columnIndexInSourceSheet;

};

But unfortunately the code won't work! I have no idea why!

Upvotes: 1

Views: 2481

Answers (1)

Anton Dementiev
Anton Dementiev

Reputation: 5716

It doesn't work because you are comparing a string to an array. The 'getValues()' method returns the 2D array of values. For example, if the header is 'name', it will return

var values = range.getValues();
Logger.log(values); //[["name"]]

Use the 'getValue()' method for the sheetColumnHeader variable.

Upvotes: 1

Related Questions