Google Sheets Automated Tracking and Reporting with a Database

I run into a lot of clients that need database management within Google Sheets. Primarily because it is free to use and multiple users are able to get in and work on the document at once. Also, you can keep certain tabs / ranges protected from any e-mails you want. Also, G sheets has some really powerful formulas and scripts that provide additionally functionality that Excel just doesn't have (still in 2023).

$45.00 USD

The template will be immediately available to access after purchase. Make sure to hit File>Make a copy when you open the sheet from the link so you get your own version that you own. Also, check out this useful Project Management Template.

database reports

So, I will still have some things going on in Excel, but with more and more clients working in Google Sheets, I will be focusing a bit of my work on that.

In respect to this template, it is a general template where you have a database, which can be populated from a Google form or manually by users. There are various columns that identify various attributes about each row (record). In the example in the video it is just salesmen that sell things and get time stamped. Then, there are descriptions about what is sold and an option to mark it as 'in progress' or 'complete'.

Based on that setup, I have put some of the most commonly structured reports into a few tabs. One is a current month and one is dynamic. What that means is they look at the dates and the condition fields and display data from the primary database that meets those conditions.

It is nice because you just have to build one "filter" formula, which is found in the sheet and then it will propagate all the data for each row if the conditions match.

The other feature of this Google Sheet is a script that looks at a column marked "in progress" or "complete". If it changes to "complete" then the entire row automatically moves into a new tab called "complete". That is really powerful when trying to manage records that will be moving from one stage to the next. Similar logic is used in this sales pipeline tracker built in Google Sheets.

Finally, I made a standard monthly report that is based on the amount earned per salesmen over time on the 'completed' tab only.

The structure of this is the value. You can apply the formulas and scripts to do all kinds of things.

To see the script that moves rows to another tab in this template, go to Tools>Script Editor

Check out another Google Sheet template for filtering a database (more advanced logic used).