The Form/Sheet architecture is more dynamic than you may think, once you crack open the lid on Google Scripts.

I’ve long seen the creative things Martin Hawksey and Tom Woodward (and plenty of others) have done with Google Scripts. It’s been on a list of “one day I will learn some of this” for, well years.

The thing that breaks it open is a pressing need.

Here is how I learned to do some scripts to (a) use the WordPress API to dynamically populate some cells in a Google Spreadsheet; and (b) how to script a way to use these results to dynamically update text and menus in Google Forms.

Maybe it ends up with your rectilinear cells and fixed questions more like

Ultra modern glass walled building appears almost submerged in water, bright green light glows out from it. Surreal!
Wikimedia Commons photo by DAVID ILIFF. License: CC BY-SA 3.0

Having done a relatively small bit, I am rather encouraged to do more, but also thinking that of there ever was a change to teach basic programming / scripting basics I might just lean to using Google Scripts because it can be used on interfaces and tools people are familiar with (it’s really just JavaScript with Google specific functions built in).

The Need (or Desire)

I just wrote up some of the ideas behind and the building of the Ontario Extend Toolkit site. Creating the entries and finding examples to add to a tool entry, and just trying to come up with all the tools that should be there, ends up being a fair chunk of work.

But Ontario Extend is a network, why paint the fence when there’s plenty of educators out there with excellent brushes?

So I thought it would be worthy to create a form to collect wither suggestions for new tools to add to the toolkit, or to collect examples of the tool in use or helpful resources to list for tools.

To do this, we’d form fillers to identify the tool a resource or example is for OR we’d want them to suggest which group to associate a tool with. Heck, we should just list all the tools in the kit, so they know entries exist.

But who wants to remember to update a form whenever something is added to the site? This is TECHNOLOGY, can’t we string some together?

I was fairly sure that I had read mention that Google Form elements could be dynamically updated by a Google Script, and among the gazillions of search results, Waffle Byte’s Google Script – Create a Drop-Down List from Spreadsheet Column made it look very doable. I also came across in the Google Script documentation reference to functions and examples of modifying the description text of a header field, which means I could update the form generate description to list all the tools.

For getting information from the WordPress site to a spreadsheet the Badly Wired post (wow did I pick great name blogs in this project) Linking WordPress to a Spreadsheet using WP REST API and Google Sheets scripts got me most of the way there.

WordPress API to Google Spreadsheet

What I knew I would need from the WordPress site was:

  • Name and maybe link for all Custom Post types in the site (the Tools)
  • Name of all Custom Taxonomy terms for tools (the one that associates a tool with a “family” or type of general function)

These are not necessarily in the standard WordPress API data, but I did not have to bust out any code to extend the API. That’s because the settings for the Custom Post Type UI have features to add their data to the WordPress API via the Show in REST API setting (fortunately the default is TRUE).

But it takes some digging to find the right URLs for these endpoints. For the list of tools, the API URL uses the slug name for the custom post type

https://toolkit.ecampusontario.ca/wp-json/wp/v2/tool

This gets all kind of data, it helps to put it into something like JSON Pretty Print to make it more readable. However, this provides the tools in reverse chronological order, like blog posts, we’d prefer it in alphabetical order. That is done by sending a few more parameters:

https://toolkit.ecampusontario.ca/wp-json/wp/v2/tool?orderby=title&order=asc

Ah, but we only get the first 20 items, for now we add one more parameter to get the maximum we cna in one call, 100 (I will have to add some logic when we get more than 100 tools)

https://toolkit.ecampusontario.ca/wp-json/wp/v2/tool?orderby=title&order=asc&per_page=100

We got a ton of data, though what we need is simple the title and we will maybe get the link to the tool

JSON data for one tool, arrows show where the link is stored "link:..." and the title (in "title: { "rendered"":)

Now for getting the information needed to make a menu of tool categories we use the taxonomy I created called “family” – the REST API url is

https://toolkit.ecampusontario.ca/wp-json/wp/v2/family

These already come in alphabetically, and examining the JSON I can see the string we need to list it is stored in "name": the url as "link": and as a bonus, the API returns a value for the number of tools that use a term, stored in "count":.

This is what we need to put into some spreadsheet scripts.

Script Time

A basic Google form was first created (made pretty later) and set up to store results in a spreadsheet. We get to the script playground by opening the Script Editor under the Tools menu.

The first step is to create a menu for this spreadsheet that can be used to manually trigger an update. I see these used in Martin Hawksey’s Twitter TAGS sheets.

Now our spreadsheet has a Toolkit menu and inder a Get Tools item. It does nothing. We need an updateTools function. What follows was mostly lifted and tweaked from the ones shared in Linking WordPress to a Spreadsheet using WP REST API and Google Sheets scripts .

It is going to look for two sheets, one named Toolkit and one named Families. I already made them. This script that is called by the menu merely gets a reference for this sheets and sends them on to other functions.

The getToolkit function first calls a function setupToolSheet(ss) to reset the sheet with headings, then calls another function getToolkitData(ss,per_page) to actually do the API work. See how we just pass the sheet references as ss. That’s how real programmers do this, is it?

This is the function that does the API call. We set up the URL it needs to get the data. We march through the data in a loop, and add the results we want from the JSON mapped above to the columns we want (name and link).

Save the script, go to the spreadsheet, and run the menu function to Get Tools. It goes from blank to this in a few seconds. That’s rather nifty.

The scripts to get the family names are almost the same.

And when run, we get the data on the Tool Families:

I likely will not use the count and links, but good to know we can grab it.

That’s what we have to get the data from WordPress into a spreadsheet. Now, we fiddle with modifying the Form scripts.

In The Google Form

In a way, the Google Form is just a series of objects you can get values from and for some change, with scripts. This is a bit of a multipurpose form, so I set up the first question as a radio button to conditionally branch to a section:

First form question is "What can you add to the Toolkit?" with options of 
"I have a suggestion for a new tool"
and "I have an example of a use of or a resource for an existing tool"

I also want to be able to list in the Form description, all tools already in the kit, so people know whether it’s worth doing the first option. The script I have in place takes the data from the spreadsheet, and puts it into the form description. The list indicated by the arrow is dynamically updated.

Add to Toolkit form header with a welcome message, and a red arrow pointing to a list of tools already in the kit.

So the branch for adding a new tool asks for the tool’s name, web address, a description why should this tool be in the Extend Toolkit, and a place for any other information. But the menu of interest is one that asks to pick a category (aka family) that would be best to put the tool:

This menu is updated by the scripts below from the data above. AMAZING! IS ANYONE IMPRESSED? (I am).

In the other kind of submission, an example of how the tool is used or a resource for a tool, we have another menu that is dynamically populated from the tool name data.

When all working, we have a system where updates to the web site will be reflected automatically reflected in the menu.

How do we do this magic?

More script editing.

The Scripting of the Form

We will modify the Toolkit menu in the spreadsheet for the form data to have a second menu item to update the form.

So the updateForm script kicks things off. This one has a lot of parts, so in sections…

We set up an ID for the form (it comes from the URL) and with some ferreting from a browser inspector look at the form (with the tips from the post by Waffle Bytes) to get the IDs for the tools menu and the family menus.

The next lines get references for the spreadsheets with the data we want get.

Then we use this to get an array of the tool names.

And then we do something similar to get an array of the Family names (I had fun naming a variable familyValues)

This is all we need to modify the form. For the tool list at the top, we have to rewrite the entire description, so we have the opening paragraph, the start of the second one, and we append the array of tool names joined by a comma as a string. We can ten directly replace the content of form.description:

And the menus can be directly populated with the appropriate arrays:

Now we can update everything by using the Toolkit menu in the spreadsheet and selecting Update Form.

This is good.

But not good enough.

We can be more automated.

Trigger this Script

The last close of this loop is setting the process to be automated. I did this by visiting my Google Script Triggers area and creating two new ones. I did this because the updateForm scripts are dependent on the tools and families being updated, so I made a trigger first that calls the updateTools on a daily basis, between midnight and 1:00am:

And the second one is just like this, except it calls updateForm between 1am and 2am.

I can of course, manually run this at any time.

That’s All Folks

I was pretty pleased getting this running with hardly any real Google scripts experience, working from the idea in my head and 2 blog posts. I’m pretty excited to do more scripting, this is just popping the lid off of the possibility box.

Give the form a try from it’s embedded spot in the toolkit, suggest a tool or send us an example of something done with the small collection we have in the Toolkit now. And marvel knowing how those menus got populated.


Featured Image: Wikimedia Commons image Soviet apartment.jpg shared under a Creative Commons CC-BY license.

If this kind of stuff has value, please support me by tossing a one time PayPal kibble or monthly on Patreon
Profile Picture for cogdog
An early 90s builder of the web and blogging Alan Levine barks at CogDogBlog.com on web storytelling (#ds106 #4life), photography, bending WordPress, and serendipity in the infinite internet river. He thinks it's weird to write about himself in the third person.

Comments

    1. Certainly possible. It was easy to work through that site’s example (except I failed to get it to page through multiple calls to get more than 100 results).

      The other way could be add to plugin api endpoints to return the data, so instead of shipping raw data, it’s just the stuff the plugin calculates

Leave a Reply

Your email address will not be published. Required fields are marked *