Google Apps Script to VMerge tables WITH FORMATTING

Question

Does anyone know if there is a Google apps script out there that does what VMerge does but keeps the formatting of the tables being merged together? (in Google Spreadsheets)

VMerge is a script that can be used as a custom formula but a script that I can trigger myself will do just fine too.

Any help would be much appreciated.

Mogsdad Mogsdad · Accepted Answer · 2013-03-27T15:30:35

VMerge expects arrays-of-values as parameters, and therefore does not know what cells were referenced creating those arrays. When used as a Custom Formula, the sheet parser resolves all range parameters into their values before passing them to VMerge. Additionally, the parameters may be hard-coded or be the result of Queries or other functions that return ranges. Because of this alone, it's not feasible to modify VMerge to copy cell formats to the new merged table.

Complicating things further, Custom Functions cannot modify cells outside of the one they are attached to, they can only return values or arrays of values. From comment in Issue 37:

2) Scripts used as cell functions are not allowed to do complex things like connect to other APIs or set the values of other cells. Scripts used as cell functions are only allowed to return a value.

So you're going to have to settle for a function you call from scripts. The following function will join multiple ranges into a new table at a given anchor point. Because I started out trying to make this a custom function callable from a sheet, the parameters are string expressions of ranges, in a1Notation. (It could easily be refactored to deal directly with Range objects.)

The "Anchor" for the new range is expected to be a cell. One or more ranges of any size may be joined - each will be positioned directly below the previous.

Examples:

VJoin("D1","A1:B"); - All of columns A & B duplicated in columns D & E
VJoin("Sheet2!A1","Sheet1!C9:E10","Sheet1!A14:B15"); - Two different ranges in Sheet 1 joined and copied to Sheet 2.

Here's the code:

/*
 * Vertically join the ranges from multiple sources into a new table
 * starting at the given anchor point. Values and formatting are copied.
 *
 * @param {a1Notation} anchorA1 Anchor for joined table.
 * @param {a1Notation} sources One or more source ranges.
*/
function VJoin(anchorA1,sources) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var anchor = sheet.getRange(anchorA1);
  var anchorSheet = anchor.getSheet();  // in case anchorA1 is not on the "active sheet"
  var nextAnchor = anchor;

  for (var i in arguments) {
    // Arguments are expected to be Strings, containing a1Notation.
    if (i == 0) continue; // First argument was anchorA1, skip it.
    if (arguments[i].constructor == String) {
      var source = sheet.getRange(arguments[i]);
      var destination = anchorSheet.getRange(nextAnchor.getRow(), nextAnchor.getColumn(),
                                       source.getNumRows(), source.getNumColumns() );
      // Copy all values & formatting to new location.
      source.copyTo(destination);
      // Prepare for next range by moving our anchor
      nextAnchor = sheet.getRange(nextAnchor.getRow() + source.getNumRows(), 
                                  nextAnchor.getColumn());
    }
    else {
      throw new Error ("Expected String containing a1Notation.")
    }
  }
}

Google Apps Script to VMerge tables WITH FORMATTING

3 Answers