How to integrate with the Google Sheets API, in 5 minutes
Tags: pizzly, javascript, node, webdev
Published: 2020-06-18 14:14:27

---
title: How to integrate with the Google Sheets API, in 5 minutes
published: true
description: In this tutorial, learn how to use Pizzly, an open source project, to integrate easily with the Google Sheets API. 
tags: #pizzly #javascript #node #webdev
cover_image: https://dev-to-uploads.s3.amazonaws.com/i/d3wkndp9pfpd6taavdja.jpg
---

A few months ago, I wrote an [article on how to send data to a Google spreadsheet in 2 seconds](https://dev.to/bearer/how-to-send-data-to-a-google-spreadsheet-in-2-seconds-1h0). It got tons of views and likes. Almost a thousand people have used it to send data with over 300K API requests in the last 30 days 😱 🚀

Recently, [Bearer](https://www.bearer.sh) has decided to open-source a large part of its codebase into a GitHub repository codenamed **[Pizzly](https://github.com/Bearer/Pizzly)**. That part of the codebase was the foundation of what made "Push to GSheet" (as we called it) so easy to build and use.

Today, I'd like to show you how you can build your very own Google Sheets integration.

Here's a quick demo 👇

{% codepen https://codepen.io/frenchcooc/pen/MWKbQqj %}
<figcaption>Click on "Save to Google Sheets" to save the table in a new spreadsheet</figcaption>

You can see the full source code [by opening the CodePen](https://codepen.io/frenchcooc/pen/MWKbQqj), but I'd like to focus on some key parts below to help you build your very own API integration.

# Setup your Pizzly 🐻

[Pizzly](https://github.com/Bearer/Pizzly) is a self-hosted API integration toolkit. It provides everything a developer needs to make it easy  to consume an OAuth-based API (_aka an API that uses OAuth as the authentication method_).

Most APIs now use the [OAuth framework](https://oauth.net/) to authorize an application that wants to access some user's data. One of the main reasons, I believe, is that OAuth provides the best user experience while being very secure. But let's be honest: it's much more difficult for a developer to implement and use.

> _When no user's data are involved, API keys are preferred as they are much easier to implement and use. For example, Stripe or Google Maps use an API Key. But Facebook, Twitter, or Google Sheets use OAuth._

Pizzly aims to provide the best developer experience when using an OAuth-based API. One of its power-features is that it totally handles the OAuth-dance (including the refresh token), meaning a developer can focus on requesting endpoints without spending hours on dealing with authentication. 

Let's see how to use it.

## Installing Pizzly

Pizzly is a self-hosted solution. This means you can install it on your machine or any platform-as-a-service. We are goning to use [Heroku](https://www.heroku.com) for this example as it's a free and well-known solution. But you should be able to use it on AWS, Google Cloud, etc.

First, **deploy Pizzly to Heroku** by clicking on the button below:

[![Deploy to Heroku](https://camo.githubusercontent.com/83b0e95b38892b49184e07ad572c94c8038323fb/68747470733a2f2f7777772e6865726f6b7563646e2e636f6d2f6465706c6f792f627574746f6e2e737667)](https://heroku.com/deploy?template=https://github.com/Bearer/Pizzly)

You might need to sign in or create an account on Heroku. Go ahead, it's free. When prompted, provide a name for your app then scroll down and click on "Deploy app". It will take around 2 or 3 minutes to deploy.

![Pizzly ready to be deployed on Heroku](https://dev-to-uploads.s3.amazonaws.com/i/now1zj5ocjv5oyn3nxw9.png)
<figcaption>Pizzly ready to be deployed on Heroku</figcaption>

Once it's deployed, open the app and you should see something like this:

![Pizzly dashboard welcome screen](https://dev-to-uploads.s3.amazonaws.com/i/67vxd20tyqwftx427vb2.png)
<figcaption>Pizzly dashboard welcome screen</figcaption>

It means that your Pizzly instance is up and running 🎉 As a short overview, Pizzly provides multiple tools to help you with your integrations, including:

* a dashboard - _where you are right now_;
* an API - _to programmatically do what you can do with the dashboard_;
* an `/auth/` service - _to handle the OAuth-dance_;
* a proxy - _to make authenticated requests to an API_;
* a JS library - _to connect a user and request an API from your application_.

This is just to give you an overview. You can learn more about each on [the Pizzly wiki](https://github.com/Bearer/Pizzly/wiki), but for now let's get back to connecting with Google Sheets.

## Configuring the Google Sheets integration

Now, let's configure your Google Sheets integration with Pizzly. From your Pizzly instance, click on "Open the dashboard", then head to the Google Sheets API and click on "Create your first configuration".

Here's a GIF with the different steps to follow:

![Adding the Google Sheets API on Pizzly](http://g.recordit.co/UGVMFJrMLB.gif)
<figcaption>Open Dashboard > Add a new API > Google Sheets > Create your first configuration</figcaption>

You should now see something similar to this:

![Configuring a Google Sheets integration with Pizzly](https://dev-to-uploads.s3.amazonaws.com/i/in0bjvvfep8xsery23s2.png)

This is where you will save the credentials of your Google Sheets application.

If you already have them, fill in the form with your Client ID, Client Secret and the following scope: `https://www.googleapis.com/auth/drive.file`. Make sure that you've registered on the Google Cloud console the callback URL provided by Pizzly. Then skip the next section.

> _You can use any scopes with Pizzly. I'm providing this one to make sure the snippets below will work with your instance. But if you want to use another scope, feel free!_

If you don't have a Google Sheets application yet, stay with me. In the next section, we will see how to create one.

## Creating a Google Sheets application 🔑

We'll work through the steps needed to retrieve your credentials. The process is as follows:

1. [Sign in](https://console.cloud.google.com/home/dashboard)  to the Google Cloud Console;
   ![Google Cloud console homepage](https://blog.bearer.sh/content/images/2019/12/googlecloud--homepage.png)
2. Create or Select an Organization and Project;
   ![Select an organization on Google Cloud console](https://blog.bearer.sh/content/images/2020/05/Dashboard_-_Home_-_Google_Cloud_Platform.png)
3. Enable the Google Sheets API;
  ![Enabling the Google Sheets API](http://g.recordit.co/HMYJon1kU7.gif)
4. Once enabled, select **Credentials** in the API manager, then **Create credentials** > **OAuth client ID**:
   ![Create OAuth credentials for the Google Sheets API](https://dev-to-uploads.s3.amazonaws.com/i/3bzuhzgz3fc56mzvghjk.png)
5. The application type is **Web application**. Make sure to add in the **Authorized redirect URIs** the callback URL provided by Pizzly on your dashboard. If you've deployed Pizzly on Heroku, it should look something like this: `https://[project-name].herokuapp.com/auth/callback`.
   ![Pizzly registered as a callback URL for Google Sheets API](https://dev-to-uploads.s3.amazonaws.com/i/2r9iip6eps0gp1bicqb7.png)
6. Back to Pizzly, save your Client ID and Client Secret. In the scope field enter `https://www.googleapis.com/auth/drive.file`
   ![Configuring the Google Sheets API in Pizzly](https://dev-to-uploads.s3.amazonaws.com/i/3yt0k63r5yiv3p7gicnw.png)

Click save and that's it! You've successfully configured an integration with Google Sheets.  

## Test your integration

To be sure that your integration is well-configured, I recommend that you try to connect yourself using Pizzly connect.

From your Pizzly's dashboard, open the Google Sheets API and click on the **Connect to Google Sheets**. This should open the following page:

![Connect to Google Sheets from Pizzly dashboard](https://dev-to-uploads.s3.amazonaws.com/i/jtmg4roy31rkszmgyddy.png)

Now, click again on **"Connect to Google Sheets"**. This will open a popup with Google's OAuth consent screen. Grant access to your application.

On success, the dashboard will prompt a **Connected!** message. It means your application is well-configured 👍 Good job!

If something went wrong, it might be because of the redirection callback URL. Make sure that you've well registered your own callback URL within the Google Cloud console.

To do so, go back to the [Google Sheets dashboard](https://console.cloud.google.com/apis/api/sheets.googleapis.com/credentials) in the Google Cloud console. Confirm you are on the right project and right organization. Edit your OAuth client ID and confirm that the Authorized redirect URIs is well-registered

![Pizzly registered as a callback URL for Google Sheets API](https://dev-to-uploads.s3.amazonaws.com/i/2r9iip6eps0gp1bicqb7.png)

If you're still in trouble, drop a comment below and I'll guide you further.

# Back to the code 👩‍💻👨‍💻

In the previous part of this article, you've managed to install Pizzly and configured a Google Sheets integration. Well done!

In this part, we will see how to connect users and make requests to the Google Sheets API. Let's go!

## Connecting users to Google Sheets 🔐

Pizzly comes with [a JavaScript library (`pizzly-js`)](https://github.com/Bearer/Pizzly/tree/master/src/clients/javascript) that includes high-level methods to interact with your instance. Here we are going to use the `.connect()` method that provides an easy way to connect a user.

```html
<script src="https://cdn.jsdelivr.net/npm/pizzly-js@v0.2.3/dist/index.umd.min.js"></script>
<script>
// Initializing Pizzly JS with a demo instance
const demoKey = "pope8Qy8qfYyppnHRMgLMpQ8MuEUKDGeyhfGCj";
const demoInstance = "https://pushtogsheet.herokuapp.com/";
const pizzly = new Pizzly(demoKey, demoInstance)

// Connecting a user
pizzly
  .connect('google-sheets')
  .then((data) => console.log("Successfully connected!", data))
  .catch((error) => console.error("It failed!", error))
</script>
```

This snippet does two things:

1. First, it initializes the Pizzly JS client using a demo instance. The `demoKey` is an optional  key that you can configure on your own instance ([learn how](https://github.com/Bearer/Pizzly/wiki/Secure-your-instance#secure-requests-to-the-api)). And `demoInstance` is just the URL of my own demo instance 😉
2. Then, and it's the most interesting part, the `.connect()` method is triggered with the API name as argument. As it's using [promises](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Promise), I can use `then`/`catch` to handle success or error.

If you run this snippet on a browser, it will open a popup that instantly prompts the authorization consent screen from Google.

![Google OAuth consent screen](https://dev-to-uploads.s3.amazonaws.com/i/dsgs0rzjitxodyflmpo2.png)

It feels a bit magical the first time that you run it (well, it felt magical for me at least 😅). A few lines of code and you can connect your users to Google Sheets. That's the power of Pizzly: focusing on the developer experience.

What's even better is that Pizzly handles the OAuth refresh sequence. If you're familiar with OAuth, you probably know how epic it is to handle token refreshes. That's something that Pizzly totally hides away ([more on that](https://github.com/Bearer/Pizzly/wiki/Reference-:-Auth)).

And what's even, even better, is that [Pizzly comes with dozens of pre-configured APIs](https://github.com/Bearer/Pizzly/wiki/Supported-APIs) (e.g. Facebook, Twitter, Salesforce, Pipedrive, etc.). This means you can create an integration with Google Sheets in a few minutes and using the same code to integrate with many others!

> _If you are curious about how it works behind the scene... When running `pizzly.connect('google-sheets')`, it opens a popup in the browser that first redirects to the Pizzly instance._
>
> _Upon receiving that request, the instance fetches the API configuration in its database and redirects as fast as it can to the consent screen_
>
> _After the user has given access, the API will redirect her/him back to the instance on what's called the `callback URL`. This is why you've to register a Pizzly URL when you configure your application._
>
> _That callback URL informs Pizzly JS if the connection succeeded (or not), then close the popup._


## Making authenticated requests to the Google Sheets API 👾

After connecting a user to the API, you can query all the API endpoints using Pizzly JS again. Here's the snippet from above slightly enhanced and made interactive:

{% codepen https://codepen.io/frenchcooc/pen/BajQZWj %}

If you click on **Create a new spreadsheet** in the previous CodePen, Pizzly JS will both connect yourself, then send a request to the Google Sheets API to create a new spreadsheet.

We already know how to connect someone, so let's focus on the request part. Here's an abstract from the previous CodePen that is responsible for the request:

```js
pizzly
  .integration("google-sheets")
  .auth(userAuthId)
  .post("/spreadsheets")
  .then((response) => response.json())
  .then((data) => {
    // Do what you want with the data
  });
```

There are three interesting parts in this snippet: 

1. First, it uses the `.integration()` method to specify which integration we are working with (`google-sheets` in this case).
2. Then, it tells Pizzly to use an `authId`. This is where the authentication happens. The `authId` is generated by Pizzly when you use `.connect()`. I'm not going to cover in detail the `authId` here, but it's a powerful concept introduced by Pizzly. [I really recommend this read to learn more about it](https://github.com/Bearer/Pizzly/wiki/Reference-:-Auth#the-authid-concept).
3. Finally, it runs `.post("/spreadsheets")` to create a POST request to the `/spreadsheets` endpoint. This is how Google Sheets API works - [You can learn more in the Google Sheets API documentation](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/create). 

> _Pizzly JS accepts all HTTP methods, including `.get()`, `.put()`, etc._

## Making authenticated from the backend 🤖

Pizzly is not tied to working from a browser. Once you've connected a user and retrieved it's `authId`, you can perform requests to the API from your servers.

Here's the same request that creates a spreadsheet, but using cURL

```bash
curl -X POST https://pushtogsheet.herokuapp.com/proxy/google-sheets/spreadsheets?pizzly_pkey=pope8Qy8qfYyppnHRMgLMpQ8MuEUKDGeyhfGCj \
 -H "Pizzly-Auth-Id: REPLACE-WITH-YOUR-AUTH-ID"
```

and the same one in Node.js:

```javascript
const axios = require("axios");

const authId = "REPLACE-WITH-YOUR-AUTH-ID";
const demoKey = "pope8Qy8qfYyppnHRMgLMpQ8MuEUKDGeyhfGCj";
const demoInstance = "https://pushtogsheet.herokuapp.com";

axios
  .post(demoInstance + "/proxy/google-sheets/spreadsheets" + "?pizzly_pkey=" + demoKey, null, {
    headers: { "Pizzly-Auth-Id": authId },
  })
  .then(({ data }) => {
    console.log(data);
    // Do what you want with the data
  })
  .catch(console.error);
```

Make sure to replace `REPLACE-WITH-YOUR-AUTH-ID` with a valid `authId`. If you're wondering how to get one, use this snippet:

```js
pizzly
  .connect('google-sheets')
  .then(({authId}) => console.log(authId))
  .catch((error) => console.error("It failed!", error))
``` 

# Conclusion

If you've read this far, first thank you! It's probably a topic that you're familiar with or want to learn more about. Feel free to add comments if anything wasn't clear enough. You can also look at [Pizzly's project on GitHub](https://github.com/Bearer/Pizzly), and especially its [wiki](https://github.com/Bearer/Pizzly/wiki).

Second, I quickly mentioned that Pizzly is open-source software (MIT License). Meaning that you can freely grab the code and [contribute to it anytime](https://github.com/Bearer/Pizzly/pulls?q=is%3Apr). OAuth is a vast topic where all developers are forced to reinvent the wheel. Let's try to make it the perfect place to handle OAuth?

Last but not least, now that you have a Pizzly instance set up, [remember to secure it](https://github.com/Bearer/Pizzly/wiki/Secure-your-instance#secure-requests-to-the-api). By default, it's unsecure to help you get started, but you shouldn't let it as is.