Development of a Master Dashboard in Google Sheets (App Script) for data aggregation from 40+ tables

Hello.
I have experience implementing a similar automation system in Google Sheets + Apps Script.

I propose the following architecture:
• centralized data collection through Apps Script with batch-fetch (without IMPORTRANGE)
• processing arrays in memory and batch writing to the "Database" tab
• generating a unique key (SourceID + RowNumber) to prevent duplication
• a separate "Settings" tab for managing the list of tables and scaling without changing the code
• time-driven trigger + manual refresh button
… • analytical dashboard (Pivot Tables + slicers) with filtering by period, Team Lead, and editor
With a volume of ~250–300 new rows/month, the Apps Script limits (6 minutes) are not critical provided that batch processing is optimized.

We can discuss the details of the update (trigger frequency, access, report structure); for additional information, please contact me privately.

Show original

Translate

Good day, thank you for your response to my comment.
I am ready to implement your task and complete the following stages:
1) A script in AppsScript that will upload data from tables to one tab "Database".
2) A Settings tab where links to the tables and the date of the last update will be listed.
3) A time-based trigger that will update the information every 30 minutes + a separate button for manual update.
4) A report tab where you will be able to analyze the received information.

I will be happy to collaborate.

Show original

Translate

Good day

I have significant experience in developing solutions on Google Apps Script.

Regarding your task - there are technical limitations on execution time (timeout), so it is necessary to assess the volume of data and structure. After that, it will be possible to propose an optimal implementation and understand how to better organize the script's work.

Show original

Translate

Hello, Andriy!

You are absolutely right about `IMPORTRANGE` — in 40+ files, this function simply paralyzes the work of your Master file.

I specialize in building complex databases and analytical dashboards in the Google ecosystem. There is one hidden danger in your task: the execution time limit of Google Apps Script (6 minutes). If the script simply opens 40-50 files one after another, it will not have enough time to execute and will throw an error. Most freelancers forget about this.

How I will architecturally solve your task:

1. Optimized Data Collection (Batch Fetching): I will write a script that will collect arrays of data from all 40+ files into memory (using `Sheets API` or optimized arrays), and only then will write them to the `Database` tab in one batch (per second). This guarantees speed and no limits.
… 2. Control Hub (Scalability): I will create a settings tab. This will be your control panel. Three columns: `Installer Name` | `Team Lead` | `File Link`. To add the 45th employee, you simply paste the link into this list — the script will automatically start fetching data from it. No intervention in the code.
3. Smart Database: The script will automatically add columns with the installer name and Team Lead to each extracted row. We will also set up smart clearing/overwriting to ensure that data is not duplicated during repeated runs.
4. Premium Dashboard Based on the collected `Database`, I will create a dashboard (Pivot Tables + Slicers). But I will do this not just as a "gray table," but will apply UI/UX design principles: corporate colors, clear hierarchy (revenue, number of projects), so that management enjoys working with it.

We can update the database with a "Sync" button + automatically every night/hour with a background trigger.

I have extensive experience in developing similar systems for businesses. Message me privately, and we will discuss the details and start working!

Show original

Translate

Good day! I have extensive experience working with Google Sheets and Google Apps Script, implementing automated reporting systems with data aggregation from dozens of files without using IMPORTRANGE. I suggest we move to private messages for a more detailed description of the terms of reference, working time, and cost.

Show original

Translate

Hello, Andriy!
The task is clear. You are absolutely right - using the IMPORTRANGE function for 40+ files will significantly slow down the main table's performance. This task is best and fastest solved through Google Apps Script.

Here’s how I propose to implement it:
- I will write a script that will go through all 40+ tables and collect data into one general database (tab Database).
- I will create a separate settings tab (Settings). When you have a new employee (41st or 45th table), you will simply add the link there, and the script will automatically start processing it. There will be no need to interfere with the code.
- Based on the collected data, I will set up an analytical dashboard where you can conveniently filter statistics by periods, teams, and employees.

I have experience working with Google Apps Script and building database architecture in Sheets.

Show original

Translate

Hello. I have developed CRM systems for accounting and so on using Google App Script V8. I will do it quickly and efficiently. Please contact me.

Show original

Translate