Google Sheets is primarily a static database tool. If you want to track exactly how many days, hours, and minutes are left until a major project deadline, you can easily use basic math (like subtracting today’s date from the deadline). However, the result is static. If the answer is “5 days, 4 hours,” it will stay exactly like that until you manually refresh the page or edit a cell to force Google to recalculate.
If you are displaying a Google Sheet on a large monitor in an office as a project dashboard, you want a live, ticking countdown timer that updates every single minute in real-time, creating a sense of urgency without anyone needing to touch the keyboard.
While Google Sheets does not have a native “Live Timer” function, you can build one in just a few minutes using a Google Apps Script and a time-driven trigger.
Step 1: Set Up Your Sheet
First, we need to designate where the deadline lives and where the live timer will output.
- Open your Google Sheet. Let’s assume you are using Sheet1.
- In cell A1, type the exact date and time of your deadline. Use a standard format, like:
12/31/2024 17:00:00(December 31st at 5:00 PM). - We will use cell B1 as the output for our live timer. You can make this cell’s font massive and bold so it is easily visible on a dashboard monitor.
Step 2: Write the Timer Script
- In the top menu bar, click Extensions > Apps Script.
- Delete any default code in the editor and paste the following JavaScript:
function updateCountdown() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
// Get the deadline from cell A1
var deadlineDate = sheet.getRange("A1").getValue();
// If A1 is empty or not a date, stop running
if (!(deadlineDate instanceof Date)) return;
// Calculate the difference between the deadline and right now
var now = new Date();
var diff = deadlineDate - now;
// If the deadline has passed, output a message and stop
if (diff <= 0) {
sheet.getRange("B1").setValue("DEADLINE REACHED!");
return;
}
// Convert the raw millisecond difference into Days, Hours, and Minutes
var days = Math.floor(diff / (1000 * 60 * 60 * 24));
var hours = Math.floor((diff % (1000 * 60 * 60 * 24)) / (1000 * 60 * 60));
var minutes = Math.floor((diff % (1000 * 60 * 60)) / (1000 * 60));
// Format the output string
var countdownString = days + "d " + hours + "h " + minutes + "m remaining";
// Write the live string to cell B1
sheet.getRange("B1").setValue(countdownString);
}Click the Save icon (the floppy disk).
Step 3: Create the “Heartbeat” Trigger
Right now, the code only runs if you manually click the “Run” button in the editor. We need to create a trigger that acts as a heartbeat, forcing the script to run every single minute automatically.
- On the far-left sidebar of the Apps Script editor, click the Triggers icon (the alarm clock).
- In the bottom right corner, click + Add Trigger.
- Set “Choose which function to run” to updateCountdown.
- Set “Select event source” to Time-driven.
- Set “Select type of time based trigger” to Minutes timer.
- Set “Select minute interval” to Every minute.
- Click Save. (Google will ask you to authorize the script; follow the prompts to grant permission).
The Result
Return to your Google Sheet. Sit back and watch. Exactly on the minute mark, cell B1 will magically update itself, ticking down from “5d 4h 32m” to “5d 4h 31m”, completely hands-free.