By David - September 3, 2020

A Drupal8 website may integrate Google Sheet data seamlessly in an existing site while while allowing team collaborated data to be easily maintained by employees in a familiar Excel-like interface for data sharing and management.

The WSG has maintained an attendance list per academic year in Google Sheets for many years. With the 2020 redesign of the website in Drupal8 we wanted to create a listing of active members that met the following criteria:

  • The list is always up to date when new group members are on-boarded.
  • The listing of active members is self maintained over time, meaning if we decide that the last 4 months of attendance is the criteria by which a user is displayed in the list, the list should reflect this criteria.
  • The list of members should ideally match the existing website branding guidelines.

As our list is maintained in Google Sheets, with some ingenuity we wanted to include a member list seamlessly into the website using features of the Drupal8 CMS that are largely available out of the box, and use re-use the campus existing Google for Edu service agreement. This blog post will serve as a brief tutorial on this integration and discuss obstacles encountered and provide resources for future similar endeavors by astute readers.

Integration Details

Preparing Our Data in Google Sheets

The WSG maintains attendance sheet(s) across the years the group has been active. Typically, one sheet per academic year is used. The attendance sheet has the following format:

SHEET EXAMPLE1 EMBED

This sheet contains alot of information, and we'd rather not share all of the sheet data with site visitors. So, in order to expose only the bare minimum list content of attendee names to the public, we take the keep the primary attendance sheet hidden and create a new Google Sheet which will be our webview of the attendance list. The webview sheet of the attendance data imports a limited relevant set columns of data from the attendance data to perform the task at hand: showing only recent attendees. A custom google formula value imports the data, and then examines which attendees are marked as "attended" per monthly meeting:

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/GOOGLESHEET_ID/edit#gid=0", "AttendanceLog!D4:Q1000"), "select Col1 where (Col2 is not null) or (Col3 is not null) or (Col4 is not null)", 0)

Let's break down this Sheet formula, from the inside-out:

  • The sheet tab named webview uses IMPORTRANGE to reference the data found in another google sheet, and limits the referencing to the set of data found in the cell range D4:Q1000
  • Having imported the range of data (as an array of data in Sheets). A QUERY is run on the data to return only the list of names that are marked as attended in the attendance log sheet. The query against the array of information is:
    select Col1 where (Col2 is not null) or (Col3 is not null) or (Col4 is not null)

    Where Col1 is the Name entry, and the remaining columns are columns that track monthly participation in meetings. Participation in a meeting is merely whether or not the column value for a given month is null or not.
  • Lastly the QUERY formula does not display any headers from the google sheet, by passing a 0 as the third parameter to the query function, as we want only the raw list of data and not the value "Name" found as the column heading in the parent attendance log sheet.

Now our sheet contains a list of active attendees. In Google Sheets the data is always uptodate when:

  • any change to (parent) the attendance log sheet is instantly reflected in our webview sheet file. If we fix in a typo in a name? The changes are reflected instantly. If we manually update the attendance values for the most recent meeting? The new members are instantly included in the list.
  • Yearly the above excel sheet formula for the QUERY and IMPORTRANGE may need to be updated to reflect a new attendance log sheet url reference, but that is a small maintanence task.

Making Our Data Available to Drupal8

You may have been wondering why we use two sheets to show our attendance list on a website. This is so that we can keep mundane or un-needed data for this task private to the group governance, while providing this brief list of attendees to the public. The Google Sharing Permissions allows us to easily Publish the webview sheet to the web. To do this we took the following step(s):

  • In the webview sheet use File -> Publish to Web ... limit the sheet to just the webview named sheet tab, and Publish to the web.

The sheet is then available at a Google Sheets Feed API endpoint by convention at a url:
 

https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values?alt=json

Below is an example set of json data returned by the Google Sheets Feed url. When inspecting this data live, the json data contains no formatting whitespace to reduce filesize. But, for readability the json has been pretty-printed so its structure can be better understood.

Example Json response from Google Spreadsheet API Feed url (click to expand)
{
  "version": "1.0",
  "encoding": "UTF-8",
  "feed": {
    "xmlns": "http://www.w3.org/2005/Atom",
    "xmlns$openSearch": "http://a9.com/-/spec/opensearchrss/1.0/",
    "xmlns$gsx": "http://schemas.google.com/spreadsheets/2006/extended",
    "id": {
      "$t": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values"
    },
    "updated": {
      "$t": "2020-09-04T04:15:34.788Z"
    },
    "category": [
      {
        "scheme": "http://schemas.google.com/spreadsheets/2006",
        "term": "http://schemas.google.com/spreadsheets/2006#list"
      }
    ],
    "title": {
      "type": "text",
      "$t": "webview"
    },
    "link": [
      {
        "rel": "alternate",
        "type": "application/atom+xml",
        "href": "https://docs.google.com/spreadsheets/u/0/d/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/pubhtml"
      },
      {
        "rel": "http://schemas.google.com/g/2005#feed",
        "type": "application/atom+xml",
        "href": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values"
      },
      {
        "rel": "http://schemas.google.com/g/2005#post",
        "type": "application/atom+xml",
        "href": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values"
      },
      {
        "rel": "self",
        "type": "application/atom+xml",
        "href": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values?alt=json"
      }
    ],
    "author": [
      {
        "name": {
          "$t": "author"
        },
        "email": {
          "$t": "author@ucsb.edu"
        }
      }
    ],
    "openSearch$totalResults": {
      "$t": "18"
    },
    "openSearch$startIndex": {
      "$t": "1"
    },
    "entry": [
      {
        "id": {
          "$t": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/cokwr"
        },
        "updated": {
          "$t": "2020-09-04T04:15:34.788Z"
        },
        "category": [
          {
            "scheme": "http://schemas.google.com/spreadsheets/2006",
            "term": "http://schemas.google.com/spreadsheets/2006#list"
          }
        ],
        "title": {
          "type": "text",
          "$t": "Ally Doe"
        },
        "content": {
          "type": "text",
          "$t": ""
        },
        "link": [
          {
            "rel": "self",
            "type": "application/atom+xml",
            "href": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/cokwr"
          }
        ],
        "gsx$aaronmartin": {
          "$t": "John Doe"
        }
      },
      {
        "id": {
          "$t": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/cpzh4"
        },
        "updated": {
          "$t": "2020-09-04T04:15:34.788Z"
        },
        "category": [
          {
            "scheme": "http://schemas.google.com/spreadsheets/2006",
            "term": "http://schemas.google.com/spreadsheets/2006#list"
          }
        ],
        "title": {
          "type": "text",
          "$t": "Brian Doe"
        },
        "content": {
          "type": "text",
          "$t": ""
        },
        "link": [
          {
            "rel": "self",
            "type": "application/atom+xml",
            "href": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/cpzh4"
          }
        ],
        "gsx$aaronmartin": {
          "$t": "Jane Doe"
        }
      },
      {
        "id": {
          "$t": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/cre1l"
        },
        "updated": {
          "$t": "2020-09-04T04:15:34.788Z"
        },
        "category": [
          {
            "scheme": "http://schemas.google.com/spreadsheets/2006",
            "term": "http://schemas.google.com/spreadsheets/2006#list"
          }
        ],
        "title": {
          "type": "text",
          "$t": "Carlos Doe"
        },
        "content": {
          "type": "text",
          "$t": ""
        },
        "link": [
          {
            "rel": "self",
            "type": "application/atom+xml",
            "href": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/cre1l"
          }
        ],
        "gsx$aaronmartin": {
          "$t": "Jim Doe"
        }
      },
      {
        "id": {
          "$t": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/chk2m"
        },
        "updated": {
          "$t": "2020-09-04T04:15:34.788Z"
        },
        "category": [
          {
            "scheme": "http://schemas.google.com/spreadsheets/2006",
            "term": "http://schemas.google.com/spreadsheets/2006#list"
          }
        ],
        "title": {
          "type": "text",
          "$t": "David Doe"
        },
        "content": {
          "type": "text",
          "$t": ""
        },
        "link": [
          {
            "rel": "self",
            "type": "application/atom+xml",
            "href": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/chk2m"
          }
        ],
        "gsx$aaronmartin": {
          "$t": "Frank Doe"
        }
      },
      {
        "id": {
          "$t": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/ciyn3"
        },
        "updated": {
          "$t": "2020-09-04T04:15:34.788Z"
        },
        "category": [
          {
            "scheme": "http://schemas.google.com/spreadsheets/2006",
            "term": "http://schemas.google.com/spreadsheets/2006#list"
          }
        ],
        "title": {
          "type": "text",
          "$t": "Jenny Doe"
        },
        "content": {
          "type": "text",
          "$t": ""
        },
        "link": [
          {
            "rel": "self",
            "type": "application/atom+xml",
            "href": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/ciyn3"
          }
        ],
        "gsx$aaronmartin": {
          "$t": "Jen Doe"
        }
      },
      {
        "id": {
          "$t": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/ckd7g"
        },
        "updated": {
          "$t": "2020-09-04T04:15:34.788Z"
        },
        "category": [
          {
            "scheme": "http://schemas.google.com/spreadsheets/2006",
            "term": "http://schemas.google.com/spreadsheets/2006#list"
          }
        ],
        "title": {
          "type": "text",
          "$t": "Ken Doe"
        },
        "content": {
          "type": "text",
          "$t": ""
        },
        "link": [
          {
            "rel": "self",
            "type": "application/atom+xml",
            "href": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/ckd7g"
          }
        ],
        "gsx$aaronmartin": {
          "$t": "Ken Doe"
        }
      },
      {
        "id": {
          "$t": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/clrrx"
        },
        "updated": {
          "$t": "2020-09-04T04:15:34.788Z"
        },
        "category": [
          {
            "scheme": "http://schemas.google.com/spreadsheets/2006",
            "term": "http://schemas.google.com/spreadsheets/2006#list"
          }
        ],
        "title": {
          "type": "text",
          "$t": "Eric Doe"
        },
        "content": {
          "type": "text",
          "$t": ""
        },
        "link": [
          {
            "rel": "self",
            "type": "application/atom+xml",
            "href": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/clrrx"
          }
        ],
        "gsx$aaronmartin": {
          "$t": "Ben Doe"
        }
      },
      {
        "id": {
          "$t": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/cyevm"
        },
        "updated": {
          "$t": "2020-09-04T04:15:34.788Z"
        },
        "category": [
          {
            "scheme": "http://schemas.google.com/spreadsheets/2006",
            "term": "http://schemas.google.com/spreadsheets/2006#list"
          }
        ],
        "title": {
          "type": "text",
          "$t": "Garret Doe"
        },
        "content": {
          "type": "text",
          "$t": ""
        },
        "link": [
          {
            "rel": "self",
            "type": "application/atom+xml",
            "href": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/cyevm"
          }
        ],
        "gsx$aaronmartin": {
          "$t": "Allen Doe"
        }
      },
      {
        "id": {
          "$t": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/cztg3"
        },
        "updated": {
          "$t": "2020-09-04T04:15:34.788Z"
        },
        "category": [
          {
            "scheme": "http://schemas.google.com/spreadsheets/2006",
            "term": "http://schemas.google.com/spreadsheets/2006#list"
          }
        ],
        "title": {
          "type": "text",
          "$t": "Alex Doe"
        },
        "content": {
          "type": "text",
          "$t": ""
        },
        "link": [
          {
            "rel": "self",
            "type": "application/atom+xml",
            "href": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/cztg3"
          }
        ],
        "gsx$aaronmartin": {
          "$t": "Karen Doe"
        }
      },
      {
        "id": {
          "$t": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/d180g"
        },
        "updated": {
          "$t": "2020-09-04T04:15:34.788Z"
        },
        "category": [
          {
            "scheme": "http://schemas.google.com/spreadsheets/2006",
            "term": "http://schemas.google.com/spreadsheets/2006#list"
          }
        ],
        "title": {
          "type": "text",
          "$t": "James Doe"
        },
        "content": {
          "type": "text",
          "$t": ""
        },
        "link": [
          {
            "rel": "self",
            "type": "application/atom+xml",
            "href": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/d180g"
          }
        ],
        "gsx$aaronmartin": {
          "$t": "Fanny Doe"
        }
      },
      {
        "id": {
          "$t": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/d2mkx"
        },
        "updated": {
          "$t": "2020-09-04T04:15:34.788Z"
        },
        "category": [
          {
            "scheme": "http://schemas.google.com/spreadsheets/2006",
            "term": "http://schemas.google.com/spreadsheets/2006#list"
          }
        ],
        "title": {
          "type": "text",
          "$t": "Jill Doe"
        },
        "content": {
          "type": "text",
          "$t": ""
        },
        "link": [
          {
            "rel": "self",
            "type": "application/atom+xml",
            "href": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/d2mkx"
          }
        ],
        "gsx$aaronmartin": {
          "$t": "Jill Doe"
        }
      },
      {
        "id": {
          "$t": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/cssly"
        },
        "updated": {
          "$t": "2020-09-04T04:15:34.788Z"
        },
        "category": [
          {
            "scheme": "http://schemas.google.com/spreadsheets/2006",
            "term": "http://schemas.google.com/spreadsheets/2006#list"
          }
        ],
        "title": {
          "type": "text",
          "$t": "Glen Doe"
        },
        "content": {
          "type": "text",
          "$t": ""
        },
        "link": [
          {
            "rel": "self",
            "type": "application/atom+xml",
            "href": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/cssly"
          }
        ],
        "gsx$aaronmartin": {
          "$t": "Al Doe"
        }
      },
      {
        "id": {
          "$t": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/cu76f"
        },
        "updated": {
          "$t": "2020-09-04T04:15:34.788Z"
        },
        "category": [
          {
            "scheme": "http://schemas.google.com/spreadsheets/2006",
            "term": "http://schemas.google.com/spreadsheets/2006#list"
          }
        ],
        "title": {
          "type": "text",
          "$t": "Matt Doe"
        },
        "content": {
          "type": "text",
          "$t": ""
        },
        "link": [
          {
            "rel": "self",
            "type": "application/atom+xml",
            "href": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/cu76f"
          }
        ],
        "gsx$aaronmartin": {
          "$t": "Matt Doe"
        }
      },
      {
        "id": {
          "$t": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/cvlqs"
        },
        "updated": {
          "$t": "2020-09-04T04:15:34.788Z"
        },
        "category": [
          {
            "scheme": "http://schemas.google.com/spreadsheets/2006",
            "term": "http://schemas.google.com/spreadsheets/2006#list"
          }
        ],
        "title": {
          "type": "text",
          "$t": "Mike Doe"
        },
        "content": {
          "type": "text",
          "$t": ""
        },
        "link": [
          {
            "rel": "self",
            "type": "application/atom+xml",
            "href": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/cvlqs"
          }
        ],
        "gsx$aaronmartin": {
          "$t": "Mike Doe"
        }
      },
      {
        "id": {
          "$t": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/cx0b9"
        },
        "updated": {
          "$t": "2020-09-04T04:15:34.788Z"
        },
        "category": [
          {
            "scheme": "http://schemas.google.com/spreadsheets/2006",
            "term": "http://schemas.google.com/spreadsheets/2006#list"
          }
        ],
        "title": {
          "type": "text",
          "$t": "Ben Doe"
        },
        "content": {
          "type": "text",
          "$t": ""
        },
        "link": [
          {
            "rel": "self",
            "type": "application/atom+xml",
            "href": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/cx0b9"
          }
        ],
        "gsx$aaronmartin": {
          "$t": "Clark Doe"
        }
      },
      {
        "id": {
          "$t": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/d9ney"
        },
        "updated": {
          "$t": "2020-09-04T04:15:34.788Z"
        },
        "category": [
          {
            "scheme": "http://schemas.google.com/spreadsheets/2006",
            "term": "http://schemas.google.com/spreadsheets/2006#list"
          }
        ],
        "title": {
          "type": "text",
          "$t": "Robert Doe"
        },
        "content": {
          "type": "text",
          "$t": ""
        },
        "link": [
          {
            "rel": "self",
            "type": "application/atom+xml",
            "href": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/d9ney"
          }
        ],
        "gsx$aaronmartin": {
          "$t": "Robert Doe"
        }
      },
      {
        "id": {
          "$t": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/db1zf"
        },
        "updated": {
          "$t": "2020-09-04T04:15:34.788Z"
        },
        "category": [
          {
            "scheme": "http://schemas.google.com/spreadsheets/2006",
            "term": "http://schemas.google.com/spreadsheets/2006#list"
          }
        ],
        "title": {
          "type": "text",
          "$t": "Jim Doe"
        },
        "content": {
          "type": "text",
          "$t": ""
        },
        "link": [
          {
            "rel": "self",
            "type": "application/atom+xml",
            "href": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/db1zf"
          }
        ],
        "gsx$aaronmartin": {
          "$t": "Jim Doe"
        }
      },
      {
        "id": {
          "$t": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/dcgjs"
        },
        "updated": {
          "$t": "2020-09-04T04:15:34.788Z"
        },
        "category": [
          {
            "scheme": "http://schemas.google.com/spreadsheets/2006",
            "term": "http://schemas.google.com/spreadsheets/2006#list"
          }
        ],
        "title": {
          "type": "text",
          "$t": "Tony Doe"
        },
        "content": {
          "type": "text",
          "$t": ""
        },
        "link": [
          {
            "rel": "self",
            "type": "application/atom+xml",
            "href": "https://spreadsheets.google.com/feeds/list/1S7dsAd1Mo2RBEyzFeOpuOu59CpAZ54NbctU4w2cXFAx/1/public/values/dcgjs"
          }
        ],
        "gsx$aaronmartin": {
          "$t": "Tony Doe"
        }
      }
    ]
  }
}

Including the Data into Drupal8

Out of the box Drupal8 has support for JSON data as apart of Core. Although, in order to consume this data more easily and without needing to create additional custom code in a custom module I opted to install the Views Json Source module, this allows the Drupal Views subsystem to easily use a json resource (file or url) as Views data for rendering.

Having installed the Views add-on module I modified the views' Advanced -> Query Settings to point to my Google Sheet feed data url .... and nothing happened. Everything broke, and I gave up in frustration for about 5 minutes.

... A problem arose that (apparently) the contrib module wants an array of json data to create simple paths against to find values.

Views Json Source query settings

Whereas previously shown above, the google sheet json output is a very messy json object.

After lamenting the various woes of 2020 thusfar and the frustration of getting thusfar in this task only to hit a brick wall of data incompatibility ... I envisioned a solution. I divined that perhaps a bridge file or mechanism could sit between Google Sheets and the Drupal Views Source ingestion task. So rather than input the Google Sheet feed url directly into the views settings -- somehow get the data from another location resource (url) that serves properly formatted data for the plugin. I recalled the Pantheon platform supplies a Private files location for our website that can be used to access scripts. Typically this private location is used for Continuous Integration scripts, but any random (php) script can live in this file and be kept hidden from prying eyes. So I went about creating a simple script in our Pantheon environment located at: <code>/private/bridges/google-sheets-attendance-list-bridge.php As mentioned in the Pantheon Docs, files within this private directory space can be made public to the world via a symlink, so I added such a symlink to our project:

cd PANTHEON_CODE_DIR
ln -s private/bridges/google-sheets-attendance-list-bridge.php google-sheets-attendance-list-bridge.php

Then in the views query settings I made the resource target for our json data this new on-site url resource. The url used during development was: https://gsheet-json-webguide-ucsb-edu-v01.pantheonsite.io/bridges/google-sheets-attendance-list-bridge.php

Note that this url does not include the private path directory structure, it's simply uses the public symlink resource location.

The purpose of this bridge file was to use basic PHP code to transform the unusable json provided by google sheets into a simplified usable json array value for the views plugin. An example of the PHP code is:

php file google-sheets-attendance-list-bridge.php (click to expand)
<?php

$raw_data = NULL;
$list = [];
try {
$url = 'https://spreadsheets.google.com/feeds/list/GOOGLESHEET_ID/1/public/values?alt=json';
$raw_data = file_get_contents($url);
#echo $raw_data;
if ($raw_data != NULL) {
	// An alternate implementation detail with json_decode($raw, TRUE) could have
    // returned a 2d assoc array. That may have been easier as derefencing '$t' value was hard.
	$json = json_decode($raw_data);
	if (count($attendance_list_array)) {
		foreach ($attendance_list_array as $i => $attendance_entry) {
          $list[]['name'] = (string)$attendance_entry->title->{'$t'};
		}
		echo json_encode((array)$list);
	}
}

} catch (Exception $e) {
	echo "";
}

 

Calling the Google Sheets API via the on-site php script transforms (bridges) the ugly json emitted by Google into a usable format for the drupal views contrib module. An example output of the on-site php bridge endpoint url is below. For clarity the response output has been formatted, but over-the-wire the json response data has no formatting and is extremely compact.

Example on-site php bridge script url json output (click to expand)
[
  {
    "name": "Ally Doe"
  },
  {
    "name": "Brian Doe"
  },
  {
    "name": "Carlos Doe"
  },
  {
    "name": "David Doe"
  },
  {
    "name": "Denise Doe"
  },
  {
    "name": "Eric Doe"
  },
  {
    "name": "Erin Doe"
  },
  {
    "name": "Gary Doe"
  },
  {
    "name": "Frank Doe"
  },
  {
    "name": "James Doe"
  },
  {
    "name": "Judy Doe"
  },
  {
    "name": "Allen Doe"
  },
  {
    "name": "Matt Doe"
  },
  {
    "name": "Jane Doe"
  },
  {
    "name": "Richard Doe"
  },
  {
    "name": "Robert Doe"
  },
  {
    "name": "Ryan Doe"
  },
  {
    "name": "Tony Doe"
  }
]


Some caveats with the php script used to bridge the Google Sheets API are:

  • the script example does not at this point use the Drupal API directly, nor does it bootstrap the Drupal site. Arguably, the script should use the Drupal API directly, but by being a bare-bones php script of 10-20 lines it's rather irrelevant should the site be upgraded to Drupal version 9, 10 or 11 in the future ... hese basic php functions are (hopefully) not likely to go anywhere soon.
  • Should there be a network error at the time the json data is fetched by the website from google, the script does not take any steps to avoid disaster. At this time the View settings could simply insert some default Empty Result text such as please check back later for our current member list. But, a future revision of this script could store the json response data into a temporary file under the files directory of the website and only update, or read, data from that file if the json request from Google was successful. Not being mission critical site functionality at this time, additional coding to handle network areas and preserving json data in the event of network issues has not been accounted for.

Difficulties Encountered

Discovering the google sheet tab (GID) value to use for the feed url for google sheets was extremely difficult and frustrating. The value found of 1 was not the value seen in the URL when editing the google sheet tab content, nor is it the ID of google sheet/file.

As mentioned previously, the json data returned from Google Sheets contained the data we wanted. But, it's format was apparently not friendly to the Views Json Source module which expected an Array of json data. The google sheets api returns a json Object of many nested convoluted values. As discussed previously, a simple php script was created to resolve this minor integration issue.

Various attempts were made at using a QUERY value to get the attendance log data. If the data exists within the same sheet and a QUERY references same-sheet data, you may reference QUERY values in SELECT using column values A,B,C,D identifiers. But, if your QUERY uses IMPORTRANGE then the data returned by importrange no longer can use the identifiers A,B,C... but must use the array references Col1, Col2, Col3 to refer to column values. This can make creating Google Sheet formulas difficult for the uniniated.

Future Enhancements

In the future the google sheets URL reference for the attendance sheet may need to change. The campus ucsbWebTheme uses the Site Settings module. If the 10-20 line php bridge file used the Drupal API .... a Site Setting value could be provided for future site editors to manage the URL of the webview attendance sheet as needed, without needing to update the custom php script file. But, even that additional development effort may not be worthwhile for a value that is only being updated once a year.

Additional Resources

The following resources were helpful in completing this task: