Merged Cells and Formatted Text in SpreadsheetApp

Google Apps Script is a really nice way to interact with Google Docs programatically.  You can write add-ons or stand-alone scripts to read or modify documents and have access to a DOM, which is a much nicer way to do things than attempting to use a REST endpoint or similar. The only problem is that while the DOM/object model is reasonably complete, it isn’t totally so.  As of this writing there is no way, when dealing with spreadsheets, to determine if a cell is merged, or, more frustratingly, obtain the formatted contents of the cell.  We really needed this information recently, so we decided to parse it out of the HTML that you get when exporting the document.  This can be done in Apps Script itself.

You can see the gist here.

Basically we build up an associative array, where for a given cell index you can get back the row span, column span and formatted value.


parsedHtml['Sheet1:A1'] = {index: 'Sheet1:A1', text: '123,456', rowspan: 2, colspan: 2}

If there is no entry for a given cell index then that cell is part of a merged range or outside the data range.  The latter should be known beforehand.

NOTE: that this code requires underscore.js to work, you can read an article from Google about how to use it in apps script projects here.

Advertisements
Merged Cells and Formatted Text in SpreadsheetApp

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s