SheetConverter


David Bingham, August 2014 

The SheetConverter 
Google Apps Script library provides functions that can retrieve the contents of a spreadsheet range in plain text or html, preserving the formatting that is present in the spreadsheet.

This library is at the heart of the Email Range Add-on for Google Sheets.

Example

With very few lines of code, you can reproduce what users see in your spreadsheets:

function example() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var range = ss.getActiveSheet().getDataRange();

  // Optional init, to ensure the spreadsheet config overrides the script's
  var conv = SheetConverter.init(ss.getSpreadsheetTimeZone(),
                                 ss.getSpreadsheetLocale());

  // Grab an array for formatted content
  var array = conv.convertRange(range);
  Logger.log(JSON.stringify(array));

  // Get a html table version, with all formatting
  var html = conv.convertRange2html(range);
  Logger.log(html);

  // Or a single cell, if you want!
  var cellText = SheetConverter.convertCell(1234.56, "0.000E+0");
  Logger.log(cellText); // "1.235E+3"
}


Installing the library

  1. In the script editor, click on Resources > Manage libraries

  2. A popup box opens. Insert the following project key in the textbox: Mo6Ljr7ZKrMeYO9mHqOdo9oxc-w7mEonb and click on the Select button next to it.

  3. Click on the box listing the different versions of the library. Select the newest version and click Save.



Documentation, Support & Resources


Change Log

Version 1, August 17 2014
First release

Version 2, August 18 2014
Significant reduction in redundant style info in HTML output (60 to 75%), and fix for a bug in .init() that lost custom timezone & locale data (and accessed Session service more than necessary).

Version 3, August 21 2014

Broadened date/time format support, thereby fixing a bug in month/minute evaluation. Thanks, Andrew S, for finding that!

Version 4, August 26 2014
Significantly rewrote padded decimal number format support to provide TEXT()-consistent rounding and padding of both whole and fractional parts of numbers. Added support for thousands separators.

Version 5, September 4 2014
Added elapsed time format support.