Retrieving Comments from Google Spreadsheets

In a recent project, Intridea utilized Google Spreadsheet as a major data source, using Roo::Google in [Roo Gem](https://github.com/Empact/roo) to access the spreadsheet and [Google Drive Ruby Gem](https://github.com/gimite/google-drive-ruby) to communicate with [Google Spreadsheet API v3](https://developers.google.com/google-apps/spreadsheets/#working_with_cell-based_feeds).

It’s a great tool for data source management. However, when we attempted to access the individual cell comments, the export capabilities proved difficult…

After some googling though, we discovered this wasn’t an isolated incident.

One suggestion we found was utilizing cell notes; a predecessor to Google comments. However, while useful, cell notes lacks necessary commentator information. In addition, when generating Google’s html code, the spreadsheet malformats and had difficulty linking notes back to cells.

Below is an example of the html export. Notice most table cells are not closed.

.

1

.

2% [1]

Slide table

[1] cell note

After further research though, we discovered a solution; exporting the spreadsheet in XLSX format.

XLSX file is actually just a zip file, and when unzipped there are a series of files. One of these files being, comments1.xml; a file with all the XLSX content we’d been looking for!