Populate a Spinner from Google Forms

Every few weeks, Tribify holds a RELATIONSHIP SALES MASTERY event where relationship-selling experts teach us how to use the Tribify Engine. At the end of every event, we hold a drawing to give away a free month of our service to one of our attendees.

Attendees fill out a Google Form during the event to register for the drawing. Submissions collect on a Google Sheet, and when we’re ready, we display a Spinner like this one and click it to select a winner!

Spinner Demo – Try It!
If you get a database error, just reload the page.

Fun toy, right? This is a great example of how just a little bit of code can tie freely available cloud services into something new and super useful! Here’s how we did it.

The Spinner

The Spinner widget comes to us from our friends at WheelDecide.

Their web service accepts a query string containing the wheel data and returns the interactive Spinner wheel. For example, this URL lets you choose a random dwarf:

https://wheeldecide.com/e.php?c1=Dopey&c2=Doc&c3=Bashful&c4=Sneezy&c5=Happy&c6=Grumpy&c7=Sleepy

If you embed this URL in an IFRAME, you can place it on a web page in a nicely formatted way. You can also add a time parameter to specify how many seconds the Spinner takes to come to rest. Here’s the same spinner, Dopey-style!

Google Forms & Sheets

We created a Google Form to collect our data. There are plenty of tutorials out there to teach you how to do that, so no need to reinvent the wheel here. Try this one.

Two key details:

• Configure your form to save its data to a Google Sheet. This will be the data source for your Spinner. Here’s the one from our Spinner Demo above.
• Share your Google Sheet with public permissions, so that anyone with the link can view it. This will also permit anybody with the form link to fill it out.

You’ll need three pieces of information from your Google Sheet:

• The spreadsheet_id. That’s the string of characters in the Google Sheet URL between /d/ and /edit, like the one highlighted here: https://docs.google.com/spreadsheets/d/1pF_zSsg4TklsRj-OZv_rj-ywiFGmsKkUwdYIER0mUPU/edit?usp=sharing
• The tab_name. If you are populating your Google Sheet with a Google Form, then by default your data is on the Form tab. We created the List tab to present just the ten most recent entries, so our demo Spinners point to that one.
• The column_name of the data column you want to use to populate your spinner. For the Spinner above, this is “Full Name”.

Finally, you will need a free Google Cloud Platform API Key. You can set one up at your Credential Manager. It will require you to set up a project if you have not already done so. A valid API key will allow you to access data on any Google Sheet, so long as it is publicly shared.

WordPress

We implemented our Spinner widget as a WordPress shortcode. There are three components:

• The shortcode is defined in our functions.php file. It can be configured with Spinner parameters when it is embedded on a page. It can also can pull the same parameters from the page’s query string. Either way, it constructs the Spinner’s DIV container and embeds its parameters as data attributes.
• A Javascript function runs after page load to find each Spinner container on the page, pull data from the relevant Google Sheet to compose its WheelDecide URL, and display the Spinner in an IFRAME with an optional title.
• A little bit of CSS centers the Spinner in its container and formats the title.

PHP Code

// Create a shortcode to generate a spinner.
function tribify_spinner_shortcode($atts = array()) {
  // Pull spinner parameters from query string.
  $params = array(
    'title' => $_GET['title'],
    'api_key' => $_GET['api_key'],
    'spreadsheet_id' => $_GET['spreadsheet_id'],
    'tab_name' => $_GET['tab_name'],
    'column_name' => $_GET['column_name'],
    'size' => $_GET['size'],
    'time' => $_GET['time'],
    'query_override' => $_GET['query_override'],
    'hide_on_show' => '',
    'error_target' => ''
  );

  // Pull spinner parameters from shortcode attributes. Use query string values as defaults.
  extract(shortcode_atts($params, $atts));
  
  // If query_override is set, override shortcode values with query string values.
  if (isset($query_override)) {
    $title = $params['title'] ?? $title;
    $api_key = $params['api_key'] ?? $api_key;
    $spreadsheet_id = $params['spreadsheet_id'] ?? $spreadsheet_id;
    $tab_name = $params['tab_name'] ?? $tab_name;
    $column_name = $params['column_name'] ?? $column_name;
    $size = $params['size'] ?? $size;
    $time = $params['time'] ?? $time;
  }

  // Construct spinner container with parameters as DATA attributes.
  $content  = "<div class=\"tribify-spinner\"";
  $content .= " data-title=\"" . $title . "\"";
  $content .= " data-api_key=\"" . $api_key . "\"";
  $content .= " data-spreadsheet_id=\"" . $spreadsheet_id . "\"";
  $content .= " data-tab_name=\"" . $tab_name . "\"";
  $content .= " data-column_name=\"" . $column_name . "\"";
  $content .= " data-size=\"" . $size . "\"";
  $content .= " data-time=\"" . $time . "\"";
  $content .= " data-hide_on_show=\"" . $hide_on_show . "\"";
  $content .= " data-error_target=\"" . $error_target . "\"";
  $content .= "></div>";

  // Return the constructed spinner container.
  return $content;
}
add_shortcode('tribify_spinner', 'tribify_spinner_shortcode');

JavaScript Code

// Populate spinners after the page loads.
jQuery(document).on('ready', function() {
    // For each spinner container on the page...
    jQuery('div.tribify-spinner').each(function() {
        // Get the spinner container.
        const spinner = jQuery(this);

        // Create a handler for error messages.
        const error_target = jQuery(spinner.attr('data-error_target'));
        const spinner_error = (msg) => (error_target ?? spinner).append(msg);

        // Extract required DATA attributes and throw an error if one is missing.
        const api_key = spinner.attr('data-api_key');
        if (!api_key) return spinner_error('Missing api_key');

        const spreadsheet_id = spinner.attr('data-spreadsheet_id');
        if (!spreadsheet_id) return spinner_error('Missing spreadsheet_id');

        const tab_name = spinner.attr('data-tab_name');
        if (!tab_name) return spinner_error('Missing tab_name');

        const column_name = spinner.attr('data-column_name');
        if (!column_name) return spinner_error('Missing column_name');

        const size = spinner.attr('data-size');
        if (!size) return spinner_error('Missing size');

        // Construct a URL to retrieve Google Sheet data as JSON.
        const url = `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheet_id}/values/${tab_name}?alt=json&key=${api_key}`;

        // Query the Google Sheet. When the response arrives...
        (jQuery.getJSON(url, 'callback=?')).done(function(data) {
            // Pass along the error message on failure.
            if (data.error) return spinner_error(data.error.message);

            // Throw an error if there is no data or only a header row.
            let values = data.values;  
            if (values.length < 2) return spinner_error('No data');

            // Find the target column and throw an error if not found.
            const i = values.shift().indexOf(column_name);
            if (i === -1) return spinner_error(`column_name "${column_name}" not found`);

            // Filter out blank values and throw an error if nothing remains.
            values = values.filter(v => v.length);
            if (!values.length)  return spinner_error('No values');

            // Construct the spinner query parameters.
            let query = values.map((v, n) => 'c' + (n + 1) + '=' + encodeURI(v[i])).join('&');

            // Add a time parameter if specified.
            const time = spinner.attr('data-time');
            if (time) query += '&time=' + time;

            const hide_on_show = spinner.attr('data-hide_on_show');
            if(hide_on_show) jQuery(hide_on_show).hide();

            // Insert a title if specified.
            const title = spinner.attr('data-title');
            if (title) jQuery(`<h2>${title}</h2>`).appendTo(spinner);

            // Create IFRAME.
            jQuery(`<iframe scrolling=\"no\" frameborder=\"0\"></iframe>`)
                .appendTo(jQuery('<div></div>').appendTo(spinner))
                .css('height', `${size}px`)
                .css('width', `${size}px`)
                .attr('name', `spinner_${Math.random().toString(36).substring(2, 15)}`)
                .attr('src', `https://wheeldecide.com/e.php?${query}`);
        });
    });
});

CSS Code

div.tribify-spinner > div {
	display: flex; 
	justify-content: center;
}

div.tribify-spinner > h2 {
	text-align: center;
	margin-bottom: 0.5em;
}

Shortcode & Open Spinner

Put all this together, and we were able to embed the Spinner at the top of this page using the following shortcode:

If we left one or more parameters out and passed them in via the query string, then we could change the data source, the size, or the timing without making any changes at all to the underlying page!

Try some variations on our Open Spinner page:

• Spinner Demo Full Name data
• Spinner Demo Company data (and smaller)
• Pick a University (from this public sheet)

The Open Spinner is at https://tribify.me/spinner. It uses our own API key, and you can pass in the following query parameters:

• spreadsheet_id (required)
• tab_name (required)
• column_name (required)
• title (optional)
• size (optional, default 700)
• time (optional, default 10)
• api_key (only if you want to use yours instead)

Feel free to use our Open Spinner to make all of your really important life decisions! 😂🤣😂

Have fun, and if you find an interesting way to use the Open Spinner or decide to implement your own Spinner shortcode, let us know in the comments below!