indexOf returning -1 despite object being in the array - Javascript in Google Spreadsheet Scripts

It sounds like an issue with GAS and not the JS. I have always had trouble with getValues(). Even though the documentation says that it is a two dimensional array, you can't compare with it like you would expect to. Although if you use an indexing statement like values[0][1] you will get a basic data type. The solution (I hope there is a better way) is to force that object into a String() and then split() it back into an array that you can use.

Here is the code that I would use:

 var column = SpreadsheetApp.getActiveSpreadsheet().getRangeByName("Director");
 var values = column.getValues();
      values = String(values).split(",");
 var myIndex = values.indexOf(myDirector);

If myDirector is in values you will get a number != -1. However, commas in your data will cause problems. And this will only work with 1D arrays.

In your case: var row = values[i]; row is an object and not the string that you want to compare. Convert all of your values to an array like I have above and your comparison operators should work. (try printing row to the console to see what it says: Logger.log(row))

I ran into a similar problem with a spreadsheet function that took a range as an object. In my case, I was wanting to do a simple search for a fixed set of values (in another array).

The problem is, your "column" variable doesn't contain a column -- it contains a 2D array. Therefore, each value is it's own row (itself an array).

I know I could accomplish the following example using the existing function in the spreadsheet, but this is a decent demo of dealing with the 2D array to search for a value:

function flatten(range) {
  var results = [];
  var row, column;

  for(row = 0; row < range.length; row++) {
    for(column = 0; column < range[row].length; column++) {
      results.push(range[row][column]);
    }
  }
  return results;
}

function getIndex(range, value) {
  return flatten(range).indexOf(value);
}

So, since I wanted to simply search the entire range for the existance of a value, I just flattened it into a single array. If you really are dealing with 2D ranges, then this type of flattening and grabbing the index may not be very useful. In my case, I was looking through a column to find the intersection of two sets.

Because we are working with a 2D array, 2dArray.indexOf("Search Term") must have a whole 1D array as the search term. If we want to search for a single cell value within that array, we must specify which row we want to look in.

This means we use 2dArray[0].indexOf("Search Term") if our search term is not an array. Doing this specifies that we want to look in the first "row" in the array.

If we were looking at a 3x3 cell range and we wanted to search the third row we would use 2dArray[2].indexOf("Search Term")

The script below gets the current row in the spreadsheet and turns it into an array. It then uses the indexOf() method to search that row for "Search Term"

//This function puts the specified row into an array.
//var getRowAsArray = function(theRow) 
function getRowAsArray()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();     // Get the current spreadsheet
  var theSheet = ss.getActiveSheet();                 // Get the current working sheet
  var theRow = getCurrentRow();                       // Get the row to be exported
  var theLastColumn = theSheet.getLastColumn();       //Find the last column in the sheet.


  var dataRange = theSheet.getRange(theRow, 1, 1, theLastColumn); //Select the range
  var data = dataRange.getValues();            //Put the whole range into an array
  Logger.log(data);                            //Put the data into the log for checking
  Logger.log(data[0].indexOf("Search Term"));  //2D array so it's necessary to specify which 1D array you want to search in.
  //We are only working with one row so we specify the first array value,
  //which contains all the data from our row
}

If someone comes across this post you may want to consider using the library below. It looks like it will work for me. I was getting '-1' return even when trying the examples provide (thanks for the suggestions!).

After adding the Array Lib (version 13), and using the find() function, I got the correct row!

This is the project key I used: MOHgh9lncF2UxY-NXF58v3eVJ5jnXUK_T

And the references:

https://sites.google.com/site/scriptsexamples/custom-methods/2d-arrays-library#TOC-Using

https://script.google.com/macros/library/d/MOHgh9lncF2UxY-NXF58v3eVJ5jnXUK_T/13

Hopefully this will help someone else also.

I had a similar issue. getValues() seems to be the issue. All other methods were giving me an indexOf = -1

I used the split method, and performed the indexOf on the new array created. It works!

  var col_index = 1;
  var indents_column = main_db.getRange(1,col_index,main_db.getLastRow(),1).getValues();
  var values = String(indents_column).split(","); // flattening the getValues() result
  var indent_row_in_main_db = values.indexOf(indent_to_edit) + 1; // this worked

I ran into the same thing when I was using

let foo = Sheet.getRange(firstRow, dataCol, maxRow).getValues();

as I was expecting foo to be a one dimensional array. On research for the cause of the apparently weird behavior of GAS I found this question and the explanation for the always two dimensional result. But I came up with a more simple solution to that, which works fine for me:

let foo = Sheet.getRange(firstRow, dataCol, maxRow).getValues().flat();