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:
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.
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.
Another common task is to filter the displayed rows. We can do this with our macro:
{{googlespreadsheet(286755fad04869ca523320acce0dc6a4, SELECT * WHERE A='Tavish')}}
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)}}
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.
Here's the Google Document macro in action:
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
- Google Spreadsheet Plugin.
- Wiki Extensions Plugin This is a plugin that was a helpful example to work from when writing our own.