thrgamon thrgamon - 3 months ago 38
Javascript Question

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

I am writing a script for a Google Docs Spreadsheet to read a list of directors and add them to an array if they do not already appear within it.

However, I cannot seem to get indexOf to return anything other than -1 for elements that are contained within the array.

Can anyone tell me what I am doing wrong? Or point me to an easier way of doing 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

When you retrieve values in Google Apps Script with getValues(), you will always be dealing with a 2D Javascript array (indexed by row then column), even if the range in question is one column wide. So in your particular case, and extending +RobG's example, your values array will actually look something like this:

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

So you would need to change

var row = values[i];

to

var row = values[i][0];

As an aside, it might be worth noting that you can use a spreadsheet function native to Sheets to achieve this (typed directly into a spreadsheet cell):

=UNIQUE(Director)

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

Comments