Mr. Hay's Technology: November 2023

If you've ever wanted to have a Google Sheet up the screen together with a countdown timer, for example if you have a pivot table leaderboard for a hackathon, you can use Google Apps Script.

From your Google Sheet, click on the Extensions menu and choose Apps Script to open the script editor.

Paste in the following code into the editor:

function onOpen(e) {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Timer')
    .addItem('Countdown Timer in Sidebar', 'showSidebar')
    .addToUi();
}
 
function showSidebar() {
  var html = HtmlService.createHtmlOutputFromFile('countdown');
  html.setTitle('Countdown Timer');
  SpreadsheetApp.getUi().showSidebar(html);
}

Then click the + button near the top left to add a new file, and choose HTML. Type countdown as the file name, then paste the following code into the editor:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <div id="buttons">
      <button id="thirtyMinutes" onclick="thirtyMinutes()">30 minutes</button><br>
      <button id="fifteenMinutes" onclick="fifteenMinutes()">15 minutes</button><br>
      <button id="tenMinutes" onclick="tenMinutes()">10 minutes</button><br>
      <button id="twoMinutes" onclick="twoMinutes()">2 minutes</button><br>
      <button id="oneMinute" onclick="oneMinute()">1 minute</button><br>
    </div>
    <div id="countdown" style="text-align:center;font-size:50px"></div>
    <script>
      function thirtyMinutes() {startCountdown(30);}
      function fifteenMinutes() {startCountdown(15);}
      function tenMinutes() {startCountdown(10);}
      function twoMinutes() {startCountdown(2);}
      function oneMinute() {startCountdown(1);}
 
      function startCountdown(minutes) {
        document.getElementById("buttons").style.display = "none"; // hide the buttons div
        let count = parseInt(minutes) * 60;
        if (!count > 0) {
          count = 60;
        }
        let displayTime = "0:00";
        const timer = setInterval(function() {
          count--;
          displayTime = Math.floor(count / 60) + ":" + ("0" + count % 60).slice(-2);
          document.getElementById("countdown").innerHTML = displayTime;
          if (count < 60) {
            document.getElementById("countdown").style.color = "red";
          }
          if (count === 0) {
            clearInterval(timer);
            document.getElementById("countdown").innerHTML = "0";
            //alert("Time's up!");
          }
          if (count < 0) {
            clearInterval(timer);
            document.getElementById("countdown").innerHTML = "0";
          }
        }, 1000);
      }
    </script>
  </body>
</html>

You can also edit the code to add or remove buttons, and change the style of the buttons and text.

Then everything should be set up. The next time you load that Google Sheet there will be a custom menu called Timer that will show a sidebar with your new timer in it.

Mr. Hay's Technology

Monday, November 27, 2023

Countdown Timer in Google Sheets