Module 14 / 15

Your Personal Dashboard

Learn how to fetch data from your Sheet and show it on your blog.

Until now, we've sent data into the sheet. Today, we're doing the opposite. We’re going to ask the sheet for information and display it beautifully on your Blogger page!

What You Will Learn
  • How to count rows in a sheet using getLastRow().
  • How to send data back to Blogger using JSON.
  • How to update your blog screen without refreshing the page.
Who Is This For?

This is for the "Data Scientist" in training. If you want a counter on your blog that says "Total Visitors" or a "Latest Announcement" box that you can change just by typing in Google Sheets, this is your module!

Real Life Connection

Think of a Video Game Lobby.

Before you play, you see a screen showing your "High Score" and "Total Coins." That data isn't typed on the screen; it's being pulled from a database (the game's sheet). Today, you are building that exact screen for your blog!

What is JSON?

When the computer sends data over the internet, it likes to pack it into a small, neat box called JSON (JavaScript Object Notation).

Instead of sending a messy sheet, it sends a neat label like this:
{ "totalMessages": 42 }.
Your Blogger site sees this label and knows exactly what to display.

The Dashboard Loop
Blogger asks "How many?"
➜
Script counts 1, 2, 3...
➜
Blogger shows Total: 3!
The Counting Script

We use doGet because Blogger is "Getting" (reading) information from our script.

function doGet() {
  const sheet = SpreadsheetApp.getActiveSheet();
  // Count total rows (minus the header)
  const count = sheet.getLastRow() - 1;

  const result = { "total": count };

  // Send the JSON box back to Blogger
  return ContentService.createTextOutput(JSON.stringify(result))
    .setMimeType(ContentService.MimeType.JSON);
}
The "Full Dashboard" Brain

This code sends back the total count AND the message from the very last user!

function doGet() { const sheet = SpreadsheetApp.getActiveSheet(); const lastRow = sheet.getLastRow(); // Get info from the last row (Time, Name, Email, Msg) const latestMsg = sheet.getRange(lastRow, 4).getValue(); const data = { "count": lastRow - 1, "latest": latestMsg }; return ContentService.createTextOutput(JSON.stringify(data)) .setMimeType(ContentService.MimeType.JSON); }
Showing it on Blogger

After you Deploy your script, you use a special "Mini Script" on Blogger to show the data. It looks like this:

fetch(url)
  .then(res => res.json())
  .then(data => {
    document.getElementById('counter').innerText = data.count;
  });

Now, every time a reader opens your blog, they will see the Live Count of how many people have contacted you!

The Dashboard is Blank!
Click to fix common fetching errors

1. Empty Sheet: If your sheet has 0 rows (only the header), getLastRow() - 1 might be 0 or -1. Make sure you have at least one test entry!

2. MIME Type: If you forget the setMimeType line, Blogger won't know the data is JSON and it will treat it like a boring text file.

3. CORS: Don't worry about the word, but sometimes browsers block scripts. Deploy as Anyone to make sure your blog has permission to read it.

The "Latest News" Challenge

Create a second sheet tab called "News." Type a message in cell A1. Can you change your doGet to find that specific sheet and return the news message to Blogger?

Who Else Uses This?

YouTube Creators use this for their "Live Subscriber Counters." They have a dashboard that fetches their subscriber number from the database and updates the screen every second. You are using the exact same logic!

Module Summary

You have completed the full circle! You can Send data, Save data, and Fetch data. Your Blogger site is now a living, breathing application. Only one more module to go before you become a Graduate Master!

Status: Data Presenter 📊 | Level: Dash Master 🏎️

Type NEXT to unlock the FINAL module 🚀