0

I'm trying to filter CSV data by comparing it to an existing google sheet. This is the code:

var ss = SpreadsheetApp.getActive();
// get booking ID
var idList=ss.getSheetByName("Umsatzliste").getRange("G:G").getValues();
var filteredCSV=[];
for ( var i=0, lenCsv=csvData.length; i<lenCsv; i++ ){
    if (idList.indexOf(csvData[i][6].toString())!=-1) {
      filteredCSV.push(csvData[i]);
    }
}
csvData=filteredCSV;

The indexOf()-function never seems to work out. csvData is a 2d-array with all csv-values:

ss.toast("ID#1: "+idList[0]+" ID#2: "+csvData[2349][6]+" - "+(idList[0]==csvData[2349][6]).toString());

returns

ID#1: MC/000002674 ID#2: MC/000002674 - false

Alright, the typeof reveals they are both "objects", so i try convert the csvData to a string value:

ss.toast("ID#1: "+idList[0]+" ID#2: "+csvData[2349][6]+" - "+(idList[0]==csvData[2349][6].toString()).toString());

which returns:

ID#1: MC/000002674 ID#2: MC/000002674 - true

So a comparison works. Any idea why indexOf() doesn't work?

clawjelly
  • 25
  • 5

1 Answers1

0

The data idList retrieved by getValues() is 2 dimensional array. indexOf() cannot search the string from 2 dimensional array. So how about the following modification?

From :

if (idList.indexOf(csvData[i][6].toString())!=-1) {

To :

if (Array.prototype.concat.apply([], idList).indexOf(csvData[i][6].toString())!=-1) {

Note :

  • Using Array.prototype.concat.apply([], idList), 2 dimensional array is converted to 1 dimensional array.

References :

If I misunderstand your question, I'm sorry.

Tanaike
  • 139,542
  • 10
  • 71
  • 111
  • Alright, yea, that makes sense! The first test looks very promising. Thanks a lot! – clawjelly Jan 06 '18 at 14:38
  • @clawjelly Welcome. Thank you, too. If your question was solved, please push an accept button. Other people who have the same problem with you can also base your question as a question which can be solved. If you don't find the button, feel free to tell me. https://stackoverflow.com/help/accepted-answer – Tanaike Jan 06 '18 at 23:17