尽管对象在数组中，但 indexOf 返回 -1 - Google 电子表格脚本中的 Javascript

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

提问by thrgamon

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.

我正在为 Google Docs 电子表格编写脚本以读取导演列表并将它们添加到数组中（如果它们尚未出现在数组中）。

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

但是，对于数组中包含的元素，我似乎无法让 indexOf 返回除 -1 以外的任何内容。

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]);
}
};

采纳答案by AdamL

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 valuesarray will actually look something like this:

当您使用getValues()在 Google Apps Script 中检索值时，您将始终处理 2D Javascript 数组（按行然后列索引），即使所讨论的范围是一列宽。因此，在您的特定情况下，并扩展 +RobG 的示例，您的values数组实际上看起来像这样：

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

[['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):

顺便说一句，值得注意的是，您可以使用 Sheets 原生的电子表格函数来实现这一点（直接键入电子表格单元格）：

=UNIQUE(Director)

=UNIQUE(Director)

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

这将随着命名范围的内容Director变化而动态更新。话虽如此，您可能有充分的理由希望为此使用 Google Apps 脚本。

回答by billmanH

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.

这听起来像是 GAS 而不是 JS 的问题。我一直在使用getValues() 时遇到问题。即使文档说它是一个二维数组，你也不能像你期望的那样与它进行比较。尽管如果您使用像这样的索引语句，values[0][1]您将获得基本数据类型。解决方案（我希望有更好的方法）是将该对象强制转换为String()，然后将其split()返回到您可以使用的数组中。

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.

如果 myDirector 在值中，您将得到一个数字 != -1。但是，数据中的逗号会导致问题。这仅适用于一维数组。

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

在您的情况下：var row = values[i];row是一个对象，而不是您要比较的字符串。将您的所有值转换为我上面的数组，并且您的比较运算符应该可以工作。（尝试打印行控制台来看看它说：Logger.log(row)）

回答by Cassiopeia

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.

因为我们正在处理一个二维数组，所以2dArray.indexOf("Search Term")必须有一个完整的一维数组作为搜索词。如果我们想在该数组中搜索单个单元格值，我们必须指定要查找的行。

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.

这意味着我们使用2dArray[0].indexOf("Search Term")if 我们的搜索词不是数组。这样做指定我们要查看数组中的第一个“行”。

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")

如果我们正在查看 3x3 单元格范围，并且想要搜索第三行，我们将使用 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"

下面的脚本获取电子表格中的当前行并将其转换为数组。然后它使用该indexOf()方法搜索该行"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
}

回答by Starfield Screensaver

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!).

如果有人看到这篇文章，您可能需要考虑使用下面的库。看起来它对我有用。即使在尝试提供的示例时，我也得到了“-1”的回报（感谢您的建议！）。

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

添加 Array Lib（版本 13）并使用 find() 函数后，我得到了正确的行！

This is the project key I used: MOHgh9lncF2UxY-NXF58v3eVJ5jnXUK_T

这是我使用的项目密钥：MOHgh9lncF2UxY-NXF58v3eVJ5jnXUK_T

And the references:

和参考：

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

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

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

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

Hopefully this will help someone else also.

希望这也能帮助其他人。

回答by AggieEric

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:

我知道我可以使用电子表格中的现有函数完成以下示例，但这是处理 2D 数组以搜索值的体面演示：

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.

因此，由于我只想在整个范围内搜索某个值是否存在，因此我只是将其展平为一个数组。如果您确实在处理 2D 范围，那么这种类型的展平和抓取索引可能不是很有用。就我而言，我正在查看一列以找到两个集合的交集。