Reputation: 1965
this is a sample of my script in google sheet. How can I set the indexOf method to work in all cases?
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("MySheet");
var myArray = sheet.getRange("A7:A10").getValues();
//this works (of course) -> returns 2
sheet.getRange('L1').setValue(myArray.indexOf(myArray[2]));
//this don't work (but it can be my fault in typing..) -> returns -1
sheet.getRange('L2').setValue(myArray.indexOf("Thing"));
//this don't work (why?) -> returns -1
sheet.getRange('L3').setValue(myArray.indexOf(sheet.getRange("A9").getValues()));
//to check my value, this works
Browser.msgBox(myArray[2]);
Where am I wrong? All contents in cells are string.
Upvotes: 0
Views: 396
Reputation: 64140
I played around with this and I finally realized that I needed to convert the data back to a one dimensional array. This was the code I was playing with. It's not exactly like yours but it does a similar sort of thing.
function test() {
var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
sh.getRange('A1:D5').clear();
sh.getRange('A1:A5').setValues([["One"],["Twp"],["Three"],["Four"],["Five"]]);//just intializing my data
var mA = sh.getRange("A1:A5").getValues();
var dA=[];
for(var i=0;i<mA.length;i++){
sh.getRange(i+1,2).setValue(mA[i][0]);
dA.push(mA[i][0]);
sh.getRange(i+1,3).setValue(dA.indexOf(mA[i][0]));
sh.getRange(i+1,4).setValue(dA.indexOf(sh.getRange(i+1,2).getValue()));
}
}
So you could add this to your code to get it to work.
var myArray = sheet.getRange("A7:A10").getValues();
var mA=[];
for(var i=0;myArray.length;i++){
mA.push(myArray[i][0]);
}
And then use mA.indexOf()
instead of myArray.indexOf();
Upvotes: 1
Reputation: 8984
getValues()
returns an array of arrays (also known as a two-dimensional array).
So basically if you had a sheet populated with data as follows:
+---+------+----+--------+
| | A | B | C |
+---+------+----+--------+
| 1 | John | | Doe |
| 2 | Jane | | Doe |
| 3 | John | Q. | Public |
+---+------+----+--------+
Calling getValues()
would give you an 2D array like this:
var myArray = sheet.getRange('A1:C3').getValues();
Logger.log(myArray);
/* Logger output will look something like this:
[
["John", null, "Doe"],
["Jane", null, "Doe"],
["John", "Q.", "Public"]
]
*/
So going back to your sheet, myArray.indexOf("Thing")
is trying to match the string object "Thing" against an array object; that will always result in -1. You need to refactor your code to take two dimensional arrays into account.
Upvotes: 2