Apps Script Spreadsheet Utilities.md

Google Apps Script Spreadsheet Utilities and Custom Functions#

These utilities are grouped into related files, for simpler copy & paste to your scripts.

ConvertA1.gs

A couple of helper functions to convert to & from A1 notation.

cellA1ToIndex( string cellA1, number index )

Convert a cell reference from A1Notation to 0-based indices (for arrays) or 1-based indices (for Spreadsheet Service methods).

colA1ToIndex( colA1, index )

Return a 0-based array index corresponding to a spreadsheet column label, as in A1 notation.

rowA1ToIndex( string rowA1, number index )

Return a 0-based array index corresponding to a spreadsheet row number, as in A1 notation. Almost pointless, really, but maintains symmetry with colA1ToIndex().

CSVUtils.gs

Provides a collection of handy-dandy utilities and helpers for dealing with CSV files in Google apps-script.

importFromCSV(string fileName, string sheetName)

Populates a sheet with contents read from a CSV file located in the user's GDrive. If either parameter is not provided, the function will open inputBoxes to obtain them interactively.

Automatically detects tab or comma delimited input.

Adapted from Tutorial: Interacting With Your Docs List.

8 Apr 2015 - DocsList deprecated

Throws Error

• "No Input File" if input CSV not found.

arrayFromCSV(string fileName)

Returns an array with contents read from a CSV file located in the user's GDrive.

Adapted from Tutorial: Interacting With Your Docs List.

8 Apr 2015 - DocsList deprecated

Throws Error

• "No filename" if input parameter missing.
• "No Input File" if input CSV not found.

sheetToArray(string sheetName, string id)

Populate an Array with the contents of the given Sheet. This represents the rectangular region that encompases all populated cells in the given sheet. Two modes of operation are supported. When called from a spreadsheet script, the active spreadsheet is used. Otherwise, it is expected that a spreadsheet is provided by its id. If the sheet name does not exist in the spreadsheet, an empty 2-D Array is returned.

arrayToSheet(Array csvData, string sheetName)

Populate a sheet with contents of the given 2D Array.

VJoin.gs

VJoin(anchorA1,sources)

This Custom Function will join multiple ranges into a new table at a given anchor point. To operate as 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.

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.

GoogleForms.gs

fixFormEvent( e )

Addresses the bug reported as Google Apps Script Issue 4355, wherein blank responses are skipped in the event object passed to trigger functions.

Force blank reponses into event object's values property, so that the value's index correctly reflects the question order. (With "new Sheets" + "new Forms", blank responses are skipped in the event object.)

function onFormSubmit(e) {
  fixFormEvent( e );
  ...
}

downloadXLS.gs

downloadXLS(fileId)

Using the Advanced Drive Service, this function will create a copy of a Google Spreadsheet, formatted as an Excel .xlsx file. See this StackOverflow Question for more info.

Toaster.gs

Toaster is a utility Class that wraps the Spreadsheet.toast() method with an object, and provides additional support for multi-line toast messages in Google Spreadsheeets. See this StackOverflow Question for more info.

Usage:

function testToaster() {
  var myToast = new Toaster( "Msg Line 1\nMsg Line 2\nAnother Line that's longer\nFollowed by one more.",
                             "Multi-line toast", 5 );
  myToast.display();
}

Displays:

Multi-line toast

Msg Line 1
Msg Line 2
Another Line that's longer
Followed by one more.

ConvertA1.gs

/**
 * Convert a cell reference from A1Notation to 0-based indices (for arrays)
 * or 1-based indices (for Spreadsheet Service methods).
 *
 * @param {String}    cellA1   Cell reference to be converted.
 * @param {Number}    index    (optional, default 0) Indicate 0 or 1 indexing
 *
 * @return {object}            {row,col}, both 0-based array indices.
 *
 * @throws                     Error if invalid parameter
 */
function cellA1ToIndex( cellA1, index ) {
  // Ensure index is (default) 0 or 1, no other values accepted.
  index = index || 0;
  index = (index == 0) ? 0 : 1;
  
  // Use regex match to find column & row references.
  // Must start with letters, end with numbers.
  // This regex still allows induhviduals to provide illegal strings like "AB.#%123"
  var match = cellA1.match(/(^[A-Z]+)|([0-9]+$)/gm);

  if (match.length != 2) throw new Error( "Invalid cell reference" );
  
  var colA1 = match[0];
  var rowA1 = match[1];
  
  return { row: rowA1ToIndex( rowA1, index ),
           col: colA1ToIndex( colA1, index ) };
}

/**
 * Return a 0-based array index corresponding to a spreadsheet column
 * label, as in A1 notation.
 *
 * @param {String}    colA1    Column label to be converted.
 *
 * @return {Number}            0-based array index.
 * @param {Number}    index    (optional, default 0) Indicate 0 or 1 indexing
 *
 * @throws                     Error if invalid parameter
 */
function colA1ToIndex( colA1, index ) {
  if (typeof colA1 !== 'string' || colA1.length > 2) 
    throw new Error( "Expected column label." );
  
  // Ensure index is (default) 0 or 1, no other values accepted.
  index = index || 0;
  index = (index == 0) ? 0 : 1;

  var A = "A".charCodeAt(0);
  
  var number = colA1.charCodeAt(colA1.length-1) - A;
  if (colA1.length == 2) {
    number += 26 * (colA1.charCodeAt(0) - A + 1);
  }
  return number + index;
}



/**
 * Return a 0-based array index corresponding to a spreadsheet row
 * number, as in A1 notation. Almost pointless, really, but maintains
 * symmetry with colA1ToIndex().
 *
 * @param {Number}    rowA1    Row number to be converted.
 * @param {Number}    index    (optional, default 0) Indicate 0 or 1 indexing
 *
 * @return {Number}            0-based array index.
 */
function rowA1ToIndex( rowA1, index ) {
  // Ensure index is (default) 0 or 1, no other values accepted.
  index = index || 0;
  index = (index == 0) ? 0 : 1;

  return rowA1 - 1 + index;
}

CSVUtils.gs

/**
 * CSVUtils - a collection of handy-dandy utilities and helpers
 * for dealing with CSV files in Google apps-script.
 *
 * JSDoc available at:
 * https://docs.google.com/macros/library/versions/d/MIDNeFeoqJsoA5khfks-IfAGkAAfQWqd9
 *
 * @author Mogsdad
 *
 * TODO: consider abandoning current approach altogether, and using http://splitterjsobj.sourceforge.net/.
 */
"use strict";

/**
 * Populate an Array with the contents of the given Sheet.
 * This represents the rectangular region that encompases all populated
 * cells in the given sheet. 
 *
 * Two modes of operation are supported. When called from a spreadsheet
 * script, the active spreadsheet is used. Otherwise, it is expected that
 * a spreadsheet is provided by its id.
 *
 * If the sheet name does not exist in the
 * spreadsheet, an empty 2-D Array is returned.
 *
 * @param {string} sheetName The name of the source sheet.
 *        If the sheet does not exist, it will be created by
 *        this function. A pre-existing sheet will be cleared 
 *        before importing the CSV data.
 *
 * @param {string} id (Optional) Unique identifier for the
 *        spreadsheet to access. Needed if calling this function
 *        from a apps-script service.
 *
 * @returns {Array} A 2-dimensional array containing data
 *        retrieved from the named sheet.
 */
function sheetToArray(sheetName,id) {
  var sheetData = [[]];
  // getActiveSpreadsheet() returns null if script called as a
  // service (i.e. not from within a spreadsheet)
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  // If we don't have an active spreadsheet, try opening one.
  if (ss === null) {
    try
      {
        ss = SpreadsheetApp.openById(id);
      }
    catch(err)
      {
        var log="Error accessing spreadsheet id " + id +", ";
        log+="Error description: " + err.message;
        Logger.log(log);
      }
  }
  if (ss !== null ) {
    var sheet = ss.getSheetByName(sheetName);
    if (sheet !== null) {
      sheetData = sheet.getDataRange().getValues();
    }
  }

  return sheetData;
}

/**
 * Populate a sheet with contents of the given 2D Array.
 *
 * @param {Array}csvData A 2-dimensional array containing source data
 *        to be written to a spreadsheet.
 *
 * @param {string} sheetName The name of the destination sheet.
 *        If the sheet does not exist, it will be created by
 *        this function. A pre-existing sheet will be cleared 
 *        before importing the CSV data.
 *
 * @returns {JsonObject} A summary of the write operation,
 *        including the number of rows and columns imported.
 * <PRE>
 *
 *   { numRows : 2541, numCols : 22 }
 *  </PRE>
 */
function arrayToSheet(csvData,sheetName) {
  // Array.prototype.isArray = true; // TODO implement variable checking
  csvData = csvData || [[]];
  sheetName = sheetName || "";
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName);
  if (sheet === null) {
    ss.insertSheet(sheetName);
    sheet = ss.getSheetByName(sheetName);
  }

  sheet.clear();

  var numRows = csvData.length;
  var numCols = csvData[0].length; // assume all rows are same width
    
  // Make a single call to spreadsheet API to write values to sheet.
  sheet.getRange(1, 1, numRows, numCols).setValues( csvData );
  //SpreadsheetApp.flush();

  // Return an object with setValue results
  return { numRows : numRows, numCols : numCols };
}

/**
 * Populates a sheet with contents read from a CSV file located
 * in the user's GDrive. If either parameter is not provided, the
 * function will open inputBoxes to obtain them interactively.
 *
 * Adapted from <I>Tutorial: Interacting With Your Docs List.</I> (https://developers.google.com/apps-script/articles/docslist_tutorial#section2)
 *
 * 8 Apr 2015 - DocsList deprecated
 *
 * @param {string}fileName (Optional) The name of the input file.
 *
 * @param {string} sheetName (Optional) The name of the destination sheet.
 *        If the sheet does not exist, it will be created by
 *        this function. A pre-existing sheet will be cleared 
 *        before importing the CSV data.
 *
 * @returns {JsonObject} A summary of the import operation,
 *        including the date the input file was last updated
 *        [see <a href="https://developers.google.com/apps-script/class_file#getLastUpdated">File.getLastUpdated()</a>], and the number of rows
 *        and columns imported.
 * <PRE>
 *
 *   { lastUpdated : 5-Mar-2012, numRows : 2541, numCols : 22 }
 *  </PRE>
 *
 *  @throws {Error} "No Input File" if input CSV not found.
 */
function importFromCSV(fileName,sheetName) {
  var useBrowser = (!fileName);   // Assume that spreadsheet UI in use, if no args
  fileName = fileName || Browser.inputBox("Enter the name of the file in your Docs List to import (e.g. myFile.csv):");
  sheetName = sheetName || Browser.inputBox("Enter the name of the sheet to import into (e.g. Sheet1):");
  
  var csvData = new Array();

  var csv = getCsvFileByName_(fileName);
  if (!csv) {
    if (useBrowser) {
      Browser.msgBox("No Input File", "Either no file name was provided, or file does not exist.", Browser.Buttons.OK);
    }
    else {
      throw new Error("No Input File");
    }
  }
  else {
    // Detect delimiter - tab or comma
    var delim = ",";
    if (csv.csvFile.indexOf('\t') != -1) delim = "\t"; 
    
    csvData = Utilities.parseCsv(csv.csvFile, delim);
    
    var setResult = arrayToSheet(csvData, sheetName);    
  }

  // Report results
  if (useBrowser) {
    Browser.msgBox("Success", "Copied " + setResult.numRows + " rows from '" + fileName
                   + "', last updated " + csv.lastUpdated + ", to '" + sheetName + "' sheet.", Browser.Buttons.OK);
  }

  // Return an object with import results
  return { lastUpdated : csv.lastUpdated, numRows : setResult.numRows, numCols : setResult.numCols };
}

/**
 * Returns an array with contents read from a CSV file located
 * in the user's GDrive.
 *
 * Adapted from <I>Tutorial: Interacting With Your Docs List.</I>
 (https://developers.google.com/apps-script/articles/docslist_tutorial#section2)
 *
 * 8 Apr 2015 - DocsList deprecated
 *
 * @param {string}fileName (Optional) The name of the input file.
 *
 * @returns {Array} A 2D array representation of the CSV file's contents.
 *
 *  @throws {Error} 
 * <UL>
 *  <LI>"No filename" if input parameter missing.
 *  <LI>"No Input File" if input CSV not found.
 * </UL>
 */
function arrayFromCSV(fileName) {
  if (typeof fileName === 'undefined') {
    throw new Error("No fileName");
  }
  
  var csvData = new Array();

  var csv = getCsvFileByName_(fileName);
  if (!csv) {
    throw new Error("No Input File");
  }
  else {
    // Detect delimiter - tab or comma
    var delim = ",";
    if (csv.csvFile.indexOf('\t') != -1) delim = "\t"; 
    
    csvData = Utilities.parseCsv(csv.csvFile, delim);
  }

  // Return an array with import results
  return csvData;
}


function getCsvFileByName_( fileName ) {
  var files = DriveApp.getFilesByName(fileName);
  var result = null;

  if (files.hasNext()) {
    result = {};
    result.file = files.next();
    result.csvFile = result.file.getBlob().getDataAsString();
    result.lastUpdated = result.file.getLastUpdated();
  }
  
  return result;
}

downloadXLS.gs

// For testing, in a spreadsheet-bound script only
function test_downloadXLS() {
  var fileId = SpreadsheetApp.getActive().getId();
  downloadXLS( fileId );
}

/**
 * Downloads spreadsheet with given file id as an Excel file.
 * Uses Advanced Drive Service, which must be enabled.
 * Throws if error encountered.
 *
 * From http://stackoverflow.com/a/27281729/1677912
 *
 * @param {String}   fileId       File ID of Sheets file on Drive.
 */
function downloadXLS(fileId) {
  var file = Drive.Files.get(fileId);
  var url = file.exportLinks[MimeType.MICROSOFT_EXCEL];
  
  var options = {
    headers: {
      Authorization:"Bearer "+ScriptApp.getOAuthToken()
    },
    muteHttpExceptions : true        /// Get failure results
  }
  
  var response = UrlFetchApp.fetch(url, options);
  var status = response.getResponseCode();
  var result = response.getContentText();
  if (status != 200) {
    // Get additional error message info, depending on format
    if (result.toUpperCase().indexOf("<HTML") !== -1) {
      var message = strip_tags(result);
    }
    else if (result.indexOf('errors') != -1) {
      message = JSON.parse(result).error.message;
    }
    throw new Error('Error (' + status + ") " + message );
  }
  
  var doc = response.getBlob();
  DriveApp.createFile(doc).setName(file.title + '.xlsx');
}


// A JavaScript equivalent of PHP’s strip_tags
// from http://phpjs.org/functions/strip_tags/
function strip_tags(input, allowed) {
  allowed = (((allowed || '') + '')
    .toLowerCase()
    .match(/<[a-z][a-z0-9]*>/g) || [])
    .join(''); // making sure the allowed arg is a string containing only tags in lowercase (<a><b><c>)
  var tags = /<\/?([a-z][a-z0-9]*)\b[^>]*>/gi,
    commentsAndPhpTags = /<!--[\s\S]*?-->|<\?(?:php)?[\s\S]*?\?>/gi;
  return input.replace(commentsAndPhpTags, '')
    .replace(tags, function($0, $1) {
      return allowed.indexOf('<' + $1.toLowerCase() + '>') > -1 ? $0 : '';
    });
}

GoogleForms.gs

/**
 * Force blank reponses into event object's values property, so that the value's index
 * correctly reflects the question order. (With "new Sheets" + "new Forms", blank responses
 * are skipped in the event object.)
 *
 * see http://stackoverflow.com/a/26975968/1677912
 *
 * @param {event} e   Event received as a Spreadsheet Form object. The event's value
 *                    property will be modified by this function.
 * @return {event}    The same event, for chaining
 */
function fixFormEvent( e ) {
  var ss = SpreadsheetApp.getActive();
  var formUrl = ss.getFormUrl();             // Use form attached to sheet
  var form = FormApp.openByUrl(formUrl);
  var items = form.getItems();

  var resp = [e.namedValues["Timestamp"]];

  for (var i=0; i<items.length; i++) {
    switch (items[i].getType()) {
      case FormApp.ItemType.IMAGE:
      case FormApp.ItemType.PAGE_BREAK:
      case FormApp.ItemType.SECTION_HEADER:
        // Item without a response - skip it
        break;

      case FormApp.ItemType.CHECKBOX:
      case FormApp.ItemType.DATE:
      case FormApp.ItemType.DATETIME:
      case FormApp.ItemType.DURATION:
      case FormApp.ItemType.GRID:
      case FormApp.ItemType.LIST:
      case FormApp.ItemType.MULTIPLE_CHOICE:
      case FormApp.ItemType.PARAGRAPH_TEXT:
      case FormApp.ItemType.SCALE:
      case FormApp.ItemType.TEXT:
      case FormApp.ItemType.TIME:
        // If item has a response, append it to array. If not, append blank.
        var itemTitle = items[i].getTitle();
        var type = items[i].getType();
        if (itemTitle === "") throw new Error( "Untitled item" );
        var itemResp = [];
        if (itemTitle in e.namedValues) {
          itemResp = e.namedValues[itemTitle];
        }
        resp.push( itemResp );
        break;

      default:
        Logger.log( "Unknown item type, index=" + items[i].getIndex() );
        break;
    }
  }
  e.values = resp;
  return e;  // For chaining
}

Toaster.gs

/**
 * "Class" Toaster
 *
 * From http://stackoverflow.com/a/33552904/1677912
 *
 * Wrapper for Spreadsheet.toast() with support for multi-line messages.
 *
 * Constructor:    new Toaster( message, title, timeoutSeconds );
 *
 * @param message         {String}    Toast message, possibly with newlines (`\n`)
 * @param title           {String}    (optional) Toast title
 * @param timeoutSeconds  {Number}    (optional) Duration of display, default 3s
 *
 * @returns {Toaster}                 Toaster instance.
 */
var Toaster = function(message, title, timeoutSeconds) {
  if (typeof message == 'undefined')
    throw new TypeError( "missing message" );
  
  this.message = this.parseMessage(message);
  this.title = title || '';
  this.timeoutSeconds = timeoutSeconds || 3;
  this.ss = SpreadsheetApp.getActiveSpreadsheet();
};

/**
 * Display Toaster message using previously set parameters.
 */
Toaster.prototype.display = function() {
  this.ss.toast(this.message,this.title,this.timeoutSeconds);
}

/**
 * This is where the magic happens. Prepares multi-line messages for display.
 *
 * @param {String} msg    Toast message, possibly with newlines (`\n`)
 *
 * @returns{String}       Message, ready to display.
 */
Toaster.prototype.parseMessage = function( msg ) {
  var maxWidth = 52;             // Approx. number of non-breaking spaces required to span toast popup.
  var knob = 1.85;               // Magical approx. ratio of avg char width : non-breaking space width
  var parsedMessage = '';
  
  var lines = msg.split('\n');   // Break lines at newline chars

  // Rebuild message with padded lines
  for (var i=0; i<lines.length; i++) {
    var len = lines[i].length;
    // Build padding string of non-breaking spaces sandwiched with normal spaces.
    var padding = ' '
                + len < (maxWidth / knob) ?
                  Array(Math.floor(maxWidth-(lines[i].length * knob))).join(String.fromCharCode(160)) + ' ' : '';
    parsedMessage += lines[i] + padding;
  }
  return parsedMessage;
}

VJoin.gs

/*
 * Vertically join the ranges from multiple sources into a new table
 * starting at the given anchor point. Values and formatting are copied.
 * From http://stackoverflow.com/a/15662782/1677912
 *
 * @param {a1Notation} anchorA1 Anchor for joined table.
 * @param {a1Notation} sources One or more source ranges.
 *
 * @customfunction
*/
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.")
    }
  }
}