Spreadsheets are everywhere. Look into any business and you'll find spreadsheets filling the gaps that other software leaves unfilled. Google Sheets are so handy for capturing and sharing bits of information, I must have hundreds of them. Any time I want to keep track of something more than a simple list, I start with a spreadsheet.

The only time I have an issue with Google Sheets is when I want to use the data that's in them somewhere else. I could export the data, but then I have a synchronization problem to solve. There is an official API, but it's not the easiest thing to use. Frankly, I'm lazy. I don't want to have to write a bunch of code. What I'd really like is an API that is as easy to use as sharing the link to the spreadsheet. Since a sheet is a simple table, it would make a great data source for a GraphQL API¹. If I could get a GraphQL API on top of any sheet, I'd be set.

Unfortunately, building a GraphQL API on top of a Google Sheet does require writing a bunch of code — that is, until now! 😉 Read on to learn how to create your own GraphQL APIs using Google Sheets without writing any code using StepZen.

The Scenario

Let's walk through a scenario to show how you can do this. I have a Google Sheet full of links and related keywords. I'd like to use this sheet as the data source for a "Related Links" section on a website. The sheet looks like this:

I have URLs, titles, keywords, and a flag to indicate whether or not I want the link to be published. What I want to do is display a simple list of links based on a keyword I provide. If I had an API on top of this sheet I could retrieve only the links I wanted, and I could edit my sheet to change what gets displayed on my website. That would be pretty cool!

So let's do it. First we'll get the API working, then pull it together with a sample app at the end.

Building the GraphQL API

If you're in StepZen's private alpha developer program³, you've seen how you can use StepZen to add a JSON Web API to your GraphQL schema using some simple configuration. (Not in the program? Request an invite here) In the next few steps, I'll use that same sort of configuration to add my sheet to a GraphQL schema.

Querying Google Sheets via HTTP

To use the Google Sheet as a JSON API, we're going to have to get a little creative. I said earlier that the official Sheets API was not easy to use. Authentication can be tricky, and you typically need to know the shape of the data you want to read ahead of time. I don't want to have to worry about any of that. Luckily, there is an API that's much easier to use and that is the Google Charts Visualization API⁴. Using that API is as simple as making an HTTP GET request with 3 simple query parameters:

• the spreadsheetId
• the sheetId
• a query string

You can find the two IDs easily by looking at the URL for one of your spreadsheets:

https://docs.google.com/spreadsheets/d/{spreadsheetId}/edit#gid={sheetId}

For example, in the picture below, the key or spreadsheetId is 1B-Qm31wtK0UUyXsdguS8_bwddkzvAFL_TEXugB1NAZc and the gid or sheetId is 0.

As for the query string, we'll get into more detail a bit later, but if you wanted all the data from the sheet, you would use SELECT%20* (which is just SELECT * url encoded).

---

Before we go further, I have to admit that I told a bit of a white lie when I said the Visualization API was much easier to use. It is much easier to call than the official Sheets API. The Visualization API is designed to be used directly from a browser, so calling it is straightforward. The tricky bit comes in when we need to process the response. The response isn't JSON, but rather it's JavaScript (or CSV, but that's not as helpful here). Inside that block of JavaScript is a chunk of JSON with the data we need. So, to use this API with StepZen we do need to write a bit of code. BUT, we did that for you! We've created an api that accepts a spreadsheetId (key), sheetId (gid), and query string (q) and returns nicely structured JSON we can use in our GraphQL API.

---

Ok, now that I've owned up, we can talk about wiring up any Google Sheet to a GraphQL API using StepZen. Oh, one more little thing, your sheet needs to have link sharing turned on. You don't have to "Publish to the Web" or anything, just enable link sharing.

The @rest Directive in StepZen

When you use StepZen to create a GraphQL API, you write the schema for your API in SDL (Schema Definition Language). In your SDL you define the interface for your types and in your type definitions you tell StepZen how to retrieve the data for the types. StepZen provides specific GraphQL directives you can use to indicate where the data for your schema comes from, be it database, JSON HTTP API, or one of the pre-built StepZen connectors. For a JSON HTTP API, the directive we use is called @rest. Here's a simple example schema that defines a customer interface, and tells StepZen to use a JSON API at https://customers.apis.stepzen.com to fulfill requests for types that implement that customer interface:

interface Customer {
  id: ID!
  name: String!
  email: String
}
type Query {
  customer(id: ID!): Customer
}
type CustomerBackend implements Customer {
}
type Query {
  customerByIdBackend(id: ID!): CustomerBackend
      @supplies(query: "customer")
      @rest(endpoint:"https://customers.apis.stepzen.com", configuration: "custom_config")
}

In the above schema, the customerByIdBackend query is defined. It requires one parameter, id, and it returns the type CustomerBackend. In the definition for the query we use two directives, @supplies and @rest. The @supplies directive tells StepZen that it can use this query to fullfill the generic customer query defined earlier in the file. The @rest directive has two attributes, endpoint which tells StepZen where to make the api call to retrieve data, and configuration which tells StepZen which configuration, if any to use. A configuration would contain things like API keys or Bearer tokens, so you don't have to put those directly in your SDL.

When you upload the above schema to StepZen, StepZen saves the configuration on how to fulfill requests for your types, and will serve up the GraphQL schema as a plain schema, without any of the StepZen directives listed. StepZen can then process queries for data, using the directives you've specified to retrieve the appropriate data for any given query.

Using the @rest Directive in our Schema

For our schema, we'll use the function we wrote that turns the Visualization API into a simple JSON API for the @rest directive (https://sheets.apis.stepzen.com/{key}/{gid}), including the SpreadsheetId and and SheetId parameters as key and gid respectively, and the query string will be passed along when we query our schema. For example, we might have something that looks like this:

type Query {
  relatedlinks(q:String!): [RelatedLinks]
  sheetsrelatedlinks(q:String!): [SheetsRelatedLinks]
    @supplies(query:"relatedlinks")
    @rest(endpoint:"https://sheets.apis.stepzen.com/1B-Qm31wtK0UUyXsdguS8_bwddkzvAFL_TEXugB1NAZc/0")
}

You can see we have subsituted the key and gid parameters in the path in the endpoint. This uniquely identifies the Google Sheet we will query for results. The query string itself will be passed in from our GraphQL request.

Now is a good time to explain how the query string works with the Visualization API. When querying a Google Sheet through the Visualization API, you cannot refer to columns by their column headers. Instead you must use the Column ID: A, B, C, etc. For example, if I want to query the links spreadsheet I mentioned at the start of this article, and I want to find any row where the Title is "StepZen" my query would look like this:

SELECT * WHERE B="StepZen"

In GraphQL using the relatedlinks query from our sample above, I would need to escape the quotation marks, like so:

relatedlinks(q:"SELECT * WHERE B=\"StepZen\"")

Google has a reference for the query language available here: Query Language Reference. Keep in mind, since we're using GraphQL, any resultset from your queries must match the available columns defined in the Interface. This means aggregate queries or pivot queries will likely fail. We will continue to enhance our API to try to support more options, so if you find something you can't do, let us know.

About now you might be thinking "Writing this schema and remembering how to query it seems like a lot of work. I thought this was supposed to be easy." I'd argue that it's not that much work but it could be easier. So, anticipating this argument, we've made it a bit easier for you. We've added a way to automatically generate a schema from a spreadsheet, and we adjusted our API to allow querying by column headers instead of just column IDs in the query strings.

If you use curl to send a POST request to https://sheets.apis.stepzen.com/ and pass in JSON with a url element containing the url to your spreadsheet and a name element with the name of the interface you want to generate, we'll return a StepZen schema for your sheet. See the example below:

🐒➔ curl -X POST "https://sheets.apis.stepzen.com/"  -d '{"name":"Related Links","url":"https://docs.google.com/spreadsheets/d/1B-Qm31wtK0UUyXsdguS8_bwddkzvAFL_TEXugB1NAZc/edit#gid=0"}'

interface RelatedLinks {
  URL__A:  String
  Title__B:  String
  Keywords__C:  String
  Publish__D:  Boolean
}
type SheetsRelatedLinks implements RelatedLinks {}
type Query {
  relatedlinks(q:String!): [RelatedLinks]
  sheetsrelatedlinks(q:String!): [SheetsRelatedLinks]
    @supplies(query:"relatedlinks")
    @rest(endpoint:"https://sheets.apis.stepzen.com/1B-Qm31wtK0UUyXsdguS8_bwddkzvAFL_TEXugB1NAZc/0")
}

The output from that request can be saved directly to a file, and used with the StepZen CLI to configure StepZen to serve up a GraphQL API that reads from your sheet!

Configuring our GraphQL Sheets API in StepZen

Ok, enough talk. Let's wire up our GraphQL API.

First, using the SpreadsheetId and SheetId from my sample sheet, I'll run a curl to generate my schema file and save the output directly to a file called relatedlinks.graphql:

~/demo on
🐒➔ curl -s -X POST "https://sheets.apis.stepzen.com/"  -d '{"name":"Related Links","url":"https://docs.google.com/spreadsheets/d/1B-Qm31wtK0UUyXsdguS8_bwddkzvAFL_TEXugB1NAZc/edit#gid=0"}' > relatedlinks.graphql

~/demo on
🐒➔ cat relatedlinks.graphql
───────┬──────────────────────────────────────────────────────────────────────────────────────────────────
       │ File: relatedlinks.graphql
───────┼──────────────────────────────────────────────────────────────────────────────────────────────────
   1   │
   2   │ interface Relatedlinks {
   3   │   URL__A:  String
   4   │   Title__B:  String
   5   │   Keywords__C:  String
   6   │   Publish__D:  Boolean
   7   │ }
   8   │ type SheetsRelatedlinks implements Relatedlinks {}
   9   │ type Query {
  10   │   relatedlinks(q:String!): [Relatedlinks]
  11   │   sheetsrelatedlinks(q:String!): [SheetsRelatedlinks]
  12   │     @supplies(query:"relatedlinks")
  13   │     @rest(endpoint:"https://sheets.apis.stepzen.com/1B-Qm31wtK0UUyXsdguS8_bwddkzvAFL_TEXugB1NAZc/0")
  14   │ }
───────┴──────────────────────────────────────────────────────────────────────────────────────────────────

~/demo
🐒➔

Next, I'll create an index.graphql file that refers to our newly created schema. If you have multiple schema files you can reference them in an index.graphql file and upload them all together. In this case, we just have the one schema. The index.graphql file looks like this:

~/demo
🐒➔ cat index.graphql
───────┬──────────────────────────────────────────────────────────────────────────────────────────────────
       │ File: index.graphql
───────┼──────────────────────────────────────────────────────────────────────────────────────────────────
   1   │ schema @sdl(files: ["relatedlinks.graphql"]) {
   2   │   query: Query
   3   │ }
───────┴──────────────────────────────────────────────────────────────────────────────────────────────────

~/demo
🐒➔

Finally, using the stepzen tool I'll create my GraphQL API. I will log in to my account, upload my schema, and tell StepZen where to deploy it.

~/demo on ☁️ carlos@stepzen.com(us-central1)
🐒➔ stepzen login
What is your account name?: chico
What is your admin key?: ****************************************************************************************
You have successfully logged in.

~/demo on ☁️ carlos@stepzen.com(us-central1) took 26s
🐒➔ stepzen upload schema demo/sheets --dir=.
Uploading...
Successfully uploaded the schema

~/demo on ☁️ carlos@stepzen.com(us-central1) took 2s
🐒➔

After logging in, I issue the upload schema command. This command takes the folder and name of the schema I'm uploading, in this example that's demo/sheets. I also use the --dir switch to tell the tool where to find my schema files.

Whenever you upload content to StepZen, you provide a folder name where you want the content to go. These folders are account-scoped, so you don't need to worry about naming collisions with other accounts. When I'm exploring using StepZen, I find it easiest to stick with a simple generic name for everything, e.g. demo or helloworld, sort of like a project name. You don't have to do that, though. And you can refer to schemas from any of the folders you have when deploying your API. So you may want to organize schemas under a "schemas" folder, and configurations under a "configurations" folder, and mix and match between those when deploying your APIs. The tool supports a great deal of flexibility. I would recommend keeping it simple at the start, just so it's easy to remember everything.

Now that my schema is uploaded under the name sheets in a folder named demo I can deploy that schema to an endpoint under my StepZen account. The endpoint will look like https://{account}.stepzen.net/{folder}/{deploymentname}. Much like schemas, deployments can be in folder names. I could have a folder named prod for all my "production" deployments. Or maybe I want to separate my APIs by project or application, and I use those names for my folders. The folder name for a deployment does not need to be the same as the name for the schema deployed there. Again, lots of flexibility, but when I'm experimenting I like to keep things simple and use the same folder name for everything.

~/demo
🐒➔ stepzen deploy demo/sheets --schema=demo/sheets
Deploying...
Redeployment successful!
Your endpoint can be found at: https://chico.stepzen.net/demo/sheets/__graphql

~/demo
🐒➔

If my deployment is successful, the stepzen tool will tell me where I can call my new GraphQL API.

Finally, we can test our API:

Now we have a GraphQL API that queries a Google Sheets doc. All we needed to do was generate our SDL with some StepZen directives and deploy it. Serverless, no-code GraphQL APIs!

Putting it all Together

Ok, now that we have our GraphQL API, let's whip up a simple app to demonstrate the api. I'm going to quickly build a simple website using Next.js. I'll also build a React component to display the links. If you aren't too familiar with Next.js, don't worry. I'm not either! This will be a "copy/paste" development effort. Just follow along and you'll end up with a working demo at the end. The only prerequisites to completing everything here are Node.js 10.13 or later, and a StepZen alpha account³.

Building the Sample App

First, let's wire up a new Next.js app:

~
🐒➔ md demo ; cd $_

~/demo
🐒➔ npx create-next-app related-links
npx: installed 1 in 1.114s
Creating a new Next.js app in /home/carlos/demo/related-links.

Installing react, react-dom, and next using npm...
**** lots of npm stuff happens ****
Success! Created related-links at /home/carlos/demo/related-links
Inside that directory, you can run several commands:

  npm run dev
    Starts the development server.
**** more instructions ****

~/demo
🐒➔

Now change into the new app directory and let's build out our sample app. We're going to use material-ui for some UI elements and graphql-react and next-graphql-react to query our API, so let's install those packages...

~/demo
🐒➔ cd related-links

~/demo/related-links
🐒➔ npm install graphql-react next-graphql-react @material-ui/core
**** npm stuff happens ****
~/demo/related-links
🐒➔

To use next-graphql-react we need to decorate the app component with the withGraphQLApp component. See the Next.js example for more details.² All we need to do with our sample app is edit the _app.js file (note the leading underscore) in the pages directory to match the following:

import '../styles/globals.css'
import { GraphQLProvider } from 'graphql-react'
import { withGraphQLApp } from 'next-graphql-react'

function MyApp({ Component, pageProps, graphql }) {
  return (
  <GraphQLProvider graphql={graphql}>
    <Component {...pageProps} />
  </GraphQLProvider>
  )
}

export default withGraphQLApp(MyApp)

Now that our app component is ready to use GraphQL, let's create our related links component. This is the component that will display the list of links on any page where we want. In the main application directory, create a directory called components. The components directory is where will put code for our new component. Create a file called relatedLinks.js in the components directory with the following content:

import React from 'react'
import { useGraphQL } from 'graphql-react'
import { List, ListItem, ListItemText, ListSubheader } from '@material-ui/core'

function RelatedLinks({ keyword, limit }) {
    let queryKeyword = keyword.toLowerCase()
    const { loading, cacheValue: { data } = {} } = useGraphQL({
        fetchOptionsOverride(options) {
            options.url = `${process.env.NEXT_PUBLIC_API_URL}`
            options.headers.Authorization = `apikey ${process.env.NEXT_PUBLIC_STEPZEN_APIKEY}`
        },
        operation: {
            query: /* GRAPHQL */`
            {
                relatedlinks(q: "select * where Keywords__C contains \\"${queryKeyword}\\" and Publish__D = TRUE limit ${limit}") {
                    Title__B
                    URL__A
                }
            }
            `,
        },
        loadOnMount: true,
        loadOnReload: true,
        loadOnReset: true,
    })
    let list = data ?
        (
            data.relatedlinks.map((v, i) => {
                return (
                    <ListItem button key={"related_" + i}>
                        <ListItemText><a href={v.URL__A}>{v.Title__B}</a></ListItemText>
                    </ListItem>
                )
            })
        ) : loading ? (
            <ListItem><ListItemText>Loading...</ListItemText></ListItem>
        ) : (
            <ListItem><ListItemText>Error!</ListItemText></ListItem>
        )
    return (
        <List disablePadding dense subheader={
            <ListSubheader>
                {keyword} Links
        </ListSubheader>}>
            {list}
        </List>
    )
}

export default RelatedLinks

If you're not that familiar with React, that's OK. In this component, we've created a function that accepts keyword and limit parameters and outputs HTML that defines a list.

The data for the list comes from a variable named data that's populated with a call to useGraphQL from graphql-react. In that call we've defined a query that will retrieve at most five links where the Publish column is true and the Keywords column contains the keyword from our select list on the main page. Also note we're making use of env vars to configure the GraphQL API URL and our API key. To run this yourself and use your StepZen credentials, you'll need a file named .env.local in your application directory with your StepZen endpoint URL and your StepZen API key like so:

NEXT_PUBLIC_API_URL={API URL}
NEXT_PUBLIC_STEPZEN_APIKEY={STEPZEN API KEY}

We've got one last step before we can run our sample and see the component. Clean out the boilerplate code that is in pages/index.js and replace it with this code:

import React, { useState } from 'react'
import styles from '../styles/Home.module.css'
import RelatedLinks from '../components/relatedLinks'
import Select from '@material-ui/core/Select'
import MenuItem from '@material-ui/core/MenuItem'
import FormControl from '@material-ui/core/FormControl'
import FormHelperText from '@material-ui/core/FormHelperText'

export default function Home() {

  const [keyword, setKeyword] = useState('GraphQL')
  const handleChange = (event) => {
    setKeyword(event.target.value);
  };
  return (
    <div>
      <main>
        <div className={styles.grid} >
          <div className={styles.card}>
            <RelatedLinks keyword={keyword} limit="5" />
          </div>
          <FormControl className={styles.card}>
            <h4>keyword &darr;</h4>
            <Select
              id="simple-select"
              value={keyword}
              onChange={handleChange}
            >
              <MenuItem value={"GraphQL"}>GraphQL</MenuItem>
              <MenuItem value={"Serverless"}>Serverless</MenuItem>
              <MenuItem value={"React"}>React</MenuItem>
              <MenuItem value={"HiFi"}>HiFi</MenuItem>
            </Select>
            <FormHelperText>Select a Keyword</FormHelperText>
          </FormControl>
        </div>
      </main>
    </div>
  )
}

In this code we wire up our RelatedLinks component and a simple form with a Select control that triggers a change on our component. Now let's run our app and take a look at things.

~/demo/related-links
🐒➔ npm run dev

> related-links@0.1.0 dev /home/carlos/demo/related-links
> next dev

ready - started server on http://localhost:3000
event - compiled successfully

Open a browser to localhost:3000 and you should see something that looks like this:

The combination of GraphQL and react components makes building data-driven pages pretty slick!

Recap

Google Sheets are popular in almost every part of a business and for personal use. You can make your Google Sheets data work even harder when you can use the data that's in them in other contexts - like to power an app or an experience on your web page. We hope you found this tutorial useful and are walking away having built and deployed a GraphQL API on top of a Google Sheet in three easy steps. If you built the app you've replaced static data about your URLs, titles, keywords etc. with dynamic data via a call to your newly minted GraphQL API.

Footnotes

1: Why GraphQL

2: Next.js graphql-react examples

3: Register for StepZen alpha

4: Google Visualization API Reference