CODE - Google Sheets Integration - Automatic Processing of Events/Listings

  • 6
  • Article
  • Updated 1 year ago
  • (Edited)
I am providing this code to the community in case others see value in it. 

Use Case
We have multiple clients who have us build them event boards for their venues. The event board usually shows events happening today, and in some cases they may have hundreds of them, similar to an Airport that has hundreds of flights coming in. 

The implementation allows for our clients to pre-populate their events in the Google sheet as far in advance as they could possibly want to go. 

The Problem
The issue then becomes, how do you get only todays events on the board?

Our original solution was using a =sort(filter..) formula to grab just today's events from one sheet to the other. However, when Google updated sheets earlier this year they seriously broke stuff. Even with spreadsheet setting set to "Every minute and on change" for how often the sheet updates the today() value, it didn't ever work properly. Even with the sheet open, unless you specifically made a change to the source sheet, the destination sheet never updated. 

This became more of a problem when a client asked us to filter events to show only 10 events at a time and automatically remove events that have expired so that the board only has the most recent and relevant entries. 

The Solution
This is where I figured I'd finally try learn Google's scripting language to find a solution. Google scripts are executed like linux cron jobs, or windows scheduled events - they happen in the background regardless of the status of the sheet. 

By creating a script, I was able to control the trigger, setting it to every 5 minutes so that the script would execute and update the sheets regularly.

Implementation
To implement my solution, first you need two sheets. 
  1. Source Sheet
  2. Destination Sheet
On the source sheet, I ALWAYS leave column A blank, and always reserve the first row for a title, because I think it looks cleaner. Headers start in B2 and span the 2nd row as far as needed. Data starts on B3, and continues as far down as needed. 

In our spreadsheet, we track both the start and end time of an event. Our columns are:
  1. Date
  2. Start Time
  3. End Time
  4. Event
  5. Location

The Destination sheet is hidden and contains no data.  This sheet is different, where it starts with headers in A1 and data in A2. I don't really care what the destination sheet looks like, because it's never meant to be seen and doesn't need to be as pretty. 

I created a new script (Tools Menu -> Script Editor), which can be found at the link below.

Link to the Code. http://paste.ofcode.org/THs6yaHYzXcRX9YPMvAnYK

The spreadsheet gets published to the web, but I only publish the Destination Sheet, that way some one cannot accidently select the Source Sheet with all the forward looking events in the Rise Gadget. 

In the rise gadget, I specify the spreadsheet name and choose the destination sheet, and set headers to auto or 1 accordingly. 

Once your script is created, click on the button that looks like a clock. This is your trigger settings.  Under run, choose the function name init. In the next few columns, choose "Time Driven" then how frequent you want it to run. 

Important Notes
  1. The script only cares about the first three columns for filtering and sorting capabilities.If you dont care about filtering by event start/end time, comment out or delete lines 104 - 147 of the script in the link below, and replace all of it with a simple copyEvent=1;
  2. We sort the destination sheet by start time, then by end time (line 224). Change the column numbers to match your sorting
  3. The code doesnt care about number of columns in your source sheet, as long as the columns used in the filtering routine are of correct data types. Whether you have 5 columns, or 10, it doesn't care. It'll copy all columns
  4. My code is ugly, but it works for our purposes and those of our clients.
  5. You may, at one or more points, authorize Google to run the script on your behalf and allow the script to access your google docs. Just accept everything.

Made with love in Winnipeg, Canada. Feel free to modify the code as you see fit. If you make any improvements, please share them with me, and update this thread. 
Photo of Kevin

Kevin

  • 390 Points 250 badge 2x thumb

Posted 3 years ago

  • 6
Photo of Blake Freeman

Blake Freeman, Official Rep

  • 36,116 Points 20k badge 2x thumb
Kevin,

This is awesome! Thank you so much for the input. I've changed this post type from Idea to Article.
Photo of HSuarez

HSuarez

  • 11,896 Points 10k badge 2x thumb
Thanks for sharing Kevin!
Photo of Wayne Williams

Wayne Williams

  • 110 Points 100 badge 2x thumb
Is there a new link to this script? The link is 404'd
Thanks!
Photo of Wendi Borden

Wendi Borden, Champion

  • 6,028 Points 5k badge 2x thumb
Following.. the link is 404 for me as well. I was also curious if you'd mind sharing some kind of test presentation to show your usage in action? Thanks for sharing!