JavaScript Google Sheets: Finding column position using search term with indexOf method fails

Other Questions Google Sheets
1

I’m working on a Google Apps Script project and running into some issues. Here’s what I’m trying to do:

Goal 1: Find a cell containing “Employee Name” in my spreadsheet data.
Goal 2: Get the column number where this text is located.
Goal 3: Store that column number so I can work with data in that entire column.

I keep getting this error: TypeError: Cannot find function hasNext in object Employee Name,Department,[...].

I think I’m mixing up different object types but can’t figure out the right approach. Any help would be great!

function findColumnHeader() {
  var searchTerm = "Employee Name";
  var spreadsheetId = "1abc123def456ghi789jkl";
  var workbook = SpreadsheetApp.openById(spreadsheetId);
  var worksheet = workbook.getSheetByName("Data");
  var headerRow = worksheet.getSheetValues(1, 1, 1, 15);
  
  for(; headerRow.hasNext(); headerRow.next()) {
    if(headerRow.indexOf(searchTerm) > -1) {
      // Want to save the column position here
      // var columnPosition = headerRow.getCurrentIndex();
    }
    Logger.log(headerRow);
  }
}
2

Your error happens because getSheetValues() returns a regular array, not an iterator object. Arrays don’t have hasNext() - that’s for iterator patterns.

Here’s a cleaner approach using getRange():

function findColumnHeader() {
  var searchTerm = "Employee Name";
  var spreadsheetId = "1abc123def456ghi789jkl";
  var workbook = SpreadsheetApp.openById(spreadsheetId);
  var worksheet = workbook.getSheetByName("Data");
  var headerRange = worksheet.getRange(1, 1, 1, 15);
  var headerValues = headerRange.getValues()[0];
  
  var columnIndex = headerValues.indexOf(searchTerm);
  if(columnIndex !== -1) {
    var columnPosition = columnIndex + 1; // Convert to 1-based
    Logger.log("Column position: " + columnPosition);
    return columnPosition;
  }
  return null;
}

This is way more readable and handles the array conversion properly. The key difference is grabbing the first row with [0] before using indexOf().

3

you’re mixing up iterator methods with array methods. headerRow is a regular array, so skip the hasNext() stuff. just grab the row data directly and use indexOf(). try var row = headerRow[0]; var pos = row.indexOf('Employee Name'); - that should work. getSheetValues already gives you everything without needing to loop.

4

The problem is you’re treating headerRow like an iterator when it’s actually a 2D array from getSheetValues(). Skip the for loop entirely since you’re only grabbing one row:

function findColumnHeader() {
  var searchTerm = "Employee Name";
  var spreadsheetId = "1abc123def456ghi789jkl";
  var workbook = SpreadsheetApp.openById(spreadsheetId);
  var worksheet = workbook.getSheetByName("Data");
  var headerRow = worksheet.getSheetValues(1, 1, 1, 15)[0]; // Get first row as array
  
  var columnPosition = headerRow.indexOf(searchTerm);
  if(columnPosition > -1) {
    Logger.log("Found at column: " + (columnPosition + 1)); // Add 1 for 1-based indexing
  }
}

getSheetValues() returns a 2D array, so you need [0] to grab the first row for indexOf(). Also remember indexOf() gives zero-based positions, but Google Sheets uses 1-based column numbers.