Event Manager Web App

Event Manager Web App thumbnail

Adding to the list of things to blog so I don’t forget about them in the future, I built an event manager web app for prom.

Sales

Writing data to/from a spreadsheet through a webapp is faster and easier on the sales team than having them edit a spreadsheet directly. Names are pulled by matching ID numbers in a lookup database and populated in the form automatically.

When a ticket is sold, the name is added to a running list on the right side. The app also ensures no students purchase tickets more than once by comparing current sales with the submitted ID number and returns an error if the person already exists in the sales record.

Ticket sale interaction click for a larger image

Manager

Students are checked into and out of the event by the door staff. A full list is returned to the app that can then be filtered by ID number by the staff. Clicking on a number populates a card showing the student information for verification. They can then be timestamped into and out of the event by the staff.

Manager workflow click for a larger image

This worked well because each of our students has a unique six-digit ID number we can use to look up their information. It could be quickly repurposed to assign an ID number at registration rather than assign a name and have the manager page function the same.

Full code for the project is on this GitHub Gist.


Featurend image: Conference Queue flickr photo by Bearfaced shared under a Creative Commons (BY-NC-ND) license

Dynamic Data Validation with Apps Script

Dynamic Data Validation with Apps Script thumbnail

My coworker, Kat, needed to set up meetings with our 13 elementary schools. Appointment slots would have been a lot of clicking back and forth on calendar events plus her dates were variable and subject to change. She set up a spreadsheet with each school on a row. The challenge was to set up data validation rules that would automatically update after a school made a selection.

cracks knuckles

The first thing to do was to list out all the possible dates. Updating a spreadsheet is easier than changing a validation object in Apps Script. I can also loop the sheet and make structural adjustments on the fly.

Once that list was done, we wrote a function to set validation on the sheet using the date.

Now that validation is set, we needed to listen for changes to the spreadsheet. The onOpen function has an event object which holds information about where and how the sheet was edited. This let us limit the function to changes in columns two or three only. If a cell was edited (ie the validation dropdown was changed by the user) the function gathers event data and passes it along to the workhorse.

Now we can update the validation range. Loop through the entire range of dates backwards. Once a matching row is found, delete the row. Looping backwards prevents your index from changing. We also remove validation from the edited cell at this point so it doesn’t show an error for having a value outside the validation list.

Because validation is pulled from a range, deleting the row (and the value) removes it as an option in all of the other cells! Once the sheet is finished and everyone has a date, you could even add a short loop to push dates to your Google calendar as all day events.

Here’s the full script if you want to add it to your sheet.


Featuredimage is Array of Rivets flickr photo by jim.choate59 shared under a Creative Commons (BY-NC-ND) license

Goolge Apps Script Knowledgebase Suite

This is another entry into the “fun, but probably not feasible” folder.

A colleague and I have been trying to show the importance of getting a knowledgebase set up for our teachers. It’s been a back-burner issue for a long time because:

  • we’ve never had one, so there isn’t really a pain point, and,
  • we’re trying to roll out 3,500 iPads to 9-12th grades and there are some small projects to get done with that process.

There’s a little bit of hyperbole there, but in essence, it would be nice, but not now.

Google released Hangouts Chat for GSuite users. The main reason was to compete with enterprise chat apps like Slack (but you won’t hear Google saying that directly). Hangouts Chat allows you to set up discussions with teams that are threaded based on rooms. They’ve also added an automation API so users can find, or write, bots to discussions. I got Chat turned on for our domain just to see what it was like. I was able to use Google’s code lab guide to get a simple bot up and running. It pulled open tasks for a user from our project management suite and I was able to use existing code to get the data into the bot. In all, it took me about two hours to have a working prototype.

I decided to build a helpdesk chat bot which could pull answers to common technical problems our teachers face. That ballooned into a web app to pair with the bot because…why not.

Apps Script Knowledgebase

I’ll go through some of the code, but all of it is on GitHub. It’s written in Apps Script because again, a lot of the code was preexisting so I didn’t have to start from scratch. Most of it is modified from standalone functions I have running in separate functions on other sheets. The real challenge in this task was to wrangle the user input into something usable by the script.

The database

First things first - we needed a database. I threw together a spreadsheet with several data fields that hold different types of information. I started with our platforms as a high level filter and then added some tags. The user can search for all platform-related entries or narrow them down with keyword tags.

Screenshot of the database sheet click for a larger view

There are two additional sheets to the template: one to authenticate users and one to build a list of district platforms. Authenticated users have a button to add new articles into the database from the web app (see below). Editing a really big spreadsheet, especially when typing multi-line, HTML formatted entries, gets really cumbersome, so I wanted a way to do that through the website. Additionally, standardizing the platform options helps the user narrow searches right from the start so they can find information quickly.

The bot

Setting up the bot is covered by Google’s Code Lab, so I’m not going into detail here. The main piece of the bot is that it listens for a mention by a user, finds the information, and returns a card-formatted response.

Screenshot of a card returned to a user's question

Our thinking for this app is that someone wants a quick video on how to do something, so only videos are returned. The main challenge is getting meaningful information from the user mention in order to match database entries.

The regular expression looks for any of the keywords mentioned by the user in a string. This was easier than trying to match array values and provides consistent results. Right now, a user can send simple requests like “Google” or “Canvas” to get general results. They can also include some refining search terms, like “Google gmail” or “Canvas assignment” to narrow searches down. If there are no videos that match the search, they’re pushed over to the web app which has articles that do not include video results. More work could be done to refine how the script processes user input to improve the experience.

The script takes the string an creates a regular expression which is then used to match a string built from columns one and two of the spreadsheet. If a result is matched, the YouTube URL is returned in an array.

Once the YouTube videos are grabbed from the sheet, they’re broken into video ID strings and put through the YouTube API to get the video title and thumbnail. The bot builds a card to return to the user with suggested videos. If there aren’t any videos available, the user is given a link to the web app where they can find written articles on their topic.

The web app

We also threw together a web app to display information that isn’t in video form. The idea being that these are longer articles that have step-by-step instructions and are meant to be used in conjunction with the chat application. Lookup can be done by either passing query parameters in the URL to the app or using the sidebar form to search for keywords within a given topic.

Web app view of the knowledgebase click for a larger view

Web apps also allow you to append queries to the published URL out of the box. If a query string is included, the app creates an object that can be passed through your script functions to build a display from a template dynamically. For instance, navigating to

https://script.google.com/.../exec?platform=ios&tags=airplay

takes you to this:

Web app loaded from a URL parameter string click for a larger view

When a video isn’t returned, we’ll pass back the user string to jump right to relevant answers.

Challenges

Writing logic to manipulate and understand user input that can vary wildly is very difficult. I’m relying on simple prompts (platform + search keys) to help return a video on every search. As the database grows, results will also improve. I’d like to spend more time refining the bot script to better process and use the chat messages. The regular expression approach is pretty brute-strength, but it works for most searches.

A bigger problem I discovered is that hosting a web app and a bot in the same script file causes things to break. Google makes it easy to deploy a bot from an Apps Script project. Hosting a web app is also quite easy and uses a similar ID string. What I think happens is that those ID numbers are conflicted when both are hosted from the same project. The bot’s onMessage listener wouldn’t register an event with a web app published. One solution is to host the bot in an unbound script that points to the same spreadsheet via the key. Another (probably better, in the long run) solution would be to write a web view that is hosted on a school server and access the Google Apps Script API to display database queries. More on that in this long demo.


In the knowledge economy, we need a Netflix of education - TechCrunch flickr photo by DigitalMajority shared under a Creative Commons (BY-NC-SA) license

Slide Tweeter Update 4

I thought I was ready for some beta testing of the Slide Tweeter AddOn. Unfortunately, I’ve run into a snag with authenticating some of the code.

When you install and Addon from Google, it’s in something called AuthMode.NONE which significantly limits access to certain data. This is a good thing because you don’t want Addons running through your account changing things the minute you install it. Anyways, once it’s installed, you can then prompt the user to enable the Addon, which gives it access to all the necessary permissions.

I’m working on moving permissions around so it installs and adds a menu successfully before activating the Addon. It’s turning into more of a trick than I thought it would.

More updates to come…

Link Google Forms to Bitly Automatically

If you have a bit.ly account, you can get a public API token which can be used to create shortcodes. This is really handy in my situation, where I’m creating a ton of feedback spreadsheets (another monster post on that later). Using a small code snippet, you can create a feedback form, throw in some code, and have it display as a short URL and QR code.

If you’re starting from scratch, create a template form and spreadsheet. When you need to make a feedback form, use File > Make a copy on the spreadsheet to copy over the code.

Otherwise, you can make a copy of this blank template to get started (code is already inserted). If you’re going to make your own, be sure you have a form linked. If there is no form on your sheet, you’ll get an error.

The code

The full source code is below. Note that there are two files: one called Code.gs and one called popup.html. If you’re copying/pasting, you need to create an HTML file (File > New > Html file in the script editor) and call it ‘popup’.

In action

click for larger view

The wheels of the web keep spinning…

Cog in the wheel flickr photo by WickedVT shared under a Creative Commons (BY-NC-ND) license

Slides Tweeter Update 2

A short update to my post about tweeting Google Slides presentations after some work this weekend.

– Working AddOn menu prototyped.

– Check if a user is logged into Twitter on launch. If not, log them in.

– The user can append a hashtag to each slide. This is stored in each presentation until the user changes the input.

– The custom player now launches in a pared-down popup rather than a new tab.

– Scrolling enabled/disabled.

– Each slide is posted on advance (slide 1 posted when you move to slide 2) to buy a little time (still laggy).

click for full-size video

to do:

  1. Improve the connect to Twitter flow.

  2. Work to improve the tweet posting time.

  3. Confirmation popup when a tweet is posted successfully.

  4. Error handling when a tweet fails to post for some reason.

Tweeting Google Slides Automatically

An app called Keynote Tweet has been around (in various working and non-working states) since the late 2000’s and let users auto-tweet images of their Keynote slides during a presentation to a hashtag or stream. Google released the Slides API this year and one of the API methods allows you to get a thumbnail of the image which can then be sent to other applications. You can see an example of this in a slideshow now by going to View > HTML View. It opens a tab with slide images embedded in plain HTML formatting. Since we can now get the image, we can start to push them out to other platforms with Google Apps Script.

This post is going to be technical in nature and is really meant as a proof-of-concept. I’ll explain some of the shortcomings of this implementation in context. The code is broken up into several chunks and the entire source is posted to GitHub.

Setup

First, the Slides API has to be enabled in the Google Cloud Console. Once that’s done, getting the thumbnails is pretty easy.

Off the bat, the API doesn’t have event triggers like the Forms, Sheets, or Docs. I wanted each slide to be tweeted as the presentation advanced, so I needed a custom presentation view. To get this to work, I wrote up a web app presentation window served by Google’s HtmlService.

This simple HTML page requests and displays the slides from an array created by the backend. There are some controls that hide on the bottom of the screen and a position indicator in the top right. Hover the mouse and they’ll pop up for interaction.

Issue 1

  1. The initial page load for the web app varies depending on the size of the presentation. The request for slides on line 37 fires as soon as the document loads in the browser. The loading GIF is replaced by the slides when they’re returned.

  2. The slide thumbnails are returned as 1600×900 pixel PNGs, so they’re big, which increases load time. There is no way to specify the size of the image returned at this point.

Each slide is sent as an image on a tweet as they show is advanced and has posted class added to prevent multiple tweets of the same slide. The “previous” button does not trigger a tweet in the event you go backwards.

I used Martin Hawksey’s TwtrService library to connect my Twitter account. He has a detailed post on how to connect and use the library, so I’m not going to go through that here. This is also where the second major snag comes up.

Issue 2

Google recommends not using libraries in production code because they can negative impact on script runtime. This is especially apparent on the first slide in this script – it times out frequently (3 of 5 times?) and I’m not sure why. Subsequent slides come in between 20-50 seconds, which isn’t terrible, considering the image size being uploaded. But, if you’re a fast talker, this won’t be able to keep up unless some kind of queueing is implemented.

To do this without a library, the OAuth flow needs to be incorporated into the main script. It’s beyond my ability at the moment, so if you’d like to contribute that component and help this run as a standalone app, you can do submit a pull request on the GitHub repo.

Tweeting

Sending the tweet is actually a two-step process. First, the slide thumbnail is posted and then the media_id assigned is attached to the tweet. This is all done on the Google Apps Script side of the code to account for security considerations.

Google’s thumbnail is generated and hosted on their server, so I used the UrlFetchApp to request the content as a blob. This is serialized data that can be passed on to Twitter’s image hosting service.

Once the image is uploaded, we can take the returned media_id string and attach it to a tweet. The Twitter API object for a tweet has a number of options, but all I’m using is status (what you’re saying) and media_ids, which takes the image ID string from the upload.

Right now, the string is hard-coded into the script. This could be set via the Apps Script UI tools if this gets turned into an AddOn at some point if I can speed it up.

Issue 3

Twitter requires a high degree of authorization for posting. I tried implementing the OAuth flow without using a library to speed up performance, but I couldn’t get it to work. TwtrService stores the app credentials for the OAuth flow and has both an upload and post method that make the tweeting easy. But, performance varies for 20 seconds to as long as 300.

Conclusion

The app works, which was exciting to put together and see. It’s a function that would be great in a number of situations and implementation will only get better as the Slides API improves. I’d love to work with someone with more experience to speed the API calls up significantly by including all the necessary authentication in the main script rather than in a library. If you’d be willing to contribute, the source code is on GitHub.

If you’d like to play with it, you can either copy all the files from GitHub or copy and paste the separate embeds here into an empty project. Add postTweet and getThumbnails to the code below.

Mountain Bluebird flickr photo by Andrej Chudy shared under a Creative Commons (BY-NC-SA) license

Date Countdown in Sheets for Triggers

I have a Google Sheet which displays all upcoming PD in the district. It also tracks registrations for people through a web app. I’ve documented that in other places, so I want to focus on an easy method of calculating days until an event to use as a script trigger.

This started because teachers were looking for an automated email reminder a few days before the workshop so they didn’t forget to come. I’d rather they get a Calendar invitation when they register for the event, but I ran into some authentication snags, so that aspect is back burner for the time being. Currently, the sheet is using today’s date and the date of the workshop to trigger an email four days in advance.

Calculating the “days remaining” is pretty easy. The cell formula is:

=ARRAYFORMULA(IF(ISBLANK(B2:B),””,ROUNDDOWN(B2:B – NOW())))

There are several components of this:

ARRAYFORMULA applies formulas to a range of cells rather than a single cell. Saves me from having to copy the formula down to each new entry.

ISBLANK checks for data in a cell. Because it’s inside ARRAYFORMULA, it looks at the cell in the matching row. If it is blank, TRUE is returned.

ROUNDDOWN rounds a result to a whole integer. This is useful because the subtraction taking place inside the formula returns a large decimal. This makes it easier to test in the script.

NOW gives the date and time when the sheet is updated. Any time you make a change, NOW is calculated.

– The IF conditional keeps the sheet clean and wraps everything up. The syntax is, IF(_logical test_, _value if true_, _value if false_). So, this reads, “If the cell column B for this row is blank, show nothing. If it’s false (is not blank), calculate the difference between the PD date in column B and NOW.

The core of the function is the count down calculation. For instance, today is Friday, September 8. Subtracting it from a date in the future like Monday, September 11, returns a whole integer: 3. I can test for that integer (or any integer) in a simple script.

This is particularly helpful with timed triggers in scripts. I have a utility script wrapped in a conditional:

“`

if(date === 3) {

// do something here

}

“`

If the condition isn’t met in the script, nothing happens and I don’t get a failure email notification. This is also nice because if I want to adjust the timing, the trigger can stay the same (daily, for instance) without changing the codebase.