Creating a custom formula in Google Sheets to concatenate a range of values with a delimiter

In the past, I have written a little VBA to create an Excel function to do this.  I use it to take a range of cells containing email addresses and make one string containing those addresses delimited by semi-colons suitable for pasting into the TO: field of an email.

Now that I am using Google Sheets more, I was curious if I could do a similar thing.

With a little investigation, I was able to go to Tools | Script Editor and enter the following:

 function CCAT(range, delimiter) {  
  var returnString = '';  
  var rows = range.length;  
  for (r=0; r<rows;r++){  
   var cols = range[r].length;  
   for (c=0; c<cols; c++){  
    returnString += range[r][c]+delimiter;  
   }  
  }  
  return returnString;  
 }  

Once it is saved I can invoke it from my Sheet using something like:

 =CCAT(C2:C25,";")  

Notice that a range like C2:C25 is automatically converted by Sheets into a two dimensional array.

After successfully creating the function, I searched for a similar solution online and found that there are ways to do it that do not require a custom script - see
https://productforums.google.com/forum/?hl=en#!category-topic/docs/how-do-i/FQbzbVK4-i0 , however my script is illustrative if not elegant.

Actionscript 2: Trouble overriding setter for MovieClip Properties like _visible

In my previous post, I described how we create classes to extend MovieClip.

Continuing with the ubSquare example, now let's suppose that I want to scale my square whenever it becomes visible.  Well, _visible is a property of the class, just like side was so how about adding some code like:

//at the top with _side
private var __visible:Boolean = true;

//with the side getter and setters
public function get _visible():Boolean{
 trace("in ubSquare get _visible");
 return this.__visible;
}
public function set _visible(newVis:Boolean):Void{
 trace("in ubSquare set _visible");
 this.__visible = newVis;
 this._visible = newVis;
}

This is just like what I did for side, except that I can't just change my private __visible property and expect the movieclip to change, so I add the last line to tell the movieclip to actually change its visibility.

There are two problems with this:

1. If I put a line like:
   this.mySquare_ubS._visible = false;
   in my tester, the set function is not called.
2. If I put a line like
   this._visible = true;
   in my scale method, I get 256 levels of recursion since the set for _visible calls itself.

One way to fix the second problem is to change the last line of the set _visible function from:
this._visible = newVis;
to
setProperty(this, _visible, newVis);

The output becomes:

scale making movieclip visible true
in ubSquare set _visible
in ubSquare get _visible

This, of course, begs three questions:

1. what is the point of writing a setter for _visible if it can be avoided by using a (deprecated) setProperty operation?
2. why is a get _visible call made when I set _visible? The line in the scale method (this._visible = true) calls both the setter and the getter.  I was sure I had a good link that explained why but cannot for the life of me find it now.
3. how do I solve the first problem which was the important one?  I want to be able to notice when anything makes my square visible, not just when some of my own class code does.

I have figured out one way to catch the setting of _visible for a MovieClip, which I will blog about next, but have yet to figure out how to apply it to a Class that extends MovieClip.

How can you make an Actionscript 2 function know about itself? How do you copy all functions from one movieclip to another?

Scoping has got to be one of the most tricky things in programming in Actionscript. A function like:

this.shape_mc.onPress = function(){
    trace(this);
}

will trace out the path to this.shape_mc when shape_mc is pressed, unless call or apply is used. If the same function above is invoked using call:

this.shape_mc.onPress.call(this.shape2_mc);

the path to this.shape2_mc will be traced!

Recently, there have been a number of times when I want a function to know about itself, the way shape_mc knows about itself as "this" in the function above. Here is a simple example of how that can be done:

//create a triangle
this.createEmptyMovieClip("triangle_mc", this.getNextHighestDepth());

this.triangle_mc.beginFill(0x00a900, 100);
this.triangle_mc.lineStyle(1, 0xFF0000, 100);
this.triangle_mc.moveTo(50,0);
this.triangle_mc.lineTo(100, 83);
this.triangle_mc.lineTo(0, 83);
this.triangle_mc.lineTo(50,0);
this.triangle_mc.endFill();

this.triangle_mc.onRelease = function(){
 this._parent.traceEvent.call(this, arguments.callee.name);
}
this.triangle_mc.onReleaseOutside = function(){
 this._parent.traceEvent.call(this, arguments.callee.name);
}

this.triangle_mc.onRelease.name = "onRelease";
this.triangle_mc.onReleaseOutside.name = "onReleaseOutside";

this.traceEvent = function(type:String){
 trace("An event of type:"+type+" happened to "+this);
}

From which you get output like:

    An event of type:onRelease happened to _level0.triangle_mc
    An event of type:onReleaseOutside happened to _level0.triangle_mc

There are three subtle things going on here. One is that arguments.callee is a self-reference to the function that is being executed. The second is that a function can be assigned properties, like name. Lastly, using call with traceEvent means that the "this" is changed from "_level0" to the first parameter in the call statement, in this case, _level0.triangle_mc.

You can find examples on the web where arguments.callee is used to implement recursion, like this one where the names of all functions are set recursively.

You can even set a property on a function to be another function, like

this.triangle_mc.onRelease.tracingFunction = this.traceEvent;

In which case the onRelease function could be:

this.triangle_mc.onRelease = function(){
 arguments.callee.tracingFunction.call(this, arguments.callee.name);
}

Getting dizzy yet?

Like call, apply lets you set the scope of a function, but the parameters are supplied in an array. The following example is the definition of a function in a class ubMath called coordsToGlobal which is used in the documented example to draw a bounding rectangle around one movieClip on another one.

/**returns an array of global (stage coordinates)
*@param mc (MovieClip) the movieclip that the coordinates are respect to.
*@param x (Number) the x coordinate in mc's coordinate system
*@param y (Number) the y coordinate in mc's coordinate system
*@param global_mc (MovieClip) optional movieclip to express the coordinates with respect to.  If undefined, it will truly be global.
*@returns (Array) [x, y]
*@example 

 import edu.clips.util.ubMath;

 var boundsObj:Object = mc.getBounds();
 var container:MovieClip = _root.createEmptyMovieClip("ubDebug_bounding"+(new Date()).getTime()+"_mc", _root.getNextHighestDepth());
 container.lineStyle(3, 0xFF0000, 70);
 container.moveTo.apply(container, ubMath.coordsToGlobal(mc, boundsObj.xMin, boundsObj.yMin));
 container.lineTo.apply(container, ubMath.coordsToGlobal(mc, boundsObj.xMax, boundsObj.yMin));
 container.lineTo.apply(container, ubMath.coordsToGlobal(mc, boundsObj.xMax, boundsObj.yMax));
 container.lineTo.apply(container, ubMath.coordsToGlobal(mc, boundsObj.xMin, boundsObj.yMax));
 container.lineTo.apply(container, ubMath.coordsToGlobal(mc, boundsObj.xMin, boundsObj.yMin));

*/
public static function coordsToGlobal(mc:MovieClip, x:Number, y:Number, global_mc:MovieClip):Array {
 var myPoint:Object = {x:x, y:y};
 mc.localToGlobal(myPoint);
 if (global_mc != undefined) global_mc.globalToLocal(myPoint);
 return [myPoint.x, myPoint.y];
}

You can also use arguments to pick up the parameters passed to a function. In the following example a function is created on newMc for every function in mc. The new function calls the old function on mc, scoped with newMc, with all the parameters that got passed to it.

for (var item in mc) {
 if (typeof(mc[item]) == "function"){
  newMc[item] = function(){
   var argArray:Array = new Array();
   for (var i=0; i<arguments.length;i++){
    argArray.push(arguments[i]);
   }
   return arguments.callee.functionDuplicatedFrom.apply(this, argArray);
  }
  newMc[item].functionDuplicatedFrom = mc[item];
 }
}

Oh no, I've said too much
I haven't said enough...