CODEWITHSUNDEEP
javascriptarraysgoogle-apps-scriptgoogle-sheetsindexof
Joshua Bennier
Joshua Bennier

Reputation: 13

indexOf not working in Google Apps Script

I'm making a Google Sheets doc which analyzes my bank transaction history. A number of my transaction descriptions begin with the same letters, specifically "SIG" (correct casing). I want to count the number of these transactions, but I can't.

For troubleshooting, I've ensured that indexOf works when I'm only checking one cell/input. It returns "-1" when it can't find "SIG" in the cell and "0" when it does find "SIG" at the beginning of the cell.

Again troubleshooting, I've also ensured that I'm correctly looping through an array (multiple cells) which just counts the number of non-null cells. That also works.

When I try to put everything together tho, I can't get it to work, and I don't know why. The short function is below. Thanks for the help.

function SIG_counter (descriptions) {
  var SIG_total = 0;
  var SIG_checker;
  for (var i=0; i<descriptions.length; i++) {
    var SIG_checker = descriptions[i].indexOf("SIG");
    Logger.log(descriptions[i]);
    Logger.log(SIG_checker);
    if (SIG_checker == 0.0) {
      SIG_total++;
    }
  }
  return SIG_total;
}

var sample_array = ["Funds Added (Donation)",
                    "SIG POS purchase at Paypal",
                    "PIN POS purchase cashback",
                    "PIN POS purchase cashback",
                    "SIG POS purchase at Paypal"]

function trouble_shooter () {
  SIG_counter(sample_array);
}

Log:

[18-01-28 15:30:54:630 PST] Funds Added (Donation)
[18-01-28 15:30:54:630 PST] -1.0
[18-01-28 15:30:54:631 PST] SIG POS purchase at Paypal
[18-01-28 15:30:54:631 PST] 0.0
[18-01-28 15:30:54:632 PST] PIN POS purchase cashback
[18-01-28 15:30:54:632 PST] -1.0
[18-01-28 15:30:54:632 PST] PIN POS purchase cashback
[18-01-28 15:30:54:633 PST] -1.0
[18-01-28 15:30:54:633 PST] SIG POS purchase at Paypal
[18-01-28 15:30:54:634 PST] 0.0

Upvotes: 1

Views: 6991

Answers (1)

Jack Brown
Jack Brown

Reputation: 5892

When a column(Ex: B1:B22) is an input to the custom functions, the range is converted into a 2D array and passed to the function. In case you input a row(Ex B9:F9), it is converted into 1D array. In this case, your passing a column into the function and hence it is converted into a 2D array. So coressponding sample array should look like this:

var sample_array = [["Funds Added (Donation)"],
                    ["SIG POS purchase at Paypal"],
                    ["PIN POS purchase cashback"],
                    ["PIN POS purchase cashback"],
                    ["SIG POS purchase at Paypal"]]

And you will need to provide the index for the second dimension like sodescriptions[i][0] where [i] represents the first dimension and [0] the second dimension. So, in case of your sample array

sample_array[0][0] = "Funds Added (Donation)"
sample_array[1][0] = "SIG POS purchase at Paypal"

and so on.

Your modified function would look like this: 

function SIG_counter (descriptions) {

  var SIG_total = 0;
  var SIG_checker="";
  if(descriptions.map){ //Check if the arugment is array
                        //This code assumess it is always a 2D or a single value
  for (var i=0; i<descriptions.length; i++) {
    SIG_checker = descriptions[i][0].indexOf("SIG");
    Logger.log(descriptions[i][0]);
    Logger.log(SIG_checker);
    if (SIG_checker == 0.0) {
      SIG_total++;
    }
  } } else { //if arugment is not array, it refers to a single cell
    if(descriptions.indexOf("SIG") == 0.0){
      SIG_total = 1
    }

  }
  return SIG_total;
}

Please follow this documentation for further reference.

Final Note: The above function will only work when a column or a single cell is selected. If a row is selected, it will give an error!

Edit: Equivalently you can use this inbuilt function to do the same

=Arrayformula(sum(iferror(find("SIG",B1:B200),0)))

Upvotes: 2

Related Questions