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:

  1. Read from a Google Sheet.
  2. Generate a unique GraphQL asset for this sheet.
  3. Deploy the GraphQL Endpoint to StepZen.
  4. Run it against your own Google Sheet.
  5. Write to a Google Sheet.
  6. Make a New Sheet.
  7. Grant Access to the Sheet.
  8. Create a Google Service Account.
  9. Create a JSON Key File For Your Account.
  10. Give Your Service Account Access to the Google Sheet.
  11. Generate Your Schema.
  12. Add Authorization to Your Schema.
  13. Issue a command to write to your schema.

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.

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

    We can typically get these values 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 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. Then create an index.graphql to list the files that make up our schema. The index file is required by StepZen.
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 simplified the process by accepting column names that we internally translate to the column identifier.

Note: This is why we have column identifiers in the column names in 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 turned 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

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.

  1. Hit 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, we'll use 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 so it's available to anyone with the link:

  1. Click the green Share button in the upper right.
  2. On the subsequent pop-up, there is a section with the heading Get link.... Click Change link... (bottom left).
  3. Then on the resulting dialog select Anyone with the link in the dropdown.
  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.

You will only need to do this process once.

Create a Service Account

  1. Log into https://console.cloud.google.com. The free trial should work fine, if you don’t have one already.
  2. In the search bar at the top of the Google Cloud Platform console, type Service Accounts.
  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: 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.

  1. Click on the service account you just created, from the list of service accounts. It will be something look similar to accountname@projectid.iam.gserviceaccount.com.
  2. Under the KEYS tab on the resulting screen, click the ADD KEY button and select Create new key.
  3. 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.

  1. Click the DETAILS tab for the service account, and copy the Email value. Note: It’s just a regular email address.
  2. On your Google Sheet, grant Editor access 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! Hooray.

Generate your Schema

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

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 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"}'
  1. Save the results as a *.graphql file somewhere, since you’ll use them in just a bit.

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

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

This will tell StepZen which files comprise your schema.

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

Add Authorization to your Schema

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. To get that, execute a command similar to the following in a terminal:

🐒➔ base64 -w 0 ~/dev-carlos-sa.json
  1. Ensure you replace ~/dev-carlos-sa.json with the path to the file you downloaded. This will spit out a big string that ends with ==.
  2. Copy that big string and put it into your config.yaml file.
  3. Now deploy to StepZen and you should be good to roll!

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