IndexOf returns -1, despite the fact that the object is in an array - Javascript in Google Spreadsheet scripts

Question

I am writing a script for a Google Docs spreadsheet to read the list of directors and add them to the array if they are not already displayed in it.

However, I cannot get indexOf to return anything other than -1 for the elements contained in the array.

Can someone tell me what I am doing wrong? Or point me to an easier way to do this?

This is my script:

function readRows() { var column = SpreadsheetApp.getActiveSpreadsheet().getRangeByName("Director"); var values = column.getValues(); var numRows = column.getNumRows(); var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0]; var directors = new Array(); for (var i = 0; i <= numRows - 1; i++) { var row = values[i]; if (directors.indexOf(row) == -1) { directors.push(row); } else { directors.splice(directors.indexOf(row), 1, row); } } for (var i = 2; i < directors.length; i++) { var cell = sheet.getRange("F" + [i]); cell.setValue(directors[i]); } }; 

Answer 1

When you retrieve values ​​in Google Apps Script using getValues ​​() , you will always be dealing with a Javascript 2D array (with an index of a row, then a column), even if the range in question is one column wide. Thus, in your specific case and extension + RobG example, the values array will look something like this:

[['fred'], ['sam'], ['sam'], ['fred']]

So you will need to change

 var row = values[i]; 

to

 var row = values[i][0]; 

---

As an aside, it may be worth noting that to achieve this goal, you can use the spreadsheet function, native to sheets, which can be entered directly into the cell of the spreadsheet:

=UNIQUE(Director)

This will dynamically update as the contents of the range named Director change. That being said, this may be a good reason why you wanted to use Google Apps Script for this.

Answer 2

This sounds like a problem with GAS, not JS. I've always had problems with getValues ​​() . Despite the fact that the documentation says that it is a two-dimensional array , you cannot compare with it, as you would expect. Although, if you use an indexing operator of type values[0][1] , you will get a basic data type. The solution (I hope there is a better way) is to force this object into String () and then split () to return to an array that you can use.

Here is the code 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 the values, you get the 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]; a string is an object, not a string that you want to compare. Convert all your values to an array like I above, and your comparison operators should work. (try printing a line on the console to see what it says: Logger.log(row) )

Answer 3

I had a similar problem with a spreadsheet function that occupied a range as an object. In my case, I wanted to do a simple search for a fixed set of values ​​(in another array).

The problem is that your "column" variable does not contain a column - it contains a 2D array. Therefore, each value represents its own string (the array itself).

I know that I could execute the following example using the existing function in the spreadsheet, but this is a worthy demonstration of working with a 2D array to find the 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 just search the entire range for the existence of the value, I just flattened it into a single array. If you are really dealing with 2D ranges, this type of flattening and index grabbing may not be very useful. In my case, I looked through the column to find the intersection of the two sets.

Answer 4

If someone gets on this post, you might want to use the library below. It seems like this will work for me. I received a return of "-1" even when trying to provide examples (thanks for the suggestions!).

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

This is the project key I used: MOHgh9lncF2UxY-NXF58v3eVJ5jnXUK_T

And links:

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

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

Hope this helps another.

Answer 5

Since we are working with a 2D array, 2dArray.indexOf("Search Term") must have an entire 1D array as a search condition. If we want to find a single cell value inside this array, we must indicate which row we want to search.

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

If we were considering a 3x3 cell range and we wanted to search for 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. He then uses the indexOf() method to search for this string 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 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 }