Create countdown timer

In this article:

Google Sheets has a couple of built-in functions — Today and Minus — which can be used to calculate the time difference between a date in the future and today.

This article explains how you can use these functions, along with our Spreadsheets integration, to create a countdown widget for your dashboard. As these functions don't update on their own by default, we've written a script for you to use that will ensure your countdown widget is kept up-to-date.

Setting up your countdown timer

In a new Google Sheet, follow these steps:

  1. In cell A1, write the label Today.
  2. In cell A2, write the formula =Today().
  3. In cell B1, write the name of the event e.g. Christmas.
  4. In cell B2, write the date of the event, e.g. 12/25/2015.

    Note

    The date needs to be added in the date format mm/dd/yyyy.

  5. In cell C1, write the label Days Until.
  6. In cell C2, write the formula =MINUS(B2, A2) (where B2 holds the value of your event and A2 holds the value of today's date).Countdown_setup.png
  7. Now switch to your Geckoboard dashboard and click Add widget.
  8. Select the Spreadsheets integration.
  9. Choose the Number widget.
  10. If you haven't already, connect your Google Sheets account to Geckoboard
  11. Select the Sheet that you saved the above calculation in.
  12. Enter the value of Days Until in the Number selection (in the above example, it is C2).
  13. Configure the other widget options.
  14. Disable the Comparison Visualization and adjust the Title.
  15. Click Add to dashboard to add your widget to your dashboard.
  16. And that's it! Your widget should look something like this: Number_widget_result.png

Making your countdown timer update automatically

Unfortunately, the Google Sheets functions don't automatically update the countdown. The only time the functions update the data is when the Google Sheet is opened. This isn't particularly helpful so we've written a script that updates the data in the Google Sheet every hour, even when the Google Sheet is closed.

Part 1: Write a script

  1. In your Google Sheet, click on the Tools menu and select the option Script Editor.
  2. A Google Apps Script window will appear. Click the Blank Project option.Setup_script_blank_project.png
  3. Replace the default function with the following script:

    Important note

    Make sure you follow the aforementioned instructions to the letter, otherwise cells in the Sheet might not match the ones in the script and your countdown will not update automatically.

    function generateNumber() {
     SpreadsheetApp.getActiveSheet().getRange('a2').setValue('=today()');
     SpreadsheetApp.getActiveSheet().getRange('c2').setValue('=MINUS(b2, a2)');
    }
  4. Save your function and give it a name.

Part 2: Set up a trigger

  1. From inside the Script Editor, click on the Edit menu and select the option Current project's triggers.
  2. In the window that appears, click the link No triggers set up. Click here to add one now.
  3. This will trigger create the trigger for your function. Use the default options provided.Trigger_setup.png
  4. Click Save.
  5. You'll be prompted to authorize your app. Click on Continue  and then Accept. More information on Google Apps authorization can be found here.Authorize_app_continue.pngauthorize_app_accept.png
  6. You can now close the Script Editor.

You're done!

Now that you've written the script and set the trigger running, the countdown on your Google Sheet (i.e. the today() and minus() functions in your Google Sheet) will refresh every hour. This will continue to refresh even when your Google Sheet is closed.

Your Geckoboard widget doesn't require any changes to reflect the addition of this script and trigger; it'll refresh every 15 minutes to check for and fetch new data from your Google Sheet, just as it always has. The difference now is that your Google Sheet will automatically have new data in it!

Note

We've found that occasionally, about a few times a week, a server error will occur on Google's end and the script will fail to run in that particular try. By default, you'll receive an email to notify you that this has happened.

You can simply ignore the errors considering that the script runs every hour each day which totals to 168 times each week, and so a few times a week is nothing to be concerned about.Google_server_error.png

Was this article helpful?

Awesome! 👍  Thanks so much for your feedback!

Sorry about that! Start a conversation now.