Reputation: 177
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
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