Back to Blog

Retrieving Comments from Google Spreadsheets

By Mobomo January 30, 2014

![cell comments](https://s3.amazonaws.com/uploads.hipchat.com/7195/313437/vNcnJWoKGDdSZfu/Ping.jpg)

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…

![cell comments](https://s3.amazonaws.com/uploads.hipchat.com/7195/31591/jv68nhW5fpfKO2Y/pingImage1.jpg)

The cell comments feature isn’t exposed by [Google Spreadsheet API v3](https://developers.google.com/google-apps/spreadsheets/#working_with_cell-based_feeds),
thus, retrieving these comments via Roo::Google in [Roo Gem](https://github.com/Empact/roo) became futile.

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!

![xlsx content](https://s3.amazonaws.com/uploads.hipchat.com/7195/31591/ORQQdDdxCIat5zo/pingImage2.jpg)

“`




cell note
testing comments -Ping Yu


“`

Initially, due to a slight variance between what Google generated and what Roo expected, Roo::Excelx reader in [Roo Gem](https://github.com/Empact/roo), couldn’t find any of the comments.

However, after we submitted a [PR here](https://github.com/Empact/roo/pull/95) we were able to retrieve the comments. You can use our [repo](https://github.com/intridea/roo) before the PR is merged.

Here is the code snippet for exporting the spreadsheet and retrieving the comments:

drive = GoogleDrive.login("username@gmail.com", "mypassword")

  path =
    Dir::Tmpname.make_tmpname("#{Rails.root}/tmp/raw", nil) + ".xlsx"

  # example google spreadsheet url
  # https://docs.google.com/spreadsheet/ccc?key=pz7XtlQC-PYx-jrVMJErTcg
  drive.spreadsheet_by_key("pz7XtlQC-PYx-jrVMJErTcg").export_as_file(path, "xls")

  Roo::Excelx.new(path, comment_xpath: './xmlns:text/xmlns:t').comments
  #[[129, 60, "New comment"], [156, 5, "commentsnt-Ping Yu"]]

Related articles:

  • GENERAL
  • GOOGLE SPREADSHEET
  • ROO
  • Mobomo, LLC Receives Re-Certification after Successful ISO Compliance Audit

    Mobomo, LLC Receives Re-Certification after Successful ISO Compliance Audit

  • Agile Project Management with Scrum

    Agile Project Management with Scrum