Send automated messages from Google Sheets to a screen in the office

Do you have a list of fun facts about employees, jokes or inspirational quotes by Steve Jobs you’d like to send to a dashboard in your office automatically at certain daily or weekly intervals? Excellent! You’re at the right place. In this post, I’ll show how I set up Google Sheets to send a random Brian Eno and Peter Schmidt’s Oblique Strategy to my Gleebeam dashboard every morning. This way I always had something fun and inspirational to start my day with! 🌞

Please note that you will have to use the Google Sheets script editor to add some JavaScript code to make the automation work. If you don’t have any experience with JavaScript, don’t worry because I’ll provide all the code you’ll need!

Without further ado, let’s set it up!

1. Create a Gleebeam notification template

In the Gleebeam template editor, create a design for the notifications you’d like to contain the random messages.

Make sure to define the message placeholder field as dynamic. Give it an appropriate name – I’ve named mine “message”.

Save and exit the template editor and click on the newly created template to view its automation data. You’ll need it in step 3!

💡You can add a sound effect to your notifications in the sound menu of the editor sidebar.

Gleebeam notification template automation data

2. Set up messages in Google Sheets

Create a new Google Sheets spreadsheet and enter the messages you’d like to send in column A. Note that you can have them in any column, but then you’ll need to change one extra line of code in step 3 to query that column.

Give your sheet a name. I’ve named mine “Cards”. You’ll need the name in step 3.

Google Sheets spreadsheet with messages pasted in

Your spreadsheet is done! Now go to Tools → Script editor to set up the script that will pick a random value in the column where you have the messages and send it to Gleebeam.

Going to the script editor from the Google Sheets document

3. Set up the Google Sheets script

Here’s all of the code we’ll need to set it up. Select all of it and paste it in the Script editor.

function getRandomMessage() {
  // STEP 1: Replace "Cards" with your sheet name in quotes
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Cards");
  
  if (sheet) {
    // If your messages are not in column A, query the column where they are in the sheet
    // E.g. if your messages are in column B starting from row 2, replace "A2:A" with "B2:B"
    const messageRange = sheet.getRange("A2:A");
    const messageValues = messageRange.getValues().map(function(row) { return row[0] });
    const messages = messageValues.filter(String);
    const randomMessageIndex = Math.floor(Math.random() * messages.length);    

    return messages[randomMessageIndex];
  }
  else return null;
}

function triggerGleebeamNotification(message) {
  // STEP 2: Copy your Gleebeam automation data here
  const payload = {
    "notification_id": "9MxMSvN51YxaBQn5ek0Q",
    "message": message
  };

  const options = {
    "method" : "post",
    "payload" : payload
  };
  
  UrlFetchApp.fetch("https://api.gleebeam.com/v1/trigger", options);
}

function sendRandomMessage() {
  const randomMessage = getRandomMessage();
  
  if (randomMessage && randomMessage.length > 0) {
    triggerGleebeamNotification(randomMessage);
  }
}

Your Script editor should now look like mine in the screenshot below. Here you need to do two to four things depending on how you set up your Gleebeam template and your Google Sheets document.

  1. On line 3, replace “Cards” with the name you gave to your sheet in quotes.
  2. On line 21, replace “<your notification’s id>” with your notification template ID from step 1 also in quotes.
  3. On line 22, replace “message” (on the left side of the colon) with the name you gave your dynamic text field in step 1 of this tutorial. You don’t have to do anything here if, like me, you named it “message”.
  4. If in step 2 of this tutorial you didn’t enter your messages in column A, you’ll also have to change which column is queried by the script on line 8.
Google Script editor with the Gleebeam notification code pasted in

To test that everything is working:

  1. Save the script by clicking the floppy disk button,
  2. Select sendRandomMessage in the drop-down menu of the Script editor’s toolbar,
  3. Click the Run button in the toolbar. You will now be asked to give the Script editor permission to access your Google Sheets document and you might even encounter a security warning if you’re using Chrome. Proceed through the steps to grant the necessary permissions to the Script editor (note that this does not grant any permissions to Gleebeam to access the spreadsheet).
Google Script editor requesting authorization for the Gleebeam random message notification script

After you run the script, you should see the notification appear on your Gleebeam dashboard. This means the test was successful and you can proceed!

4. Automate messages

To get a random message sent automatically every day at a certain time, you’ll have to set up a trigger for your Google Sheets project. Go to EditCurrent project’s triggers to set this up.

Setting up the Google Sheets project's triggers

You’ll be taken to the G Suite Developer Hub. Add a new trigger.

The G Suite Developer Hub

Set the trigger up to run function sendRandomMessage. Set Event source to Time-driven and choose your desired timer interval and time. I’ve set up mine to be triggered every day between 8 AM and 9 AM. Save the trigger when you’re done.

Setting up a new trigger in the G Suite Developer Hub

You’re all set!

Well done. You should now be receiving random automated messages from Google Sheets in Gleebeam. Feel free to update the messages in your Google Sheets document however you wish – they should still be sent to Gleebeam at your specified time interval.


This post was inspired by a tutorial I read in the Zapier community. You’ll need to set up an account to read it.

If you’d like to contact me about anything related to this, please send me an email to [email protected] or message me on Twitter.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s