Straightening Elements in 2 Dimensional Array using Google Apps Script

GasTips
Google Apps Script / spreadsheet / Javascript

Gists

This sample script is for straightening elements in 2 dimensional array using Google Apps Script (GAS). When applications using Spreadsheet are developed by GAS, it usually uses 2 dimensional array by setValues(). And the lengths of each element are required to be the same. On the other hand, data used for the applications might not be the same length for each element in 2 dimensional array. This sample script can be used under such situation.

Sample 1 :

var data = [
  ["string"],
  ["string", "string", "string", "string", "string"],
  ["string", "string", "string"],
  ["string", "string", "string", "string", "string"],
  ["string", "string", "string", "string", "string", "string", "string", "string", "string"],
  ["string", "string"],
  ["string", "string", "string", "string", "string", "string", "string"]
];

var n = 0;
for (var i in data) {
  if (n < data[i].length) {
    n = data[i].length;
  }
}
var temp = data.slice();
for (var i in temp) {
  for (var j=0, len=temp[i].length; j<n-len; j++) {
    data[i].push("");
  }
}
Logger.log(JSON.stringify(data))

Flow :

  1. Retrieve maximum length of element by for loop.
  2. Add elements with the difference between the maximum length and the element for each element as "".

Sample 2 :

This is solved by one liner script.

var data = [
  ["string"],
  ["string", "string", "string", "string", "string"],
  ["string", "string", "string"],
  ["string", "string", "string", "string", "string"],
  ["string", "string", "string", "string", "string", "string", "string", "string", "string"],
  ["string", "string"],
  ["string", "string", "string", "string", "string", "string", "string"]
];

var result = [
  data[i].concat(
    Array.apply(null, Array(
      data.slice().sort(
        function(a, b) {
          return (a.length < b.length ? 1 : -1);
        }
      )[0].length - data[i].length)
    ).map(
      function() {
        return "";
      }
    )
  )
  for (i in data)
];
Logger.log(JSON.stringify(result))

Flow :

  1. Retrieve maximum length of element by sorting array.
  2. Add elements with the difference between the maximum length and the element for each element as "".

Result :

[
    ["string","","","","","","","",""],
    ["string","string","string","string","string","","","",""],
    ["string","string","string","","","","","",""],
    ["string","string","string","string","string","","","",""],
    ["string","string","string","string","string","string","string","string","string"],
    ["string","string","","","","","","",""],
    ["string","string","string","string","string","string","string","",""]
]

 Share!