Google Sheet Template for agile scrum projects reporting automations
Table of Contents
Why we are sharing this Template?
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 fernando.lopez@evenytes.com. We are open to include changes in the Google Sheet if necessary!
What it’s included in this template
- Highly customizable
- Include your sprint details (Sprint Names, Dates, etc)
- Add your own categories for Priority columns, Release, Status, etc
- Autonumeric task ID
- Auto Archive completed tasks
- Automatic Reports embedded
- Automatic System columns
- Created By & Created Date
- Last Modified By & Last Modified Date
- Customizable Email notifications
- Customize email templates
- Configure when notifications are sent
- When task is in concrete state
- When task is assigned to someone
- Long Tasks can be described in Google Docs
How automations works?
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.
System Columns
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:
- Email Notifications
- Track actions in the Audit log Sheet
- Track change in System Columns (Created By, Last Modified By)
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
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:
Task Description Document
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
Configuration Options for Task Description 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.
I like it, How to start using the Google Sheet Template?
- Request your copy of the Google Sheet Template here
- You will receive an email with the link. Click in Copy Scrum Google Sheet Template (Note: You can create as many copies as you want)
- Review and configure properly Config Sheet based on your needs
- Finally activate the script, in the SCRUM menu. (Follow the steps shown in the video) NOTE: You have to do this steps for each copy of the file.