GoogleScript Spreadsheet Custom Function Handling a range of cells and getting their values

Question

I have a Goggle Spreadsheet with some data, and I want to write a custom function to use in the sheet, which accepts a range of cells and a delimiter character, takes each cell value, splits it by the delimiter, and counts the total.

For example
Column A has the following values in rows 1-3: {"Sheep","Sheep,Dog","Cat"}
My function would be called like this: =CountDelimitedValues(A1:A3;",");
It should return the value: 4 (1+2+1)

The problem I am having is in my custom script I get errors like

"TypeError: cannot get function GetValues from type Sheep"

This is my current script:

function CountArrayList(arrayList, delimiter) {
  var count = 0;
  //for (i=0; i<array.length; i++)
  //{
    //count += array[i].split(delimiter).length;
  //}

  var newArray = arrayList.GetValues();
  return newArray.ToString();

  //return count;
}

I understand that the parameter arraylist is receiving an array of objects from the spreadsheet, however I don't know how to get the value out of those objects, or perhaps cast them into strings.

Alternatively I might be going about this in the wrong way? I have another script which extracts the text from a cell between two characters which works fine for a single cell. What is it about a range of cells that is different?

Community · Accepted Answer · 2017-04-13 12:57:16Z

3

That's something you can achieve without using script but plain old formula's:

=SUM(ARRAYFORMULA(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3; ","; "")) + 1))

Credit goes here: https://webapps.stackexchange.com/q/37744/29140

edited Apr 13, 2017 at 12:57

CommunityBot

11 silver badge

answered Jan 9, 2013 at 22:46

Jacob Jan

1,1973 gold badges19 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Serge insas · Accepted Answer · 2013-01-09 17:01:10Z

1

something like this works :

function CountArrayList(arrayList) {
return arrayList.toString().split(',').length
}

wouldn't it be sufficient ?

edit Oooops, sorry I forgot the user defined delimiter, so like this

function CountArrayList(arrayList,del) {
return arrayList.toString().split(del).length
}

usage : =CountArrayList(A1:C1;",")

NOTE : in this example above it would be dangerous to use another delimiter than "," since the toString() joins the array elements with commas... if you really need to do so try using a regex to change the commas to what you use and apply the split on that.

try like this :

function CountArrayList(arrayList,del) {
return arrayList.toString().replace(/,/g,del).split(del).length
}

edited Jan 9, 2013 at 17:01

answered Jan 9, 2013 at 15:55

Serge insas

46.9k7 gold badges113 silver badges135 bronze badges

1 Comment

Fletcher Over a year ago

That works and it bothers me because I could have sworn I tried that. Or I think perhaps I tried something like arrayList[0].toString() which didn't work.

Fletcher · Accepted Answer · 2013-01-10 14:24:44Z

0

Another solution I have was that I needed to implicitly cast the objects in the array being passed as a string.

For example this function accepts the array of cells, and outputs their contents as a string with del as the delimiter (similar to the String.Split() function). Note the TrimString function and that it is being passed an element of the array.

function ArrayToString(array,del) {
  var string = "";

  for (i=0; i < array.length; i++) {
    if (array[i] != null) {
      var trimmedString = TrimString(array[i]);
      if (trimmedString != "") {
        if (string.length > 0) {
          string += del;
        }

        string += trimmedString;
      }
    }
  }

  return string;
}

Below is the TrimString function.

function TrimString(string) {
  var value = "";
  if (string != "" && string != null) {
    var newString = "";
    newString += string;

    var frontStringTrimmed = newString.replace(/^\s*/,"");
    var backStringTrimmed = frontStringTrimmed.replace(/\s*$/,"");
    value = backStringTrimmed;
  }
  return value;
}

What I found is that this code threw a TypeError unless I included the declaration of the newString variable, and added the array element object to it, implicitly casting the array element object as a string. Otherwise the replace() functions could not be called.

answered Jan 10, 2013 at 14:24

Fletcher

371 silver badge6 bronze badges

2 Comments

Jacob Jan Over a year ago

Why do you want to use Google Apps Script for this? Because it is possible?

Fletcher Over a year ago

Mostly yes. The formula you presented earlier works, and actually I already had a formula similar to yours that did all of this plus more that didn't use Google Apps Script. There are some parameters to this problem that I have not completely shared. However the primary motivation was to condense the amount of code needed to be typed so that it could be re-used across the entire spreadsheet easily and quickly.

Collectives™ on Stack Overflow

GoogleScript Spreadsheet Custom Function Handling a range of cells and getting their values

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related