CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsindexof
marcoresk
marcoresk

Reputation: 1965

Google script indexOf not working for custom element

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

Answers (2)

Cooper
Cooper

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

TheAddonDepot
TheAddonDepot

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

Related Questions