Using Google Sheets as a Gatsby Backend

Posted on Sunday, January 24, 2021

Google Sheets and Gatsby's GraphQL work great together

GatsbyJS ❤️ Google Sheets

One of the reasons I love working with Gatsby specifically, and React in general, is how easy it usually is to find a plugin to solve whatever problem you're trying to solve. I'm a big fan of not reinventing the wheel. Chances are, you're not the first developer in the world who has come up against a certain use case, so it's usually just a matter of building off of someone else's solution. It turns out that it's very easy to set up an ad hoc backend for Gatsby with Google Sheets by using the gatsby-source-google-spreadsheets plugin by Cian Butler.

The Query

Following the instructions on the plugin's page, it's simple enough to configure. The only gotcha which I will go into detail below is using a Google Sheets API key in your Gatsby site. Once configured, you should see your specified spreadsheet show up in the GraphQL query explorer, which can be found at http://localhost:8000/___graphql if you've run gatsby develop from the command line.

Once you can confirm that the data is coming into your Gatsby site this way, you can run a query inside one of your components to render the data. Spreedsheet tabs will show up at the root level of your site's query explorer with a allGoogle[Tab]Sheet naming convention, and spreadsheet columns will be converted to nodes. As an example, this is how the query from my reading list tab of my spreadsheet data looks, grabbing just the nodes I need:

// GraphQL query for reads.js

const data = useStaticQuery(graphql`
  query {
    allGoogleReadsSheet {
      nodes {
        author
        addAuthors
        dateRead
        title
      }
    }
  }
`)

Mapping the Results

With the query established, using the React hook, useState, to set the starting year for my Bootstrap tabs, it's just a matter of iterating through the data with map(). In the example below, I've separated the data into tabs by year, so I first iterate through the years array before iterating through the query data, only returning entries that match the selected year. This is a sample of my completed reading list JSX:

// reads.js

// import statements, yada yada yada
// ...

const ReadsPage = props => {
  const [yearSelected, setYearSelected] = useState(2021)
  const years = [2021,2020,2019]

  const data = useStaticQuery(graphql`
    query {
      allGoogleReadsSheet {
        nodes {
          author
          addAuthors
          dateRead
          title
        }
      }
    }
  `)
  return (
  // ...
    <h2 className="mt-4">Books Read</h2>
    <p className="mb-4 h5">Sorted by Year Finished, then Author</p>
    <Tabs
      id="books-read"
      activeKey={yearSelected}
      onSelect={(k) => setYearSelected(k)}
    >
      {years.map((year, index) => { // iterating through `years` array
        return (
          <Tab key={year} eventKey={year} title={year}>
            <Table responsive="sm">
              <thead>
                <tr>
                  <th style={{ width: '50%' }}>Title</th>
                  <th>Authors</th>
                  <th className="text-right" width="135">
                    Date Finished
                  </th>
                </tr>
              </thead>
              <tbody>
                {data.allGoogleReadsSheet.nodes.map(({ 
                  // this is where the GraphQL magic happens
                  author,
                  addAuthors,
                  dateRead,
                  title
                }, index) => {
                  return dateRead 
                  && dateRead.includes(year)
                  && (
                    <tr key={index}>
                      <td>{title}</td>
                      <td>
                        {author}
                        {addAuthors && (<>,<br/>{addAuthors}</>)}
                      </td>
                      <td className="text-right">{dateRead}</td>
                    </tr>
                  )
                })}
              </tbody>
            </Table>
          </Tab>
        )
      })}
    </Tabs>
// ...
)}

export default ReadsPage

Environment Variables

As mentioned above, the one gotcha I ran into using the latest version of Google Sheets API and gatsby-source-google-spreadsheets is the need to use an API key. While it's a trivial task to set up a free API key in Google's developer console, I made the rookie move of including said API key in my source code. 😬 This was immediately flagged by Github as a security risk and I quickly got a crash course in Gatsby/Gatsby Cloud environment variables. Fortunately, they really do make it easy (see documentation).

For my local build, I simply had to create a src/.env.development file with the SHEET and API_KEY variables specified, and made sure to hide it from Git in my .gitignore file. For the Gatsby Cloud interface, it only takes a few clicks to define the same variables for production builds. Take a look at my modified gatsby-config.js file with properly hidden env variables:

// gatsby-config.js

// somewhere halfway down the page...
{
  resolve: 'gatsby-source-google-spreadsheets',
  options: {
    spreadsheetId: process.env.SHEET,
    apiKey: process.env.API_KEY
  }
},
// ...

Going Forward

Knowing what I now know about GraphQL makes me want to do a full rewrite of String Band Database, a project I initially wrote in jQuery back in 2013 using a plugin called Sheetrock that allows you to do SQL-like queries against a public Google spreadsheet. The project has since been moved over to Github Pages and is spearheaded by my fellow banjo player and web developer, TJ Ferry, who has done a bang-up job adding new features and curating the content.

The time to modernize our searchable listing of old string band themes is nigh!