Build a GraphQL API from Google Sheets

How to use Google Sheets as a data source for your GraphQL API

With StepZen, you can connect to a Google Sheet as a data source for your GraphQL API. For now, we generate the field names by introspecting a Google Sheet.

Part 1: Read from a Google Sheet

Let's get started by looking at a publicly available Movies Sheet, which includes movies available for streaming:

View the Google Sheet.

It's worth noting that you can use your own sheet, but the permissions have to be set for sharing so that "anyone on the internet with this link can view."

Generate a unique GraphQL asset for this sheet

Let's start by creating a working directory for our project:

mkdir sheets
cd sheets

Next, let's generate the StepZen GraphQL code using introspection:

curl -s -X POST https://sheets.apis.stepzen.com/ -d '{"name":"Movie","url":"https://docs.google.com/spreadsheets/d/1V6LvoyepIAPTWBiP8CNulv6REy9z0kPjK5yLP2omUiI/edit#gid=2134244083"}'

The value of name determines the name of the GraphQL type that will be generated from this sheet. The value of url is the sheet URL (with the ID of the specific sheet). We can typically get this by opening the Google Sheet to the specific sheet we want to generate against and copying the URL from the browser bar.

The command will output the code for the .graphql file in the console. It will look something like the following:

type Movie { 
  ID__A:  Float 
  Title__B:  String 
  Year__C:  Float 
  Age__D:  String 
  IMDb__E:  Float 
  RottenTomatoes__F:  Float 
  Netflix__G:  Float 
  Hulu__H:  Float 
  PrimeVideo__I:  Float 
  Disney__J:  Float 
  Type__K:  Float 
  Directors__L:  String 
  Genres__M:  String 
  Country__N:  String 
  Language__O:  String 
  Runtime__P:  Float 
}
type Query {
  movie(q:String! = ""): [Movie]
    @rest(endpoint:"https://sheets.apis.stepzen.com/1V6LvoyepIAPTWBiP8CNulv6REy9z0kPjK5yLP2omUiI/2134244083")
} 

Copy this response into a file movie.graphql. Then create an index.graphql, which is required by StepZen, to list the files that make up our schema.

schema @sdl(files: ["movie.graphql"]) {
    query: Query
}

Deploy the GraphQL Endpoint to StepZen

Let's deploy and test our GraphQL API.

stepzen start

We'll be asked to give your API a name in the format of [FOLDER_NAME]/[ENDPOINT_NAME]. Feel free to use the default name provided.

Our GraphQL API is live. Let's paste the following query into the query explorer that stepzen start opened in our browser.

{
  movie(q: "select * where Year__C = 1980") {
      Title__B
  }
}

You should see something like:

{
  "data": {
    "movies": [
      {
        "Title__B": "Urban Cowboy"
      },
      {
        "Title__B": "Popeye"
      },
      {
        "Title__B": "Playing for Time"
      },
      ...
    ]
  }

As you can see, Google Sheets accepts queries that look somewhat like SQL. However, the syntax requires column identifiers, not column names. We have made it easier for you by accepting column names that we internally translate to the column identifier (this is why we have column identifiers in the column names in the generated GraphQL). For a reference on other queries, see Query Function.

Run it against your own Google Sheet

Make sure that you have turned link sharing to Anyone on the internet with this link can view. Use the link to your sheet in the POST call above to generate the GraphQL code, then follow the steps above.

curl -s -X POST https://sheets.apis.stepzen.com/ -d '{"name":"[TYPE_NAME]","url":"[SHEET_URL]"}

Now you can mix and match your sheets data with any other backend that StepZen supports.

Part 2: Write to a Google Sheet

Make a New Sheet

Now, let's learn to write to a Google Sheet! For this part, you'll be using your own Google Sheet. Hit https://sheets.new to create one. Enter some data in the first row, simple names, no spaces for now. In this tutorial, we'll be using "tape", "staples", and "glue" for the values of columns A, B, and C:

google sheet with three columns

Grant Access to the Sheet

Turn on link sharing and set viewing rights open to anyone with the link:

  • Click the green "Share" button in the upper right
  • On the subsequent pop-up, there is a section headed “Get link” … in that section, click the “Change link…” link (bottom left).
  • Then on the resulting dialog select “Anyone with the link” in the dropdown.
  • Click "Done".

Create a Google Service Account

To support mutations for the sheet, you need to take a few more steps.

Since we don’t support the OAuth flows yet, you need to create a service account, download the service account json file, grant access to the sheet for that service account, and then tell StepZen about the service account.

You will only need to do it once.

First, log into https://console.cloud.google.com. Free trial should work fine, if you don’t have one already.

  • Next, in the search bar at the top of the Google Cloud Platform console type “Service Accounts”.
  • Select the entry called “Service Accounts”.
  • Click the “+ Create service account” link at the top.
  • Give the service account any name.
  • Click the create button then click the done button (we don’t need to grant any project or user access).

Create a JSON Key File For Your Account

In this step we create a JSON keyfile for this service account.

You’ll download this to your computer and use it later.

  • Now click on the service account you just created in the list of service accounts.
  • It will be something like “accountname@projectid.iam.gserviceaccount.com” – Under the KEYS tab on the resulting screen, click the Add Key button and select “Create New Key”
  • Leave the default selected (JSON) and click CREATE.

A JSON file will be saved to your computer. We’ll use this file later when configuring StepZen to do support mutations on the sheet, so don’t lose it.

Give Your Service Account Access to the Google Sheet

Now, you'll need to give your service account access the Google Sheet you created.

  • Click the DETAILS tab for the service account, and copy the Email value. (It’s just a regular email address).
  • Now on your Google Sheet, grant Editor access to the email address for your service account.
  • Click the green Share button on the sheet, and paste in the email adress you copied, setting editor rights.

Now that service account can write to your sheet! Hooray.

Ok, now for the StepZen parts. First we will generate a schema for our spreadsheet, then we’ll upload it to StepZen, etc.

Generate Your Schema

To generate a schema, issue a POST request like so, passing in the url of your spreadsheet instead of the one used below.

Also change the “name” to be something you like (it’s used in the schema that gets spit out):

curl -s -X POST "https://sheets-generator-v2-zlwadjbovq-uc.a.run.app" -d '{"name":"testingsheets","url":"https://docs.google.com/spreadsheets/d/1nOkOqs7TdzKeJTON7okp8PIgGa9OOD_d1w_1gPn2Vcw/edit#gid=0"}'

Save the results as a *.graphql file somewhere, since you’ll use 'em in just a bit.

In the same directory, you'll need an index.graphql file with

schema @sdl(files: ["{{your_generated_graphql_file_name_here}}.graphql"]) {
  query: Query
}

This will tell StepZen which files comprise your schema.

Add Authorization to Your Schema

You’ll see in the mutation in the schema that we define a configuration for the mutation to use.

So create a config.yaml file like so.

configurationset:
  - configuration: 
      name: {{name_from_schema}}
      authorization: Bearer {{Big string}}

{{Big string}} is the base64-encoded contents of the json file you downloaded earlier. To get that you execute a command like so in a terminal:

🐒➔ base64 -w 0 ~/dev-carlos-sa.json

but replace ~/dev-carlos-sa.json with the path to the file you downloaded. This will spit out a big string that ends with == Copy that big string and put it into your config.yaml file.

Now deploy to Step Zen and you should be good to roll!

Issuing a command to write to your schema

Since you've deployed to StepZen, you've got a StepZen GraphiQL browser sitting on your localhost:5000 port with your selected name.

Open it up and run:

mutation {
  addTestingsheets2(glue__C: "10", staples__B: "6", tape__A: "5") {
   	staples__B
    tape__A
    glue__C }
}

Your response will be:

{
  "data": {
    "addTestingsheets2": {
      "glue__C": "10",
      "staples__B": "6",
      "tape__A": "5"
    }
  }
}

Now check out your Google doc!

google-sheet-with-new-values

You've written to it, using only GraphQL!

If you run into any issues, please contact us on our Discord server, via the site chat, or filing an issue on Github.

This site uses cookies: By using this website, you consent to our use of cookies in accordance with our Website Terms of Use and Cookie Policy.