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

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

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

Documentation, Support & Resources

• The SheetConverter library was created by David Bingham (Mogsdad).

• You can view the source code here, or in github.

• The latest jsDoc documentation.

• Discuss in the Google Apps Script Community on Google+.

• Ask questions on Stack Overflow using the google-apps-script tag.

• Issue reporting and tracking:

  • Report an issue

  • View the Issues Tracker.

• Contribute your own enhancements with a Pull Request on github.

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.