Google Sheets中indexOf()无法匹配字符串对象的问题排查

Let's break down why your indexOf() call isn't working, and how to fix it.

The Root Cause #

When you use getRange("G:G").getValues(), Google Apps Script returns a 2D array (an array of arrays), even for a single column. Your idList ends up looking like this:

[[123], [456], [789], [""]] // Each entry is a single-element array, plus empty cells

When you run idList.indexOf(csvData[i][6].toString()), you're trying to find a string (like "123") inside an array that contains arrays, not strings. Since none of the elements in idList are the string itself—they're single-element arrays holding the value—indexOf() will always return -1.

You mentioned direct string comparison works, which makes sense: if you manually grab a value from the 2D array (e.g., idList[0][0].toString() === csvData[i][6].toString()), you're comparing the actual string values, not trying to match a string against an array element.

The Fix: Convert to a 1D String Array #

We need to transform the 2D array into a flat array of strings, and clean up any empty cells (since your full column range will include blank rows). Here's how to adjust your code:

var ss = SpreadsheetApp.getActive();
// Transform the 2D range data into a clean 1D array of strings
var idList = ss.getSheetByName("Umsatzliste")
               .getRange("G:G")
               .getValues()
               // Flatten the 2D array to 1D
               .flat()
               // Remove empty cells (avoids matching blank CSV values accidentally)
               .filter(id => id !== "")
               // Convert every value to a string to match CSV data type
               .map(id => id.toString());

var filteredCSV = [];
for (var i = 0, lenCsv = csvData.length; i < lenCsv; i++) {
  var csvId = csvData[i][6].toString();
  if (idList.indexOf(csvId) !== -1) {
    filteredCSV.push(csvData[i]);
  }
}
csvData = filteredCSV;

Bonus: Optimize with a Set (Faster for Large Datasets) #

If your idList is large, using indexOf() (which loops through the array every time) can be slow. For better performance, convert the array to a Set instead:

var idSet = new Set(idList);
// Then in your loop:
if (idSet.has(csvId)) {
  filteredCSV.push(csvData[i]);
}

This cuts the lookup time from O(n) to O(1), which is a big win for large datasets.

内容的提问来源于stack exchange，提问作者clawjelly