Hi Reader,

Welcome to the Google Sheets Tips newsletter #393, your Monday morning espresso, in spreadsheet form!

⚽️ We were bitten by World Cup fever in my house, watching matches together in the evenings. What a fantastic tournament, with drama in spades. Of course, I'm disappointed England didn't reach the final, but the I thoroughly enjoyed the ding-dong 6-4 third place playoff game against France.

🚴‍♂️ But, imho, there's an even better sporting spectacle happening in France at the moment: the Tour de France! Admittedly, it's more exciting if you're a cyclist yourself. There's a lot of team work and tactics going on in the background. What sometimes looks like a long line of riders grinding slowly and painfully up a mountain is really modern gladiators battling it out until there's one left standing. Along with the colorful motorcade and drama that surrounds every race, it's the greatest sporting spectacle on the planet imho ;) I love it!

In today's tip, we're vibe coding an activity dashboard.

Paying Workspace accounts have an Activity Dashboard tool in Sheets that tells you who's using the Sheet, on which days, etc. But it's not available for consumers.

So today, we'll build our own with the help of Gemini! We'll build an Apps Script version that replicates most of the same features. Find it below...

➜ News

I.
NotebookLM is being renamed to Gemini Notebook, to reflect the broader position within the Google AI ecosystem. There's no changes to the tool itself. This rolls out automatically over the next few weeks. I'll update my Gemini Mastery course in due course.

Read more here >>

➜ Sheets Tip #393: Activity Dashboard

Did you know that Google Sheets on paid Workspace plans come with a built-in activity dashboard? You can find out who is viewing the Sheet, how many times, how many comments there are, etc.

Unfortunately, this feature isn't available on consumer Google accounts. Instead, you can build one for yourself using Gemini and Apps Script. Previously this might have taken a day or two to build, or require hiring a professional developer. So it was probably a case of the juice not being worth the squeeze. Now however, the calculation is totally different. Copy the prompt below and follow the steps and you can build one in under 5 minutes!

Let's see both in action:

📌 Workspace Accounts: Built-in Activity Dashboard

Access the dashboard through the menu: Tools > Activity Dashboard

For Google Workspace account users, the Activity Dashboard shows you who the viewers of your Sheet are, viewer and comment trends over time, the sharing history, and privacy settings (where you control your view history).

For example:

Notes:

1) You’ll only see activity from people in your organization or the people you see in Share > Advanced.

📌 Consumer Accounts: Vibe Code An Activity Dashboard

The activity dashboard is not available for consumer (free) accounts.

Instead, let's use Gemini to build a custom one for us.

Phase 1: Get the Code

Copy the prompt underneath these steps.
Enter the prompt into Gemini.

Phase 2: Add to Google Sheets

Open your Google Sheet and go to Extensions > Apps Script.
In the existing Code.gs file: Delete any default code in the editor, copy Gemini's Code.gs code, and paste it here.
Create a new index.html file:
Click the + icon (Add a file) next to Files -> select HTML -> name it index
In this new index.html file, delete any default code and paste in Gemini's index.html code.
Click the Save icon (floppy disk) at the top.

Phase 3: Authorize & Launch

Return to your Google Sheet and refresh the page.
Click the new Analytics menu at the top, then select View Dashboard.
When prompted, authorize the script:
- Click Continue > select your Google account.
- Click Advanced > Go to Untitled project (unsafe).
- Click Allow.
Click Analytics > View Dashboard again to use your new tracker.

The Prompt

# Goal

Create a functional, bug-free "Activity Dashboard" modal popup (800x600px) for a personal Google Sheet that mimics the official Google Workspace Activity Dashboard.

# Critical Technical Fixes (To Prevent Infinite Loading/Spinner Freezes)

1. Chart Library Execution Sequencing:

- The frontend HTML must wait for `google.charts.load` to fully resolve *before* invoking the backend data fetch (`google.script.run`).

2. Bulletproof Comment Fetching (No Silent Crashes):

- Wrap the `DriveApp` comment retrieval inside a strict try-catch block.

- Crucially, test permission availability *before* looping through comments. If `DriveApp` throws an error or is blocked by missing project permissions, catch it immediately, log a warning, and fallback to returning an empty array `[]` for comments so that the rest of the dashboard (Viewers & Viewer Trend) still completely loads.

3. Empty Log Safety:

- The backend must gracefully handle a brand new or empty "SYS_ActivityLog" sheet without crashing (return empty default structures instead of null or undefined).

4. Global Error Visibility:

- Always chain `.withFailureHandler(err => ...)` on the frontend `google.script.run` call. If any backend error occurs, instantly hide the loading spinner and display the explicit error message text inside a red notification banner on the UI.

# Requirements

1. Trigger & Data Logging:

- On open, log the timestamp and current user's email (fallback to "Anonymous Viewer") to a hidden sheet named "SYS_ActivityLog". Create/initialize headers if the sheet does not exist.

- Add a custom menu "Analytics" > "View Dashboard" to open the modal.

2. Data Processing (Last 14 Days):

- Parse "SYS_ActivityLog" to aggregate daily viewer counts and compile a unique user list.

- Safely fetch sheet comment metrics using `DriveApp.getFileById().getComments()`.

3. Google Material UI Design:

- Layout: Clean sidebar navigation with three clickable tabs: "Viewers" (Default active view), "Viewer trend", and "Comment trend".

- "Viewers" View: Displays a vertical list of unique users with their last seen timestamp and a color-coded circular avatar containing the user's first initial (generated dynamically via CSS/JS from their email).

- "Viewer trend" & "Comment trend" Views: Display a "Total Count" metric card and a standard Google Column Chart mapping data over the last 14 days.

4. Privacy Notice:

- Include a small footer notice: "Charts do not include anyone who has opted out or accessed the file anonymously."

# Output

Provide a single `Code.gs` backend script and a single `index.html` frontend file ready to deploy. Ensure all asynchronous operations are defensively designed to guarantee data delivery.

Fixing Errors

You might run the prompt above and get code that works perfectly on the first try.

But you might also run into an error, and that's ok. Simply describe what's happening and ask Gemini to fix the code. For example, one of my attempts wouldn't load properly. This prompt fixed the issue:

When I tried this code, it got stuck on the loading spinner and would not show any actual data in the dashboard. Please fix this issue.

If you enjoyed this newsletter, please forward it to a friend who might enjoy it.

Have a great week!

Cheers,
Ben

P.S. A trippy moment for this weather presenter.

Google Sheets Tips

Sheets Tip 393: Vibe code your own Sheet activity dashboard (if you can't access the built-in one)