← Home

Automated Spreadsheet Dashboards

Role

Data Analyst

Keywords

Google Sheets

Analytics Dashboard

Year

2025

← Home Automated Spreadsheet Dashboards Table of Contents About Project 1 Introduction Process Project 2 Introduction Process Project 3 Project 4 Project 5 Other Projects Let’s Work Together

About

Project 1 Files

Submit BI Project 1 - Truong Thi Hong Mai - Google Drive

https://drive.google.com/drive/folders/10e7-8Vf0btaHaci6JQ07Cg-a7Cz76AaR?usp=drive_link

Project 2 Files

Submit BI Project 2 - Truong Thi Hong Mai - Google Drive

https://drive.google.com/drive/folders/1MbfuKE58w2vOU9nfOA4VugwMQ5wZeQLG?usp=drive_link

Project 3 Files

Submit BI Project 3 - Truong Thi Hong Mai - Google Drive

https://drive.google.com/drive/folders/1AoqOJmofGFHC8SH69yLFTPBcm1BXYL00?usp=drive_link

Project 4 Files

Submit BI Project 4 - Truong Thi Hong Mai - Google Drive

https://drive.google.com/drive/folders/1_1-DXaeOxcj4iE_psYD7aN-OIBrclVX4?usp=drive_link

Project 5 Files

Submit BI Project 5 - Truong Thi Hong Mai - Google Drive

https://drive.google.com/drive/folders/12VfCEFACmgK0Dw1-Tr6obyqDh_10Bvqx?usp=drive_link

Project Review

Every organization can leverage the use of data analytics and automation to help gain insights and work of efficiently.

However, not every company has the budget, infrastructure, or specialized personnel required to implement complex data platforms or enterprise analytics tools. In many cases, Google Sheets can serve as a practical and cost-effective solution for small- to medium-scale data analysis, especially in areas such as marketing, lead tracking, and community operations.

This project demonstrates how advanced Google Sheets techniques can be used to build lightweight yet effective analytics and automation systems for:

Automated Leads Management

Marketing Campaign Analysis

Community Engagement Analysis

It also implements different types of Dashboards:

Operational Dashboard: Displays operational metrics, scope is within a department.

Tactical Dashboard: Focuses on detailed analysis to support performance evaluation, and short- to medium-term planning.

Strategic Dashboard: Presents high-level KPIs and long-term growth trends.

Tech Stack

Google Sheets

Project 1

Introduction

In a sales department, leads need to be distributed among sales representatives so they can follow up with potential customers. However, manually assigning leads is time-consuming, difficult to maintain at scale, and prone to human error.

This project uses Google Sheets and Google Apps Script to automate the lead allocation and sales tracking workflow.

The system is designed to:

Automatically distribute leads among sales representatives

Generate personal lead-tracking dashboards for each salesperson

Create a centralized management dashboard for team leaders and managers

Process

First, the 1 - Lead Sale Divider spreadsheet automatically assigns a salesperson to each incoming lead based on predefined allocation logic.

Next, the auto_create_sales_sheets Google Apps Script automatically generates individual lead tracker spreadsheets for each salesperson.

Script to automatically create new Lead Tracker Spreadsheets

Each salesperson receives a personal dashboard to monitor assigned leads, track progress, and manage follow-up activities.

Lead Tracker Dashboard for the first employee “Hong Mai” (me)

Finally, the Team Lead Management spreadsheet consolidates data from all salesperson trackers into a centralized dashboard, allowing managers to monitor team-wide performance, lead progress, and sales activity in one place.