Making Google Forms and Spreadsheets a Bit Less Rigid Block Style

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.

function onOpen() {
  // setup a custom menu
  SpreadsheetApp.getUi() 
      .createMenu('Toolkit')
      .addItem('Get Tools', 'updateTools')
      .addToUi();
}

function onOpen() {

// setup a custom menu

SpreadsheetApp.getUi()

.createMenu('Toolkit')

.addItem('Get Tools', 'updateTools')

.addToUi();

}

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.

function updateTools() {
  var sheet = SpreadsheetApp.getActive();
  
  // run the check for tools
  var toolkit = sheet.getSheetByName("Toolkit");
  getToolkit( toolkit );
  
  // run the check for families
  var families = sheet.getSheetByName("Families");
  getFamily( families );

}

function updateTools() {

var sheet = SpreadsheetApp.getActive();

// run the check for tools

var toolkit = sheet.getSheetByName("Toolkit");

getToolkit( toolkit );

// run the check for families

var families = sheet.getSheetByName("Families");

getFamily( families );

}

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?

function getToolkit(ss){
    // set up call for tools

    setupToolSheet(ss);
    // set max expected, Rest API max is 100 post
    per_page = 100;
    got = getToolkitData(ss,per_page);
    Logger.log('tool count %s', got);
}

function getToolkit(ss){

// set up call for tools

setupToolSheet(ss);

// set max expected, Rest API max is 100 post

per_page = 100;

got = getToolkitData(ss,per_page);

Logger.log('tool count %s', got);

}

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).

function getToolkitData(ss,per_page) {
  // do the work, call API, populate the rows with results
  
  var options = {
      'method': 'get',
      "contentType" : "application/json",
      'muteHttpExceptions': true
    }
  var apiHost = 'https://toolkit.ecampusontario.ca/wp-json';   // set to your own domain
  url = apiHost + '/wp/v2/tool?orderby=title&order=asc&per_page='+per_page;
    
  try {
    var response = UrlFetchApp.fetch(url, options);
    var data = JSON.parse(response);
    // loop through the map and output to sheet
    for (i = 0; i < data.length; i++) {
     row=i+2;  // set the row to make sure it is below header and takes into account the paging
     ss.getRange('A'+row).setValue(data[i].title.rendered);
     ss.getRange('B'+row).setValue(data[i].link);
  }
    return data.length;
  } catch(error) {
    var result = Logger.log( error.toString());
  }
  return 0;
}

function getToolkitData(ss,per_page) {

// do the work, call API, populate the rows with results

var options = {

'method': 'get',

"contentType" : "application/json",

'muteHttpExceptions': true

}

var apiHost = 'https://toolkit.ecampusontario.ca/wp-json'; // set to your own domain

url = apiHost + '/wp/v2/tool?orderby=title&order=asc&per_page='+per_page;

try {

var response = UrlFetchApp.fetch(url, options);

var data = JSON.parse(response);

// loop through the map and output to sheet

for (i = 0; i < data.length; i++) {

row=i+2; // set the row to make sure it is below header and takes into account the paging

ss.getRange('A'+row).setValue(data[i].title.rendered);

ss.getRange('B'+row).setValue(data[i].link);

}

return data.length;

} catch(error) {

var result = Logger.log( error.toString());

}

return 0;

}

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.

function getFamily(ss){
  // do the work, call API, populate the rows with results

  setupFamilySheet(ss);
  // set max expected, Rest API max is 100 post. 20 should be enough
  per_page = 20;
  got = getFamilyData(ss, per_page);
  Logger.log('Tool Family Count %s', got);
}

function setupFamilySheet(ss) {

  // set up / clear  the Tools sheet
  var range = ss.getDataRange();
  range.clear();
  
 //set headings
  ss.getRange('A1').setValue('Name');
  ss.getRange('B1').setValue('Count');
  ss.getRange('C1').setValue('URL');
  
  ss.getRange('A1:C1').setFontWeight('bold');
  ss.getRange('A1:C1').setFontStyle('italic');
}

function getFamilyData(ss, per_page) {
  // do the work, call API, populate the rows with results
   
  var options = {
      'method': 'get',
      "contentType" : "application/json",
      'muteHttpExceptions': true
    }
  var apiHost = 'https://toolkit.ecampusontario.ca/wp-json';   // base URL
  url = apiHost + '/wp/v2/family?per_page='+per_page;  // API call for custom post type
  try {
    var response = UrlFetchApp.fetch(url, options);
    var data = JSON.parse(response);
    // loop through the map and output to sheet
    for (i = 0; i < data.length; i++) {
     row=i+2;  // set the row to make sure it is below header and takes into account the paging
     ss.getRange('A'+row).setValue(data[i].name);
     ss.getRange('B'+row).setValue(data[i].count);
     ss.getRange('C'+row).setValue(data[i].link);
  }
    return data.length;
  } catch(error) {
    var result = Logger.log( error.toString());
  }
  return 0;
}

function getFamily(ss){

// do the work, call API, populate the rows with results

setupFamilySheet(ss);

// set max expected, Rest API max is 100 post. 20 should be enough

per_page = 20;

got = getFamilyData(ss, per_page);

Logger.log('Tool Family Count %s', got);

}

function setupFamilySheet(ss) {

// set up / clear the Tools sheet

var range = ss.getDataRange();

range.clear();

//set headings

ss.getRange('A1').setValue('Name');

ss.getRange('B1').setValue('Count');

ss.getRange('C1').setValue('URL');

ss.getRange('A1:C1').setFontWeight('bold');

ss.getRange('A1:C1').setFontStyle('italic');

}

function getFamilyData(ss, per_page) {

// do the work, call API, populate the rows with results

var options = {

'method': 'get',

"contentType" : "application/json",

'muteHttpExceptions': true

}

var apiHost = 'https://toolkit.ecampusontario.ca/wp-json'; // base URL

url = apiHost + '/wp/v2/family?per_page='+per_page; // API call for custom post type

try {

var response = UrlFetchApp.fetch(url, options);

var data = JSON.parse(response);

// loop through the map and output to sheet

for (i = 0; i < data.length; i++) {

row=i+2; // set the row to make sure it is below header and takes into account the paging

ss.getRange('A'+row).setValue(data[i].name);

ss.getRange('B'+row).setValue(data[i].count);

ss.getRange('C'+row).setValue(data[i].link);

}

return data.length;

} catch(error) {

var result = Logger.log( error.toString());

}

return 0;

}

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.

function onOpen() {
  // setup a custom menu
  SpreadsheetApp.getUi() 
      .createMenu('Toolkit')
      .addItem('Get Tools', 'updateTools')
      .addItem('Update Form', 'updateForm')
      .addToUi();
}

function onOpen() {

// setup a custom menu

SpreadsheetApp.getUi()

.createMenu('Toolkit')

.addItem('Get Tools', 'updateTools')

.addItem('Update Form', 'updateForm')

.addToUi();

}

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

// call your form and connect to the drop-down item
  var form = FormApp.openById("1KF-0GdYv2bALAdny41egdtA1bcdFlaZBX7k7mM1u48k");
  var toolList = form.getItemById("115599544").asListItem();
  var familyList = form.getItemById("1368946500").asListItem();
      
  // identify the sheet where the data resides needed to populate the drop-down
  var ss = SpreadsheetApp.getActive();
  var tools = ss.getSheetByName("Toolkit");
  var families = ss.getSheetByName("Families");

// call your form and connect to the drop-down item

var form = FormApp.openById("1KF-0GdYv2bALAdny41egdtA1bcdFlaZBX7k7mM1u48k");

var toolList = form.getItemById("115599544").asListItem();

var familyList = form.getItemById("1368946500").asListItem();

// identify the sheet where the data resides needed to populate the drop-down

var ss = SpreadsheetApp.getActive();

var tools = ss.getSheetByName("Toolkit");

var families = ss.getSheetByName("Families");

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.

 // grab the tool name values in the first column of the sheet - use 2 to skip header row
  var familyValues = families.getRange(2, 1, tools.getMaxRows() - 1).getValues();

  var familyNames = [];

  // convert the array ignoring empty cells
  for(var i = 0; i < familyValues.length; i++)   
    if(familyValues[i][0] != "")
      familyNames[i] = familyValues[i][0];

// grab the tool name values in the first column of the sheet - use 2 to skip header row

var familyValues = families.getRange(2, 1, tools.getMaxRows() - 1).getValues();

var familyNames = [];

// convert the array ignoring empty cells

for(var i = 0; i < familyValues.length; i++)

if(familyValues[i][0] != "")

familyNames[i] = familyValues[i][0];

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

// grab the tool name values in the first column of the sheet - use 2 to skip header row
  var familyValues = families.getRange(2, 1, tools.getMaxRows() - 1).getValues();

  var familyNames = [];

  // convert the array ignoring empty cells
  for(var i = 0; i < familyValues.length; i++)   
    if(familyValues[i][0] != "")
      familyNames[i] = familyValues[i][0];

// grab the tool name values in the first column of the sheet - use 2 to skip header row

var familyValues = families.getRange(2, 1, tools.getMaxRows() - 1).getValues();

var familyNames = [];

// convert the array ignoring empty cells

for(var i = 0; i < familyValues.length; i++)

if(familyValues[i][0] != "")

familyNames[i] = familyValues[i][0];

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:

// put the tools in the description
  var description = "We are building an Ontario Extend Toolkit https://toolkit.ecampusontario.ca/ and need your help in building it out. Use this form to suggest a new tool to include or to share a resource or example of how a tool is used.\n\nThe tools currently in the box include "
  description += toolNames.join(", ")
  form.setDescription(description)

// put the tools in the description

var description = "We are building an Ontario Extend Toolkit https://toolkit.ecampusontario.ca/ and need your help in building it out. Use this form to suggest a new tool to include or to share a resource or example of how a tool is used.\n\nThe tools currently in the box include "

description += toolNames.join(", ")

form.setDescription(description)

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

// populate the drop-down in section 1 with the family names
  familyList.setChoiceValues(familyNames); 
    
  // populate the drop-down in section 2 with the tool data
  toolList.setChoiceValues(toolNames);

// populate the drop-down in section 1 with the family names

familyList.setChoiceValues(familyNames);

// populate the drop-down in section 2 with the tool data

toolList.setChoiceValues(toolNames);

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.

Share this barking on social media

If this kind of stuff has value, please support me by tossing a one time PayPal kibble or monthly on Patreon

Comments

Aaron Davis says:

December 22, 2019 at 3:42 am

I really like your WP Posts to CSV plugin Alan. However, considering I usually export the file and then upload the Google Sheets, I wonder if I could use the WP API to simply extract the data straight there.

Also on: Read Write Collect

1. Alan Levine aka CogDog says:
  
  December 22, 2019 at 8:55 am
  
  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

Coding, Ontario Extend

Making Google Forms and Spreadsheets a Bit Less Rigid Block Style

The Need (or Desire)

WordPress API to Google Spreadsheet

Script Time

In The Google Form

The Scripting of the Form

Trigger this Script

That’s All Folks

Comments

Leave a Reply Cancel reply