Nona Blog

Connecting your Google Sheet to an API using OAuth2 - Step by Step for noobs (like me).

A while back I built an App using Google App Scripts that needed to connect to the Xero API. One of the most frustrating parts of this process was getting my app to connect with an API which uses OAuth2 for authentication. 

There are a lot of nifty things one can do with Google App Scripts and many API’s that use oAuth2 for authentication.

If you’re looking to connect your Google Sheet or Google App Script to an API using OAuth2 and you’re struggling, here’s a step by step guide (for noobs).

STEP 1: CREATE YOUR PROJECT

  • Create a google sheet & name it.
  • In your toolbar, select ‘Tools’ → ‘Script Editor’.
  • This will open an untitled project in Google app scripts — name the project.

You should have an App Script Project linked to your Google sheet that looks like this:

Blank App Script Project Example Image | Nona
Blank App Script Project Example

STEP 2: ADD THE ‘GSUITEDEVS APPS-SCRIPT-OAUTH2’ LIBRARY TO YOUR PROJECT

I came across this library which really does all the heavy lifting for you. If you’re not a developer, like me, you’re liable to have some difficulty in getting it working.

The instructions are good however. There’s a fair amount of contextual knowledge needed to implement the library for your own purposes.

To implement the library:

  • Go to the link: https://github.com/gsuitedevs/apps-script-oauth2 we’ll call this “The Readme” going forward.
  • Follow their instructions under ‘Setup’ in the readme on Github to add the library to your script (these are straight forward). Side-note — Don’t worry about the mention of setting explicit scopes below the instructions if you’re just getting started and aren’t publishing or making your project public.
  • Get your redirect URI by following the first set of instructions below the setup instructions (don’t worry about trying to use the logRedirectUri function as this won’t work yet) Keep this redirect URI on hand, we’ll need it shortly. 
  • Don’t worry about copying over the code examples just yet, we’ll get there in a bit.

STEP 3: CREATE YOUR “APP” FOR THE API SERVICE YOU WANT TO CONNECT TO. 

I say “App” here because right now calling it an App is a bit of a stretch. Anyway.

Depending on what API you’re trying to connect to these steps might vary somewhat. I’m using the Xero API as an example but the principles should be the same for any API using OAuth2.

In order to connect to the Xero API, you first need to create an ‘App’. To do this you will need to log in and head to the Xero developer site . From here, select the ‘MyApps’ tab and then select ‘Create New App’.

When creating your app it will ask you to provide:

  1. A name which you can make up
  2. A URL (this can be the URL of to your Google Sheet)
  3. An OAuth 2.0 redirect URI — This is where you add the URI we generated in Step 2 above.

Once you’ve completed these details and ‘created’ the App, the site will provide you with a Client ID and a Client Secret, copy and store these somewhere safe! You will need them soon and you won’t be able to see or copy the secret from here again.

STEP 4 : ADDING THE CODE FROM THE README

  • Go back to your script editor screen (as seen above).
  • Delete the what is in there by default (the myFunction bit).
  • Copy the code blocks from The Readme (in the GitHub link) under the following headings:
  1. Create the OAuth2 service (Note — If you’re not connecting to Google, don’t include the bits underneath where it reads “Below are Google-specific OAuth2 parameters”)
  2. Direct the user to the authorization URL
  3. Handle the callback
  4. Get the access token
  5. Logout

Save your file, if you get an error message at this point it’s likely you’re missing a curly brace {} or a parenthesis somewhere, but it should work fine.

Now, you will notice that there are a number of mentions to “Drive” in the code you’ve copied. This is because the example code you’ve copied across is for the use case of connecting to Google Drive. If you’re connecting to something else however this doesn’t make sense (it will still work but it will get confusing). As a result you’ll want to update these references for the services you’re looking to use. In my case that was Xero so I changed all references of “driveService” to “xeroService” or “getDriveService” to “getXeroService”. 

Once you’ve done this you’ll notice:

// Set the client ID and secret, from the Google Developers Console. 
.setClientID('...')
.setClientSecret('...')

Replace the …’s with the clientID and clientSecret you saved earlier. 

Similarly, assuming you’re not connecting to Google, you’ll need to change the Authorisation URL and Token URL in the code (they have used the Google ones in the example as seen below)

// Set the endpoint URLs, which are the same for all Google services. 
.setAuthorisationBaseUrl('https://accounts.google.com/o/oauth2/auth')
.setTokenUrl('https://accounts.google.com/o/oauth2/token')

You will generally find these URL’s in the API documentation of the service you’re trying to connect to. 

In the case of Xero, you’ll find these under the OAuth2 section of the documentation on the page titled, the code flow. This page will provide you with the token url (exactly as you need to enter it into your code).

https://identity.xero.com/connect/token

The example Authorisation URL however, as seen below is more than you need though. They give this example:

//Your app should direct users to the following url:
https://login.xero.com/identity/connect/authorize?response_type=code&client_id=YOURCLIENTID&redirect_uri=YOURREDIRECTURI&scope=openid profile email accounting.transactions&state=123

This URL example is an example of a FULL URL that your APP needs to direct users to. In the code you’ll notice that it asks for the Authorization BASE URL. This is the bit up to and including the word “authorize”. The remainder of the Auth URL will be generated by the code when it runs.

So, in the case of Xero we’d only want to add:

https://login.xero.com/identity/connect/authorise

Replace the Authorization and Token URL’s. 

Lastly, you’ll need to set your scopes where you see: 

//Set the scopes to request (space-separated for Google services). 
.setScope('https://www.googleapis.com/auth.drive')

Again, you’ll need to check the API documentation for what the specific API expects as scopes but in the case of Xero, you can see what the various scopes you can add at: https://developer.xero.com/documentation/oauth2/scopes

Let’s say you want to add the scopes for “accounting.transactions” and “accounting.transactions.read” your updated scopes in your code would look like this: 

// Set the scopes to request (space-separated for Xero Services).
.setScopes('accounting.transactions accounting.transactions.read')

STEP 5: TEST IT OUT

Now you’re ready to test the setup. To do this you need to assign the function in your spreadsheet. 

  • Go back to your Google Sheet and in your toolbar select Insert > Drawing. Draw a shape to your liking and insert it into the sheet.
  • Right click on the shape and click on the three vertical dots that appear in the top right corner of the shape this will bring up a menu. Select ‘Assign Script’ from this menu.
  • In the pop up write (or copy from your code) “showSidebar”.

With the function assigned, click on the shape and the showSidebar function should run. This should cause a sidebar to pop up with the word “Authorize” as a link. Click on the link and you’ll be prompted to grant the app access to the API. 

Once completed, you’ll be prompted to close the sidebar. 

That’s it. With all this done you can continue on your quest to do some cool stuff. 

A caveat in closing —  it’s always highly advisable to read the manual or RTFM as we like to say. As such, while I hope you find this helpful, take the time, do some reading & try to understand what and how you’re doing things, it’ll make your life much easier in the long run. 

Thanks for reading. 

Nona helps funded businesses accelerate their software projects. If you’d like to soundboard your tech project or your development team, book a consultation with us and we can chat through it! 

Jared Davies-Coleman

Jared Davies-Coleman

Commercial Director - Nona

Add comment