Join several columns with a single formula returning an array in Google Sheets

Question

The title was taken from Join several columns with a single formula returning an array a Q&A hosted at http://webapps.stackexchange.com. The content is based on the original version of the answer posted by myself and posted to ARRAY_JOIN as a gist hosted at GitHub Gist

There could be differences as while I was writing this post I figured out some parts that should be corrected or enhanced (including grammar and spelling errors).

My main concerns are:

1. Performance of the custom function when large references or arrays are used as input.
2. The correct implementation as custom function
3. It's suitability for being included as part of a library or add-on.

---

filename: ARRAY_JOIN.md

---

ARRAY_JOIN

ARRAY_JOIN is a custom function that is part of a large personal project. It's purpose is to join the columns of one or several sets of columns, by sets. Accepts one or multiple ranges and/or arrays but they should have the same number of rows.

Demo spreadsheet

Sample usage

=ARRAY_JOIN("|",{A1:A3,B1:B3})
=ARRAY_JOIN("|",A1:B3,B1:C3)

Syntax

ARRAY_CONCATENATE([delimiter],reference_or_array1[,reference_or_array2, reference_or_array3])

[delimiter]: The character or string to place between each concatenated value..

reference_or_array1 [,reference_or_array2, reference_or_array3]: One or more references or arrays.

The custom function requires a helper function. Both are included in the gist as separate files.

Examples

Example 1

=ARRAY_JOIN("|",{A1:A3,B1:B3})

source

AA  Red
BB  Yellow
CC  Green

returns

AA|Red
BB|Yellow
CC|Green

Example 2

=ARRAY_JOIN("|",A1:B3,B1:C3)

source

AA    Red       Dog
BB    Yellow    Cat
CC    Green     Rabbit

returns

AA|Red        Red|Dog
BB|Yellow     Yellow|Cat
CC|Green      Green|Rabbit

TO DO:

1. Improve error catching/descriptions
2. i18n( internationalization) I doubt that it's possible that custom functions include support for multiple languages, so maybe I should to move from GitHub Gist to other code repository that allows to fork in order to have a version in English and other languages.

---

filename: Custom Funtions.gs

---

/**
 * Joins the columns in one or several sets, by sets. A set could be a reference
 * or an array.
 * 
 * @param {"|"}   delimiter            Delimiter
 * @param {A1:B4} reference_or_array   REferences or array
 * @return                             Columns joined by set.
 * @customfunction
 */
function ARRAY_JOIN(delimiter,reference_or_array){
  var i;
  var effectiveDelimiter;
  if(typeof(delimiter) == 'string'){
    i = 1;
    effectiveDelimiter  =delimiter;
  } else if (Array.isArray(delimiter)) {
    i = 0;
    effectiveDelimiter  = '';
  } else {
    throw new Error('The first parameter should be a string or an array.')
  }
  for (i ; i < arguments.length; i++) {
    if(!Array.isArray(arguments[i])){
      throw new Error('The reference or array should be references or arrays. '
        + 'Also all of them should have the same number or rows');
    }
  }  
    var result = new Array;
    for (var i = 0; i < arguments.length-1; i++) {
      result[i] = new Array;
      for (var j = 0; j < arguments[i+1].length; j++) {
        result[i].push(arguments[i+1][j].join(effectiveDelimiter));
      }
    }
    return transpose(result);
}

---

filename: Helper functions.gs

---

/**
 * Taken from http://stackoverflow.com/a/16705104/1595451
 * posted on May 23 of 2013 at 3:18
 * by [Mogsdad](http://stackoverflow.com/users/1677912/mogsdad)
 */
function transpose(a) {
  return Object.keys(a[0]).map(function (c) { 
    return a.map(function (r) { 
      return r[c]; 
    }); 
  });
}