Introducing the Redmine Google Docs Plugin

You're in a meeting with a client, and you want to go through the specs for a big project. Each spec is a row in a spreadsheet, which is in Google Docs so the management team can edit it collaboratively throughout the meeting. You like the spreadsheet layout because it's well-organized, simple, easy to edit, and hides the distracting technical details from the client. Your development team needs to see this information too, but they prefer to use Redmine; it's a great place to dump gritty technical details.

You need to keep the developers in the loop, so you copy and paste the spreadsheet text into the issue after the meeting. But after a particularly long meeting, you forget to update Redmine, and the developers are now working off out-of-date documentation.

What you need is a Redmine plugin to synchronize your spreadsheet and your Redmine issues. We wrote about our solution in a previous post. Automatically updating an issue page with the changes saves us unnecessary meetings and confusion, so Alex Dergachev and I wrote a plugin called redmine_google_docs. This plugin allows embedding Google Spreadsheets and Google Documents in Redmine wiki pages and issues with simple macros.

Google Spreadsheet Macro

A macro is simply a code that you can place in the text of a Redmine page that will get rendered by Redmine in a special way. For example, {{toc}} will be rendered on a Redmine wiki page as a table of contents.

The Google Spreadsheet macro works similarly, but takes arguments that specify information about the spreadsheet you are embedding. Take a look at this diagram:

Plugin overview

Preparing the Spreadsheet on Google Docs

Before embedding a spreadsheet in Redmine, you must make sure that your team can view the spreadsheet. Google Docs lets you change the "Share" settings of a document using the Share menu in the top right of a document view. Use the Share menu to give your team viewing privileges before attempting to embed it. Otherwise, your team will only see an error message, instead of the spreadsheet.

If your spreadsheet is not publicly viewable, you must be logged into Google Docs in another browser tab to view it in Redmine. Otherwise, instead of a rendered Google spreadsheet, you will get an "access denied" error.

Using the Plugin

The simplest version of the Google Spreadsheet macro has a document key as its first argument. In this example, the document key is the string of characters inside the parentheses, 0ApF8ewDeRUY8dGxxTzdqWmdWSTNXNDdwZU44My13N0E.

{{googlespreadsheet(0ApF8ewDeRUY8dGxxTzdqWmdWSTNXNDdwZU44My13N0E)}}

Where do you get the document key from? The document key is part of the URL where you view the document. It will be a long string of letters and numbers and should not contain any symbols.

Getting the document key

Once you have the document key, you can insert it in the macro. Use this as a template:

{{googlespreadsheet(insert document key here)}}

Here it is in action, in an issue comment.

In the issue comment

Another common task is to filter the displayed rows. We can do this with our macro:

{{googlespreadsheet(286755fad04869ca523320acce0dc6a4, SELECT * WHERE A='Tavish')}}

Filtering displayed rows

We often set up our spreadsheets with the second column as the related issue number. We can query for a specific issue number like this:

{{googlespreadsheet(286755fad04869ca523320acce0dc6a4, SELECT * WHERE B='5790')}}

Or we can use the googleissue macro, which automatically does this on issue pages. Keep in mind that this will only select rows with the current issue's number in the second column.

{{googleissue(286755fad04869ca523320acce0dc6a4)}}

Issue query

Two-way Editing

Sometimes developers need to change a specification in the spreadsheet, but they don't want to log into Google Docs to do so. The good news is that you can enable two-way editing on the embedded spreadsheet by adding "/edit" to the document key used in the macro.

For example:

{{googlespreadsheet(286755fad04869ca523320acce0dc6a4/edit)}}

Be warned: it may be a good idea to force your team to edit the document on Google Docs, especially if the document is client-viewable. It is easy to accidentally modify an embedded spreadsheet.

Rostyslav Hulka pointed out in the comments that this doesn't actually work with spreadsheets. See the section below on two-way editing with documents. Thanks for the tip, Rostyslav!

Google Document Macro

While writing this blog post, I used a Redmine issue to track my progress and collect feedback from team members. Instead of just linking to the work-in-progress, I embedded the document itself in the issue. This way, Aran could read the post and comment on it in the same window.

We also use this approach for writing proposals. The proposal's outline is in Redmine and each section is given an issue with an embedded Google Doc. That way, the status of each section is tracked and assigned and even edited through Redmine.

Here's the macro itself. You get the document key from the URL of the document just like with Google Docs spreadsheets.

{{googledoc(194cXWd9mCPCUHR1ktGofJLJuu2Sg50coGR2lo8srEuI)}}

To use it, you need to first publish the document on Google Docs.

Google Doc publishing

Here's the Google Document macro in action:

Google Doc example

Two-way Editing

Sometimes developers need to edit a document, but they don't want to log into Google Docs to do so. The good news is that you can enable two-way editing on the embedded document by adding ", edit" to the macro argument.

For example:

{{googledoc(286755fad04869ca523320acce0dc6a4, edit)}}

Be warned: it may be a good idea to force your team to edit the document on Google Docs, especially if the document is client-viewable. It is easy to accidentally modify an embedded document.

Final Words

Keeping a team agile involves constant communication. As a project progresses, its integral that all team members remain up-to-date on project specifications. We find these Redmine plugins invaluable for keeping management and development on the same page — literally. If you have a similar technique, or if this solution is helpful to you, let us know in the comments!

Get the Google Spreadsheet plugin here.

Links

Comments

This sounds fantastic, but will it work with the new ChiliProject fork? Folks are switching over to that nowadays.

Hi!

Thanks for your work! It plugin is very useful!!

I only faced with one problem, it's editing spreadsheets. I created test spreadsheet which is public for everyone https://docs.google.com/spreadsheet/ccc?key=0ArqsWwuQPhGcdHdGTGZiZEJSblR... .

I checked {{googlespreadsheet(0ArqsWwuQPhGcdHdGTGZiZEJSblRVdF9XbHFwcDU3UFE)}} and it works great.

But when i put {{googlespreadsheet(0ArqsWwuQPhGcdHdGTGZiZEJSblRVdF9XbHFwcDU3UFE/edit)}} (how it described above) i got Access denied. I tried to found Google API where is described how to get editable spreadsheet but i didn't found :(

Generated link: https://spreadsheets.google.com/tq?key=0ArqsWwuQPhGcdHdGTGZiZEJSblRVdF9X...

Response: google.visualization.Query.setResponse({"version":"0.6","reqId":"0","status":"error","errors":[{"reason":"access_denied","message":"Access denied","detailed_message":"Access denied"}]});

May you suggest how to fix it? Or at least link on Google API where it described...

Thanks in advance!

Tried to check out the plugin:

rob@dev:~/git$ git clone https://github.com/tarmstrong/redmine_google_docs.git
Initialized empty Git repository in /home/rob/git/redmine_google_docs/.git/
error: The requested URL returned error: 403
warning: remote HEAD refers to nonexistent ref, unable to checkout.

@Rob: Try the following command.

git clone git://github.com/tarmstrong/redmine_google_docs.git

@Rostyslav: Looks like I got a bit mixed up. It's the documents that had two-way editing support. I just pushed a change to make this work properly. It's possible that this did work at some point, but they changed the API a little -- but it's more likely that this was just my mistake.

@Tavish: Thanks for your answer!

I found other issue, in google_docs_macros.rb is

if /^\w+$/.match(doc_key)
url = "https://docs.google.com/a/evolvingweb.ca/document/pub?id=#{doc_key}"
out = ""
else
raise "The Google document key must be alphanumeric."
en

But key can be not just alphanumeric, for example https://docs.google.com/document/pub?id=1EQHR-dHXvNF2-YPszPomf32z2FBuvp5... . Key can contain "-" characters.

I forked this plugin on github, maybe i can help with developing.

Cheers

That did it. Thanks. I have some small problems with a div overlap that prevents me from editing the comment I put the spreadsheet in. I'll look at the code and see if I can figure it out.

@Rob: I just pushed a change that should fix the div overlap problem. Let me know if you're still having that problem.

@Rostyslav: Thanks for the tip. If you send a pull request I'll definitely take a look!

Sounds great I was really intrigued by your original article about this and I'm going to let our IT know to see if we can get this going.

My only concern is that this steps out of the notifications system of redmine. It would be a real time saver when developing the spec and tasks. But once development is started you would have to be careful not to change specs out from under someone without making sure to notify them by making an update to the issue as well as the spreadsheet.

Of course that is just as true without this awesome linkage, since you would have to update redmine issues anyway every time you update the spreadsheet.
So really this would save me from that same error more often than cause any issues.
And I love anything that eliminates duplicate non-connected versions of the same information.

What has this got to do with Drupal and why is it tagged "Drupal Planet"?

@Hal: agreed, it would be nice to be notified when the spreadsheet changes. If it's important, you can always add a comment to that issue to remind a developer to take a closer look. I'll brainstorm ways to do automatic notifications (I'm open to ideas!)

@Kris: Valid question! People in the Drupal community voiced interest in the plugin (in the comments of our last blog post on the subject of agile development) so we posted this to Drupal Planet. The link is admittedly tenuous.

@Rostyslav: FYI, I pushed a change that validates document keys correctly.