Join data from Google Sheets API and Postgres using Hasura Remote Joins
Tags: hasura, remotejoins, googlesheets, graphql
Published: 2019-12-23 06:30:05

---
title: Join data from Google Sheets API and Postgres using Hasura Remote Joins
published: true
date: 2019-07-30 06:02:36 UTC
tags: hasura, remote joins, google sheets, graphql
canonical_url: https://blog.hasura.io/join-data-from-google-sheets-api-and-postgres-using-hasura-remote-joins/
---

## TL;DR

Use Hasura Remote Joins to join data from your Postgres database with Google Sheets API in GraphQL. Boilerplate -\> [Google Sheets](https://github.com/praveenweb/google-sheets-remote-schema)

![](https://blog.hasura.io/content/images/2019/07/google-sheets-cover-image.png)

This post is a part of our [Remote Joins](https://blog.hasura.io/remote-joins-a-graphql-api-to-join-database-and-other-data-sources/) (available in preview) series. Remote Joins in Hasura allows you to join data across tables and remote data sources. You can sign up [here](https://hasura.io/remote-joins) if you'd like to be notified when we launch. Head to [the PR](https://github.com/hasura/graphql-engine/pull/2395) to check out more details, read the preview docs, and to try out a test Hasura image that you can spin up. Jump on our [discord](https://discord.gg/hasura) or comment on [github](https://github.com/hasura/graphql-engine) and let us know what you think!

In this example, we will look at how data from Google Sheets API can be joined with existing data in Postgres using Hasura Remote Joins. Let's say there is a `users` table with columns `id`, `name`, `email`. There is a Google Sheet, with a list of email IDs mapped to URLs. Now we would like to query users data along with the url data in Google Sheets mapped against each user's email id. Let's see how this can be done using a custom resolver.

![](https://blog.hasura.io/content/images/2019/07/google-sheets-example.png)

## Adding Google Sheets API as Remote Schema

To be able to query Google Sheets data via Hasura, it needs to be added as a `Remote Schema` using the Hasura Console.

### Deploy Custom Resolver
[![](https://raw.githubusercontent.com/hasura/graphql-engine/master/community/boilerplates/auth-webhooks/nodejs-express/assets/deploy-glitch.png)](http://glitch.com/edit/#!/import/github/praveenweb/google-sheets-remote-schema)

Add the following environment variables in the `.env` file on glitch.

```
GOOGLE_SHEETS_API_KEY=xxx
SPREADSHEET_ID=xxx
PORT=3000
```

- Get the Google Sheets API Key by visting the [Authorize Requests Sheets API](https://developers.google.com/sheets/api/guides/authorizing#APIKey) page.
- Set the API key as `GOOGLE_SHEETS_API_KEY` environment variable.
- We need to input the spreadsheet id from where we are going to fetch data. Set that as `SPREADSHEET_ID` environment variable. We can find the sheet ID in the url.
- This custom resolver is used to accept a user's email and will return a filtered list of data pertaining to that user.

Get the GraphQL API Endpoint from Glitch and add it as remote schema.

![](https://blog.hasura.io/content/images/2019/07/google-sheets-remote-schema.png)

Now let's add the Remote Relationship called `sheets`

![](https://blog.hasura.io/content/images/2019/07/remote-relationship-google-sheets.png)

Now the GraphQL query to fetch this data in a single API call would look like the following:

```
query {
  users {
    id
    name
    email
    sheets {
      urls
    }
  }
}
```

Notice that, the nested query `sheets` come from Google Sheets API and it will apply the filter of users.email = sheets.author, there by only giving data about the current user.

Checkout our other posts in the Remote Joins series:

- [Building a Music Playlist app with Gatsby, Contentful](https://blog.hasura.io/building-a-music-playlist-app-with-gatsby-contentful-auth0-hasura/)
- [Using GraphQL to join data across Google Places API and Postgres](https://blog.hasura.io/graphql-join-data-google-places-api-postgres-postgis-location/)
- [Build apps with rich content using Contentful and Postgres](https://blog.hasura.io/build-apps-with-rich-content-contentful-hasura-remote-joins/)

Remote Joins opens up a lot of exciting usecases and these boilerplates are simple examples to explore the possibilities.