Change Document Ownership with Apps Script

My Google account is managing a lot of document creation at school. I set up a lot of autoCrat projects to create docs alongside several utility scripts that pump out Docs on a regular basis.

The problem is, I don't want to be the owner of all of these documents. Using some Apps Script, I can set a trigger to automatically set a new document owner so the files are out of my account.

Notes

We use autoCrat for document creation, which has a consistent spreadsheet structure. When setting up the form to create documents, make sure you do the following:

  1. When autoCrat is set up, set the Merged Doc ID column in line 6 of the script.
  2. Include the user email address in the form response. Set the column ID with the email in line 7 of the script.

There's a check in the loop that makes sure the emailCol actually gives an email address. If it's not a valid email address, the row will be skipped. This shouldn't cause some rows to complete and others to fail because the entire column is checked.

You can run the script manually from the script editor and it will loop the sheet, setting the doc owner as the person who submitted the form. I set it to run daily with a trigger so I don't have to manage these long-running tasks.

This doesn't have to be used with autoCrat, either. All it needs is the ID of a document and the email address of the person to set as the owner. As long as you have that information, you can set this to run and help keep your Drive from becoming everyone's filing cabinet.

DocuTube Dev Update

Preview and choose an embed method in the popup window.

Some quick notes of my progress on updating DocuTube in the Docs Addon store:

Search by Default

  • Search videos in the addon popup window
  • Video, channel, and playlists can be specified in the search types
  • Results are paginated 15 videos at a time

Preview and Embed

Preview and choose an embed method in the popup window.

With this update, you'll be able to determine what kind of embed you'd like to make. If you choose text, it defaults to the title of the video, but you can also type in custom text ("Watch this video") and it will be linked automatically.

Known Issues

One of the major problems I'm going to face is the YouTube Data API quota. Applications are restricted, by default, to 10,000 'units' of data used per day from the API. Each call to the API has a cost that needs to be managed.

Right now, each pagination step is using the YouTube API to pull data down, which uses some of the allotted data. To make sure the app doesn't run out of resources, I'm probably going to implement some kind of simple cacheing, either in the script itself or in the browser's sessionStorage or localStorage cache.

This came up because I hit my quota limit while testing. Now, I'm calling a lot of videos to make sure it's all working - way more than a normal user would during normal use (preseumably). But, it's still a concern because the quota can get used up very quickly if I don't include some kind of cache mechanism.


The featured image is a screenshot from DocuTube. It is featuring Paul Andersen of Bozemanscience on YouTube.

Copy Notes Between Google Sheets

If you work in big systems, sometimes you come across a situation where you want to share a single tab of a Google Sheet with someone else rather than the master copy. An easy way is to just make a snapshot copy of that tab and share a new sheet with them. A more advanced method is to use a parent/child relationship and some Sheets cell formulas to share an always-up-to-date copy of the master data. The problem with that (there's always a problem, isn't there?) is that it only copies the data, none of the notes or any other information that might be on the master sheet.

In this post, I'm going to give an example of a Google Apps Script that can be used to copy notes from a master parent sheet to a child spreadsheet. If you want to make a copy of a folder with working examples, ask and thou shalt receive.

The Basic Setup

For this example, we have a master Google Sheet with several rows of data organized by location, like this:

The sample master spreadsheet

We want to share child sheets tied to schools A, B, and C with only their relevant data. This first part is done with query and importrange in the following formula in cell A1 of the child spreadsheet.

=query(importrange("masterSheetURL","Sheet1:A:E"),"SELECT * WHERE Col1 CONTAINS 'A'",1)

Query selects an entire row where Column 1 contains the string 'A'. If this were a real situation, the building name would be the imported data, which looks like this:

Imported data into a child spreadsheet using QUERY.

I prefer query because I don't have to select a specific range - it will look at the entire sheet for that data. If you don't want to import notes, this works really well. If you do want to import notes, make sure data is grouped together because you need to determine some offsets when writing to the child sheets. Create a second spreadsheet in masterthat has the following structure:

Helper sheet in master with links to the child sheets

where column A is the building (or location or other selector) and the URL to the child sheet you want to update. Column C will become very important as it holds the offset data for writing notes to the child sheet.

The Script

Copying notes can only be done with the .getNotes() Google Apps Script method. This script looks at the sheet and uses a couple of loops to build arrays to post to the child sheets. The first challenge is to set the offsets. Notice location B in the master sheet is rows six through eight, while on the child sheet it is rows two through four. Without setting an offset, our notes would be written into the wrong rows, and that's no fun.

After running that script, your child sheet will update the offset of each building in Column 3 of the helper sheet in the master copy. I also added an onChange trigger to this function so it runs when rows are inserted anywhere in the sheet.

Now, you're ready to copy notes from one sheet to another.

This script will loop the master spreadsheet and look for a building name it recognizes. If there's a match, it will open the child sheet and set the notes for each row using the offset provided in the previous step. To be honest, this isn't the most elegant solution, but hey, it works.

Just in case of catastrophe, here's another little utility script you can use to clear all notes from the child sheets. This was particularly helpful when I had my offset calculations off by a row.

This is a manual process - there is no edit or change event you can hook into when a note is added or deleted. So, I wrapped all of this into an onOpen simple trigger to add a custom menu.


There are definitely improvements that can be made. Here are all the files as a gist so you can clone, copy/paste, and hack away at your own sheets.

Event Manager Web App

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.

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.


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

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