Some links to bookmark:
I’ve used many of the templates already for various things, but one of the easier ones is the auto Slideshow. This usage case came up when I was asked to somehow create a slideshow using submissions from a Google Form for our Staff R.O.A.R. Awards. In a nutshell, teachers nominate each other on a monthly basis for being “Reliable, Organized, Attitude, or Risk-Taker.” These submissions are collected through a Google Form, which populates a spreadsheet. Using the Awesome Table slideshow template, I used some fancy formulas to pull in the submissions automatically and then auto create the “award” slide. The slideshow is embedded on our MercerNet site (Google Site) and auto scrolls every 6 seconds (this can be adjusted).
So let’s look at some screenshots of how the process works:
- Teachers submit via a Google Form.
- Spreadsheet w/ submissions:
- Using the Awesome Table template, I import that data into a separate tab (the first tab is what creates the slideshow, so don’t mess with it yet!) using the importrange formula.
- In Column G on this sheet, I used the concatenate formula to combine Columns D & E. Column G is what I then imported back to the Awesome Table tab that I said don’t mess with….YET. 🙂
- On the “Data” tab, I used an array formula in Column A to pull in the nominees name (Column C) on the “Imported Tab” on this same spreadsheet. In Column B, I used the array formula again to pull in the “blurb” about the nominee (Column G) from the “Imported Tab“. In Column C, I put in the URL for the award image, which I uploaded into CMS (process not included in this post). These array formulas are basically looking at the various columns and matching certain aspects if a certain criteria is met (i.e. a nominee’s name shows up, which prompts the sheet to pull in the blurb, which then prompts the sheet to pull in the URL of the Award image. Auto-magically, Awesome Tables pulls that all together into the slideshow.
- The end result is this auto scrolling slideshow, which includes the Staff R.O.A.R. Award image, along w/ the nominee’s name and their blurb.
Yes, I know I made it seem easier than it really is, but it truly is easy….and I’m happy to help you if you’re interested in setting something like this up at your school.