Hello everyone. I’ve recently started learning project management and I’m currently using Google Sheets to manage some small projects at work. One challenge I’m facing is finding an effective Google Sheets template that helps with: Project tracking, Task logging and Creating clear summary updates for management. If anyone has advice, tools or free Google Sheets templates they’ve used and would recommend, I’d really appreciate the help. Happy Christmas Eve 🎄

For our company we use one Google Sheets file for all our project management. I'm sharing here as a walkthrough because there's too many unanswered questions in r/projectmanegement, and because I want to start a discussion and see how it's different from what you do.

We are an international team with ~20 people. We pay $12 per month for access to G Suite and then we have a share folder which we share with all other people. So effectively this is less than $1/user/month.

This is a quick view for each task. (Maybe other people call these projects.) Our rule is that all active tasks must have weekly status updates. We have instruction files for each one and there is a link. Other hidden columns off the right include Status Update Text. Here are the other tabs:

• Work log -- people log hours here, this is how the HOURS on the Tasks tab will calculate, our rule is that these must match submitted time sheets

• Outreach tasks -- some additional details we track for content marketing projects

• Workers -- a list of each worker, their Slack identifier and other data

• Calendar -- each column is a team member, each row is a date, people mark what days they are working

We use the above collected data in some useful ways. First we suck this whole file into our MySQL database hourly. (See on GitHub google-sheets-etl, we open sourced this.) We have all kinds of exception reports and performance reports. If you forget to update the status on an active task then you and your manager will get a message on Slack. If your task is overdue it will show up on our intranet tickets list. If you are scheduled for vacation today, your face is grayed out on the TV in my office.

Our biggest pain point was just a normal failing of Google Sheets: you can't lock down formatting or data validation for a cell. We use formulas, conditional formatting and more to make this work. If somebody types a date into the wrong cell and then copies it to the date column then bam our formatting and everything is now broken on that cell. We work around this by using protected cells in most places -- and more exception reports.

I have looked at other project management tools. First, they want everyone on my team to have a separate account login -- it is hard enough to get my team to login to Gmail. Then they want to bring me from $12/mo to $300 or more. Lastly, they want to lock up my data so I can't pull it every hour and use it elsewhere. Every one of them has a stupid animation to explain how their product works without actually showing the product. Google Sheets on the other hand is mostly free, or price does not go up when I add people, is easy to understand, everyone is already logged in and it connects with my intranet. For me it is just lightyears ahead of all SaaS out there.

First of all, I want to say, I very much dislike this. I did everything I could to avoid being one of those silly PMs who uses Excel/Gsheets for everything, but I am at a company that has implemented their PM tools so poorly (no integrations, everything locked down, on-prem versions of cloud installs that run like dial-up, any changes require 48-hour IT ticket request, saving intermittently discards your work) that the only way to move quickly and reliably is to just work out of Google.

That being said, I find myself in a place where I need to just make a spreadsheet work for everything. It needs to be my documentation, my risk log, my stakeholder list, my recordings database, status summary, etc. all in a single "dashboard".

My biggest priorities:

• Minimize manual work required to keep things updated

• Keep my work transparent and easily understood by viewers

• Integrate or link to other people's relevant docs whenever possible

Anyone have tips for making this work better? I desire your wisdom.