Table of Contents
After evaluating several options, we didn’t find any solution that match our simple needs for Agile project management. Either the solutions were too costly or too complex for our requirements. We decided to create a Google Sheet with some automations that helps us to track the activities and we discovered that it was really helpful!.
We really think this simple solution can help a lot of people who is looking for an simple alternative for small / medium Scrum project management.
We really like honest feedback, if you want to share with us your thoughts, you can send me an email to email@example.com. We are open to include changes in the Google Sheet if necessary!
Most of those automations are done with advanced Google Sheet features like Queries and formulas and there are a few that requires Google Apps Script. If you decide to use the template, you will have a copy of the Google Apps Script code in your copy of the template. If you want to can improve yourself.
Created Date, Created By, Last Modified Date, Last modified by & Completed are completed automatically using a simple Google Apps Script and taking benefit of the onEdit event.
The first time you run the template, the Sheet will ask for your email. This information will be used for the System column as well as for email notifications.
See here how System columns are completed each time there is a change in the sheet:
NOTE: The first time a new user tries to edit any cell, the Google Sheet is going to ask him / her to introduce the email. This is used for the following proposes:
This information is stored in a Google Apps Script feature called properties service. In the last version of the Template we have introduced an option in the SCRUM menu to allow users to change this value in case they introduce something wrong the first time they asked for it.
Reporting module has been done without any Google Apps Script using only formulas. In order to make this module work, you need to define properly the Sprint dates in the config sheet otherwise the formulas will not have data to generate the charts.
Once everything is properly setup in the config sheet, you navigate to the Query Report sheet and charts should be automatically generated for you as shown here:
Often you will find limitations to describe a task in one single cell, to avoid this limitation we have created an Google Apps Script which creates a Google Doc based on a template you define and creates an a link with the task line you are located.
You can see here how it works the automatic creation of task detailed document based on templates
You can create your own Google Doc to describe your task as you wish. To do it, you need to go to the Config Sheet and change the following paramenters:
Task Folder ID: Provide the Google Drive ID Folder. This is the folder where the task documents will be created. In case this parameter is empty, the tasks will be created in the same folder where the file is located. The ID looks like 194iAKdH6iR3RAU5rbTRX2ZCL7EjchRNFkDJ1Fr6Er6c and it’s extracted from the URL https://drive.google.com/drive/folders/194iAKdH6iR3RAU5rbTRX2ZCL7EjchRNFkDJ1Fr6Er6c
Template Doc: Provide a Google Doc ID. This is the ID of the document that will be copied each time you want to have a detailed description for a task.