Best way to refresh a Google Sheets + Apps Script dashboard more frequently

Google Workspace Developers Q&A
,
1

Hi everyone,

I’m working on an internal dashboard built with Google Sheets, Google Apps Script, and an HtmlService web app.

The current flow is like this:

  • users submit data through Google Forms / field forms

  • the data goes into Google Sheets

  • Apps Script cleans and transforms the data

  • several summary sheets are generated for KPI, risk analytics, reports, and GIS/map data

  • the HtmlService dashboard reads prepared data using google.script.run

The dashboard works, but as the dataset grows, the refresh process is becoming heavier. When I run several build/update functions in one flow, it sometimes gets close to the Apps Script execution time limit.

I’m trying to understand the best architecture for a safer near-real-time setup.

What I want:

  • new form/sheet data should appear in the dashboard as soon as reasonably possible

  • heavy calculations should not be triggered from the frontend

  • the dashboard should stay mostly read-only and read prepared snapshots

  • refresh jobs should not overlap

  • if possible, only new or changed rows should be processed

For this kind of Google Sheets + Apps Script dashboard, what pattern would you recommend?

Should I use a combination of:

  • installable onFormSubmit triggers

  • time-driven triggers

  • incremental/delta processing

  • LockService

  • CacheService

  • PropertiesService for refresh state

  • separate phases for heavy calculations

Also, what is the best way to show the dashboard user that data is currently being refreshed, without making the dashboard itself start heavy backend processing?

Any practical architecture advice or examples from similar Apps Script projects would be helpful.

1 Like