The Background
Recently, a friend of mine reached out about a website redesign for his acoustic duo. The site was straight-forward enough with some information about upcoming shows, videos, merch, and contact information. Being comfortable with React, I decided to reach for the new hotness that is Gatsby as a static site generator.
The content within the site was mostly static and what information was going to change was very minimal. The client was savvy enough that a markdown file could work but introducing the idea of either GitHub or modifying files on a server was not so ideal. I didn't want to spring for a full CMS backend but wanted something easily maintained. Enter Google Sheets.
Google Sheets is a spreadsheet tool similar to Microsoft Excel. It's cloud based and allows for editing wherever there is an internet connection. This was perfect for my use case because it allowed the client to edit on his phone, tablet, or at a computer with familiar tools.
Implementing a data source with Google Sheets was definitely foreign but seemed straight-forward enough thanks to a little help from the Gastby community. An open source Gatsby source plugin, gatsby-source-google-sheets, is available that allows the specified Google Sheet to be queryable with GraphQL and used as a data source.
The Setup
Setting the plugin was easy enough thanks to the documentation available on it's GitHub repo. Setup requires some configuration within your Google account to create an API for Google Drive, create a service account, and share the desired spreadsheet with your service account. This will allow the Gastby plugin to access the data and inject it into the data for your site. This Twilio article details these steps in a great follow along way.
Once that is set up, you'll need to set up the plugin for use within your project. First, add the plugin to your gatsby-config.js. The spreadsheet id will be a string in the url of your Google Sheets document. It'll look something like https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID/. Next, you'll need to provide the name of the worksheet within your document where your data is held. Currently, this source plugin only supports one sheet. Your gatsby-config.js should look something like this.
plugins: [
// ...
{
resolve: 'gatsby-source-google-sheets',
options: {
spreadsheetId: YOUR_SPREADSHEET_ID,
worksheetTitle: YOUR_WORKSHEET_TITLE,
credentials: require('./PATH_TO/YOUR/client_secret.json'),
},
},
//...
]
The Solution
With the set up now complete, it's time to start querying for your data and providing it to your pages and components for display. This source plugin exposes two ways to query your Google Sheets data - googleSheetDataRow and allGoogleSheetDataRow. The extent of my use was exclusively with allGoogleSheetDataRow and will be used for the purposes of demonstration. A basic query will look something like
query ShowsQuery {
allGoogleSheetDataRow(filter: { type: { eq: "Show" } }) {
edges {
node {
// Query specific fields within your sheet here
}
}
}
}
This returns an array of objects that represent each row populated with data from your sheet. Each object will contain keys correlating to the column names and values to the data within the cell at the intersection of row and column. Consider a spreadsheet of players on a team - there might be columns for firstName, lastName, jerseyNumber, and position. To use this data within your page or component, you can construct your query and pass it to your React component.
export const query = graphql`
query PlayersQuery {
allGoogleSheetDataRow {
edges {
node {
firstName
lastName
jerseyNumber
position
}
}
}
}
`
// Returns an array of objects
[
{
firstName: "Greg",
lastName: "Olson",
jerseyNumber: 11,
position: "shortstop"
},
{
firstName: "Frank",
lastName: "Alzeada",
jerseyNumber: 61,
position: "left fielder"
},
{
firstName: "Alex",
lastName: "Smith",
jerseyNumber: 4,
position: "catcher"
}
]
// React component rendering the data from the query
const Roster = ({ data }) => (
<article>
{data.allGoogleSheetDataRow.edges.map(obj => (
<div>
<h1>`${obj.node.lastName}, ${obj.node.firstName}`</h1>
<p>`#${obj.node.jerseyNumber} // Position: ${obj.node.position}`</p>
</div>
)}
</article>
)
Improving & Implementing the Solution
For my use case, Google Sheets was a great option given the data needs of the site. One obstacle that I encountered was the need to have two different sources of data pulled in from the same sheet. Due to the current version of gatsby-source-google-sheets only allowing one worksheet to be used, I had to get creative with the way that the sheet was structured and how the client could interact with it to ensure my queries wouldn't break regularly from ill formatted data.
For this site in particular, the two areas that needed data to be updated over time were the listing of upcoming shows for the duo and featured YouTube videos of the duo performing. Both sources of data needed to live within the same sheet but there would be no overlapping of columns between the two. For shows, I would need a date, venue, starttime, endtime, street, city, state and videos needed a videoname and videoId to be passed into the react-youtube component.
To solve this dilemma, another column was added to the sheet designating whether the row was a Show or a Video. This allows the GraphQL queries to be filtered based on the value within that column. Of course, this left a potential issue wherein if the data within that column was not filled out exactly how the filter expects, the query will not recognize the row.
Luckily, data validation within Sheets allows for a drop down to be created and values outside of the drop down to be invalid. This will ensure the values are consistent and able to be found within the GraphQL query.
Adding filters within the GraphQL query allowed me to specify the fields that related to the type of data contained within the row. The filter is added to allGoogleSheetDataRow by specifying the column name to be filter along with the operator and value used to perform the filter. In both of cases, filters for this site were placed on the type column of the sheet using the eq operator and the value within the dropdown corresponding to the type of data needed.
// Query for data pertaining to Shows, filtered on the type column where the value equals Show
export const query = graphql`
query ShowsQuery {
allGoogleSheetDataRow(filter: { type: { eq: "Show" } }) {
edges {
node {
date
venue
starttime
endtime
street
city
state
}
}
}
}
`
// Query for data pertaining to Videos, filtered on the type column where the value equals Video
export const query = graphql`
query VideoQuery {
allGoogleSheetDataRow(filter: { type: { eq: "Video" } }) {
edges {
node {
videoname
videoid
}
}
}
}
`
Final Thoughts
Using Google Sheets as a data source along with Gatsby for a static site with light data needs was a great experience and something worth reaching for again in the future. Gatsby has a great community and ecosystem that adds to the already fantastic developer experience. It's unconventional and might not be the first thing that comes to mind as a data source but is a great option for the niche it serves.