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. The following steps in this tutorial show how to generate the field names by introspecting a Google Sheet:

  1. Read from a Google Sheet
  2. Write to a Google Sheet

Read from a Google Sheet

Start by looking at the publicly-accessible Movies Sheet that includes movies available for streaming:

View the Google Sheet

Note: 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.

Perform the steps in the following subsections to read from a Google Sheet:

  1. Generate a Unique GraphQL Asset for this Sheet
  2. Deploy the GraphQL Endpoint to StepZen
  3. Run it Against your own Google Sheet

Generate a Unique GraphQL Asset for this Sheet

Follow the steps below to create a working directory for your project:

  1. Create the directory:
mkdir sheets
  1. Navigate into that directory:
cd sheets
  1. 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"}'
  • name specifies the name of the GraphQL type that will be generated from this sheet.
  • url is the sheet URL (with the ID of the specific sheet).

You can typically get these values by opening the Google Sheet to the specific sheet you 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, similar to 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")
}
  1. Copy this response into a file movie.graphql and create an index.graphql to list the files that make up the schema. The index file is required by StepZen.
schema @sdl(
  files: [
    "movie.graphql"
  ]
) {
  query: Query
}

Deploy the GraphQL Endpoint to StepZen

Deploy and test your GraphQL API:

stepzen start

When asked to give your API a name in the format of [FOLDER_NAME]/[ENDPOINT_NAME], you can use the default name provided.

Your GraphQL API is live. Paste the following query into the Query Explorer that stepzen start opened in your browser:

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

You should see output similar to the following:

{
  "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 simplified the process by accepting column names that we internally translate to the column identifier.

Note: This is why there are column identifiers in the column names of the generated GraphQL. For a reference about other queries, see Google's Query Function.

Run it Against your own Google Sheet

Ensure that you have set link sharing to Anyone on the internet with this link can view. Use the link 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 backend that StepZen supports.

Write to a Google Sheet

Follow the steps in the subsections below to write to a Google Sheet:

  1. Make a New Sheet
  2. Grant Access to the Sheet
  3. Create a Google Service Account
  4. Create a JSON Key File For Your Account
  5. Give Your Service Account Access to the Google Sheet
  6. Generate Your Schema
  7. Add Authorization to Your Schema
  8. Issue a command to write to your schema

Make a New Sheet

Let's learn to write to a Google Sheet! For this part, you'll use your own Google Sheet:

  1. Click https://sheets.new to create one.
  2. Enter some data in the first row. Use simple names with no spaces for now. For this tutorial, use tape, staples, and glue for the values of columns A, B, and C:

google sheet with three columns

Grant Access to the Sheet

Enable link sharing and set viewing rights so it's available to anyone with the link:

  1. Click the green Share button at the upper right.
  2. Click Change link... at the bottom left of the subsequent pop-up, under the section with the heading Get link....
  3. Select Anyone with the link in the dropdown on the resulting dialog.
  4. 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:

  1. Create a Service Account
  2. Create a JSON Key File for your Account
  3. Give your Service Account Access to the Google Sheet
  4. Generate your Schema
  5. Add Authorization to your Schema
  6. Issue a Command to Write to your Schema

Note: You only need to do this process once.

Create a Service Account

  1. Log into https://console.cloud.google.com. The free trial will work fine if you don’t have one already.
  2. Enter Service Accounts in the search bar at the top of the Google Cloud Platform console.
  3. Select the entry called Service Accounts.
  4. Click the + CREATE SERVICE ACCOUNT link at the top.
  5. Give the service account any name.
  6. Click the CREATE AND CONTINUE button, then click the DONE button.

Note: You don’t need to grant any project or user access.

Create a JSON Key File for your Account

In this step, you will create a JSON keyfile for your service account.

You must download this to your computer and use it later:

  1. Click on the service account you just created, from the list of service accounts. It will look similar to accountname@projectid.iam.gserviceaccount.com.
  2. Click the ADD KEY button under the KEYS tab on the resulting screen and select Create new key.
  3. Leave the default selected (JSON) and click CREATE.

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

Give your Service Account Access to the Google Sheet

Follow the steps below to give your service account access to the Google Sheet you created:

  1. Click the DETAILS tab for the service account and copy the Email value. Note: It’s just a regular email address.
  2. Grant Editor access on your Google Sheet, to the email address for your service account.
  3. Click the green Share button on the sheet, and paste in the email address you copied, setting editor rights.

Now the service account can write to your sheet.

Generate your Schema

Now for the StepZen parts. First, generate a schema for your spreadsheet and then upload it to StepZen, etc.

Follow the steps below to generate a schema:

  1. Issue a POST request, passing in the URL of your spreadsheet instead of the one used below.
  2. Change the name to something you like. This is used in the schema that gets generated:
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"}'
  1. Save the results as a *.graphql file somewhere, since you’ll use them in just a bit.

  2. Add a index.graphql file with the following in the same directory:

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

This tells StepZen which files comprise your schema.

Note: The Mutation in the schema defines a configuration for the Mutation to use.

Add Authorization to your Schema

Follow the steps below to add authorization to your schema:

  1. Create a config.yaml file similar to the following:
configurationset:
  - configuration:
      name: {{name_from_schema}}
      authorization: Bearer {{Big string}}

{{Big string}} is the Base64-encoded contents of the JSON file you downloaded earlier. Execute the following command replacing ~/dev-user-sa.json with the path to the file you downloaded. This will generate those contents as a big string that ends with ==:

base64 -w 0 ~/dev-user-sa.json
  1. Copy that big string and put it into your config.yaml file.
  2. Deploy to StepZen and you should be good to go!

Issue a Command to Write to your Schema

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

  1. Open it up and run:
mutation {
  addTestingsheets2(glue__C: "10", staples__B: "6", tape__A: "5") {
   	staples__B
    tape__A
    glue__C }
}

The response will be:

{
  "data": {
    "addTestingsheets2": {
      "glue__C": "10",
      "staples__B": "6",
      "tape__A": "5"
    }
  }
}
  1. 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 by 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.