Print

Create a Custom OAuth Connection

If API Connector does not provide a preset OAuth2 integration for your platform, you can add your own custom OAuth2 connection. API Connector supports the two most common OAuth flows (aka grant types): Authorization Code and Client Credentials.

Contents

Before You Begin

Click here to install the API Connector add-on from the Google Marketplace.

What is OAuth 2.0?

Most APIs require some kind of authorization. Frequently this involves entering an API key or token, which function similarly to passwords. OAuth2 is an alternative authorization framework that lets a user securely connect a third party to their account.

You've probably encountered OAuth2 before. It's the technology behind most "sign in with X" authorization flows.

Why Create a Custom OAuth Connection?

  • some APIs provide access only through OAuth 2.0, and do not enable API keys/tokens.
  • access APIs or scopes not available in our preset OAuth connections.
  • keep your connection totally secure. Only authorized users can connect through the Authorization Code flow, even if they have access to your sheet.

How to Add a Custom OAuth Connection

Step 1: Configure an App

  1. To get started, you generally need to enter the developer section of your API platform and configure an "app". Some APIs may ask you to simply click a few buttons, while others will ask that you provide detailed information or a video walkthrough of the data you intend to access.
  2. During this process, the API platform usually requests that you enter a redirect or callback URL. This is the redirect URL for API Connector: https://script.google.com/macros/d/12COOkin8nodCH7fZGIBu0D2jWY8-AEA0uvElt4Ph4wRbLUD4wslqQUfG/usercallback
  3. Once you've finished setting up your app, the API should provide you with a Client ID and Client Secret.

Step 2: Prepare API Connector

After retrieving the Client ID and Secret, you can move on to setting up the connection in Google Sheets.

  1. Open Google Sheets and enter the Manage Connections screen by clicking Extensions > API Connector > Manage Connections, or by clicking the Manage Connections link on the Create screen.
    customoauth-manageconnections
  2. Once there, scroll to the bottom and click Add Custom OAuth:
    customoauth-add
  3. You will be presented with a screen containing input values for OAuth Grant Type, Name, Authorization Base URL, Token URL, Client ID, and Client Secret:
    customoauth-addcustom

Step 3: Complete OAuth Form

Populate the input fields in the OAuth form as follows:

  • OAuth Grant Type: Choose whether you're using the Authorization Code or Client Credentials Flow.
  • Name: Choose a name for your custom connection and enter it in, e.g. "Custom BigQuery".
  • Authorization Base URL: This should be provided within the API's developer documentation, and usually contains the word "auth" or "oauth" somewhere in the URL. If required, include the scope parameter here.
    The OAuth2 connection manager will automatically handle all the standard OAuth2 parameters: client ID, client secret, code, grant_type, redirect_uri, response type, and state. Therefore you generally don't need to add any parameters besides scope.
  • Token URL: This will also be provided within the API's developer documentation, and usually contains the word "token".
  • Client ID and Client Secret: These will be provided by the API as described above.

After you've entered your custom OAuth2 values, click Save. The custom connection will then appear on your list of Connections, where you can edit or delete the connection. For connections using the Authorization Code flow you can also click connect or disconnect to log in through the application's website.

Custom OAuth Example

Every API will work a bit differently, but let's look at the Google BigQuery API as an example of what to look out for.

  • Go to the Google API Console and register an app
  • When you finish registering the app, Google provides a client ID and a client secret.
    bigquery-img13
  • Read the API documentation to find the authorization and token URLs. For Google BigQuery it would be these:
    • Authorization Base URL: https://accounts.google.com/o/oauth2/v2/auth?scope=https://www.googleapis.com/auth/bigquery/insights
    • Token URL: https://oauth2.googleapis.com/token

This site also has many articles containing complete examples of setting up custom OAuth connections:

Authorization Code Flow Examples

Client Credentials Flow Examples

Password Grant Flow Examples

How to Activate the Connection

The Authorization Code grant type involves logging into a server as described below. (For the Client Credentials flow, simply click Save and select the connector for your request as shown in step 4, as there's no need to log in.)

  1. First, click Connect to authenticate yourself through your new connection.
    customoauth-connect
  2. Assuming everything was set up correctly, you'll be taken to the target site and prompted to connect your account.
    custom-oauth2-img4
  3. Once you accept, you’ll see your new connection reflected in both the Connections list and under the OAuth dropdown menu.
    customoauth-connected
  4. Now you can enter your request details and choose your custom connector to run your request.
    bigquery-img21

Usage Notes

  • Custom OAuth2 connections are only saved to the sheet in which they're created. Unlike preset connections, they won't be available in other sheets.
  • Some APIs provide multiple methods of connecting, e.g. either an API key or OAuth 2.0. If you're using the OAuth 2.0 method, just skip their instructions for adding an API key. You don't need to enter any authentication-related headers if you're using OAuth 2.0.
  • If your OAuth 2.0 connection hasn't been set up, or you no longer have access, you will receive the following error message: "Request failed: Access not granted or expired." In that case, go back to the Connections screen and click Connect again.

Appendix: Manually Retrieve Client Credentials Token

While API Connector's OAuth Manager can retrieve and refresh access tokens using the Client Credentials flow, there may be times you'd like to fetch the tokens yourself, without using the OAuth Manager. In those cases, open Google Sheets, click Extensions > API Connector > Open > Create request, and set up a request as follows:

  • Application: Custom
  • Method: POST
  • Request URL: your_token_url
  • Request body: {"grant_type":"client_credentials", "client_id":"your_client_id", "client_secret":"your_client_secret"}

Substitute in your API's token URL, and your client ID and secret. Name your request and click Run. The whole thing should look like this:
taboola-token

Some APIs require that you encode the client ID and secret to base 64 and pass them in a Header rather than pass them directly in the request body. In those cases, the token URL will remain the same, the Header will have a key of Authorization, value of Basic your_encoded_credentials, and the request body will be {"grant_type":"client_credentials"}

Once you pass your client ID and secret, an access token will be returned in the access_token field. To fetch data, create a new request including a Header with a Key of Authorization, value of Bearer your_access_token. Access tokens typically have a short-lived lifespan, so you'll likely need to refresh the token each time you make a request.

54 thoughts on “Create a Custom OAuth Connection”

    • Sure, API Connector works with any kind of token. They provide a cURL expression to get your token: curl -d "grant_type=password&username=YOUR_EMAIL&password=YOUR_PASSWORD" -X POST https://api.virtuoussoftware.com/Token
      So I think the easiest is to just substitute in your email and password, and then just copy and paste the whole expression into the cURL importer (Add-ons > API Connector > Import/Export > Import cURL). This will parse the expression and automatically configure it for API Connector so you can just hit Run to get your token.
      Then, follow the docs and use your token in the header like this: Key = Authorization, Value = Bearer your token. You can reference that token in its cell so you don't need to keep typing it in whenever the token updates. Hope that clarifies.

      Reply
  1. Hi -

    Is there a way to set the access and refresh token? The OAuth2 service I want to connect to doesn't use the normal OAuth2 "connect" workflow for some reason. They just give the client id, secret, access, refresh token, and token url.

    Thanks!

    Reply
    • Sure, it's totally fine to manually handle OAuth2 (or the various requests within the flow). In that case, instead of using API Connector's built-in OAuth2 connection manager, you'd set up a series of "regular" API requests and go through the OAuth2 flow manually.

      Reply
      • Generally it would be like this:
        Method: POST
        URL: https://api.com/v1/oauth2/token
        Request Body: {"client_id":"your client ID","client_secret":"your client secret","grant_type":"client_credentials"}

        That should return a token, which you'd then enter into the Headers section in a second request (where Key = Authorization, Value = Bearer your_token). The specifics may be different depending on your API, so you can check the API's documentation for details, or feel free to share the name of the API if you'd like me to take a look.
        Update: API Connector now has built-in support for the client credentials flow, so you can run and refresh your token in a single request.

  2. Hi. Is it possible to create a simple connector for google sheets with the Scope user.email ?

    I have a sheet with multiple editors added and i will welcome the editor with his google-email.
    cell A1 is "Welcome" and cell A2 is "=ACTIVEUSER()".
    This function calls activeUser = Session.getActiveUser().getEmail();
    But there is a authorisation required and i have problems to handle this.

    Thank you 🙂

    Reply
    • Hey Sven, sorry, I don't know any way to pass in a dynamic value for the scope. Generally you don't need to pass in the value for the 'email' scope yourself anyway, it's just used to make the platform pop up a login modal so the user can log in with their own email address. So users click Connect, then they see the popup and authorize the connection with their own account. That means multiple users can connect through a single OAuth2 connection, but they each need to authorize themselves individually.Hope that helps clarify, if not, let me know and I'll try to explain further.

      Reply
  3. Hello! I have this client and they are using RetailExpress POS and this is the API documentation: https://developer.retailexpress.com.au/

    It seems that it requires OAuth 2.0

    Can you add this? Or help/guide me to do it?

    Thaaaank you so much. I’m a very big fan of this tool will be using these to all of my clients in the future

    Reply
    • Hi Jab! Thank you for the message. Unfortunately it looks like they're using a non-standard mix of APIs keys + OAuth2, so it won't directly work with API Connector's OAuth2 manager (they require you pass an API key in the header to get your OAuth2 token). Therefore you will need to step through their process semi-manually, like this:
      1) register an application on their site to get an API key
      2) run a request to https://api.retailexpress.com.au/v2/auth/token. Under Headers, include Key = x-api-key, Value = your-API-key (substitute in your API key here). This request will return your token.
      3) To make new requests, keep that x-api-key header and add a new one of Key = Authorization, Value = Bearer your-token (substitute in your token here)
      4) Since the bearer token will continuously expire, you will always need to run that first request before you make a new request. You can set your headers to reference the updated token in your sheet, so you don't need to manually update your request.
      It's a little inconvenient compared to the normal process, but should work. Let me know if you get stuck or need further clarification.

      Reply
  4. 'Request failed: Access not granted or expired. Please reconnect.'
    i got this message for Zoho Analytics after some time and my connection is expired, then i need to manually re-connect the connection,

    so is there any way to avoid this manual process??

    please help

    Reply
  5. Hi Ana,

    I try to connect to an affiliate network. Therefore I used your explanation in your comment to Jab by using the credentials in the 'key' and ' value' fields in the header. That worked. But now I need to make a new request which is getting access to the affiliate programs based on a keyword. How to make a new request that follow up the first one (authorization)? This is the link of the affiliate program.

    https://wiki.awin.com/index.php/API_get_programmes

    Reply
    • Hey Michel, I checked their docs and it’s not the full OAuth2 process, it’s much simpler so you don’t need any of the instructions on this page.

      Under API Url Path, enter the API URL you’re interested in.

      Then, under headers enter Key = Authorization, Value = Bearer your_token

      Substitute in your token where it says your_token. You can get your token while logged in, they have some screenshots here: https://wiki.awin.com/index.php/API_authentication

      Please check if that works for you.

      Reply
  6. Hello, I'm curious if you could assist on the Snapchat Story Studio Analytics API? I keep getting "missing scope" when I follow the steps for the Custom API connector. Thank you!

    Reply
    • Sure, the scope should be snapchat-marketing-api. So the whole thing would be set up like this:
      Name: any name, e.g. Custom Snapchat
      Authorization Base URL: https://accounts.snapchat.com/login/oauth2/authorize
      Token URL: https://accounts.snapchat.com/login/oauth2/access_token?scope=snapchat-marketing-api
      Client ID & Secret: provided by Snapchat

      Let me know if you run into any issues.

      Reply
  7. I'm evaluating this add-on for use and am needing to do a custom OAuth2 connection (to GoToConnect / Jive). All I get when I try to connect is...

    Access Error
    An error has occurred: Error: Error retrieving token: access.denied, Invalid client secret.

    I've even deleted and created a new app on their side to generate a new Client ID & Secret, but I get the same thing every time. I can make manual app script code via Google's apps-script-oauth2 library authenticate fine, but I just keep getting this error when I try to do it from API Connector.

    Reply
  8. Hi Ana! I'm trying to connect with the Naver api login (https://developers.naver.com/products/login/api/api.md) but it seems something is missing as I got an error message as below. As you described I've input you callback url (https://script.google.com/macros/d/12COOkin8nodCH7fZGIBu0D2jWY8-AEA0uvElt4Ph4wRbLUD4wslqQUfG/usercallback) with the service URL of my client (e.g. https://www.apple.com). Is there any point that I need to revise? Please let me know 🙂

    ---------------------
    The URL where you tried logging in to NAVER is not registered. you need to check due to error in the *** service. You can use NAVER Login only through the registered URL.

    In case you need to use Log in with NAVER in a website that is not registered due to service characteristics (e.g. plugin services), please email us at [email protected] with the description of the service and of the action structures attached.

    Reply
    • I believe the service URL should be https://googleusercontent.com, as that's the domain used by Sheets extensions. Please check if that resolves the issue.

      Reply
    • Sorry for the delay in replying. I gow the following message:

      Error 400: invalid_request
      OAuth 2 parameters can only have a single value: access_type

      Reply
    • I'm sorry, please disregard. I realized I had set up my user stype incorrectly, therefore it was getting expired every seven days.

      Reply
      • Interesting, thank you for the follow up. I'm not totally sure where you set a user type, but I'm glad it's working now.

  9. Hi, I'm trying to connect to Square payments from Google sheets and have the Client ID: and Client Secret: but can't work out how to obtain Authorization Base URL: and Token URL:

    Reply
    • Looks like they list those URLs here. Based on that, the Auth URL is https://connect.squareup.com/oauth2/authorize and the token URL is https://connect.squareup.com/oauth2/token, please try those and see how it goes.

      Reply
      • Normally if a request works in Postman it should work in API Connector. Can you please double check that the setup is exactly the same? Or let me know your config details if you’d like me to take a look.

  10. Hello, I receive the below error while creating custom OAuth 2.0 for Mircosoft Dynamics 365 Business Central.
    How to change the redirect URL.

    AADSTS50011: The redirect URI 'https://script.google.com/macros/d/12COOkin8nodCH7fZGIBu0D2jWY8-AEA0uvElt4Ph4wRbLUD4wslqQUfG/usercallback' specified in the request does not match the redirect URIs configured for the application '439cfe63-7cd2-4032-a914-e236c24c8291'. Make sure the redirect URI sent in the request matches one added to your application in the Azure portal. Navigate to https://aka.ms/redirectUriMismatchError to learn more about how to fix this.

    Reply
    • Hey Selva, the redirect URL is registered on Microsoft's side, not on our side, and it looks like the link in their error message explains how and where to set this.

      Reply
      • Hi Ana,

        When I click on connect, this URL is loaded "https://script.google.com/macros/d/12COOkin8nodCH7fZGIBu0D2jWY8-AEA0uvElt4Ph4wRbLUD4wslqQUfG/usercallback". We have different redirect URL that I need to pass as a parameter. I can see exact same URL in your documentation.

      • Any requests running through the API Connector extension need to use the API Connector redirect URL; you can't use something else since then it wouldn't redirect to our extension after login. That redirect URL should be registered in the Microsoft portal so they can recognize and whitelist it.

  11. Hi. I have a trouble to create custom OAuth with Nexar.
    Authorization Base URL: https://identity.nexar.com/connect/authorize
    Token URL: https://identity.nexar.com/connect/token
    and added redirect url to my nexar portal,
    But when I try to connect, I get error with invalid request.

    Reply
    • Hey Karlis, from what I can tell in their docs, they're using the client credentials flow. That means you don't need to use the OAuth manager, instead you should create a POST request to https://identity.nexar.com/connect/token, with a request body of {"client_id":"your_client_id","client_secret":"your_client_secret","grant_type":"client_credentials"}. Please check if that works for you.
      Update: API Connector's OAuth manager now supports the client credentials flow.

      Reply
  12. Hi Ana, paid user here. Just wanted to know if this article could be used to pull snapchat marketing data? Because the api connector doesn't have the snapchat app in the dropdown button available.

    Reply
    • I haven't used Snapchat's API myself, but it looks like you would start off by making an application from your Ads Manager. That will give you the client ID and secret, and then the auth URL is https://accounts.snapchat.com/login/oauth2/authorize?scope=snapchat-marketing-api and the token URL is https://accounts.snapchat.com/login/oauth2/access_token. With those values you should be able to create your own custom OAuth connection and retrieve data from their API.

      Reply
  13. Hi Ana, do you think users without subscription/paid will be able to use this? I have been trying to create a custom oauth, but i got error. So i was thinking if this is locked for Free users.

    Reply
  14. Hi!
    I'm trying to connect to Spread Sheets form GA4(Google Analytics4).
    I wanted to know if this article could be used to pull GA4 data?
    Thanks.

    Reply
    • Yes, it is technically possible to create your own custom OAuth connection to Google Analytics, using a similar process to the example given for BigQuery. However there’s really no need to do so as API Connector has a preconfigured connection for GA4. Please see this article for info on connecting to GA4, or just let me know what you’re looking for.

      Reply
  15. hello! trying to pull API data from Marketo but didn't see any guides here. Can you please help giving guidance on Authorization Base URL and Token URL for Marketo? Thank you!

    Reply
    • I believe you don't need our OAuth manager for this, you can get a token by following their instructions here: https://developers.marketo.com/rest-api/authentication/.
      1. Get your client ID & secret by clicking Admin > Integration > LaunchPoint > View Details.
      2. Get your identity URL by clicking Admin > Integration > Web Services
      2. Send a GET request to https://000-CCJ-000.mktorest.com/identity/oauth/token?grant_type=client_credentials&client_id=1111111&client_secret=222222, substituting in your own identity URL, client ID, and secret
      3. The response will return your access token, which you can use by including it in a Header like Authorization: Bearer 12345, or by attaching to the end of your URL like access_token=12345
      You'll need to fetch new access tokens regularly as they only last 1 hour.
      Update: API Connector's OAuth Manager now supports the Client Credentials flow, and will refresh your token for you.

      Reply
  16. how can i create a flow of data from google sheet to business central? if there is something that i can get help from, i shall be thankful !

    Reply
    • Sorry, I'm not familiar with this API and from what I can tell it's pretty complex (like most of Microsoft's APIs). So I think you'll be better off with an application that contains a preset integration for Business Central, but if you want to try, you could test the instructions here. That article walks through the process of connecting to Business Central using Postman, and generally any request you make in Postman can be copied and run in API Connector.

      Reply
  17. I'm trying to connect the API for NinjaOne to API Connector, but the Callback URI for API Connector is just a few characters too long for the Callback URI input field used to create the client ID on NinjaOne's site. Is there any way to shorten that Callback URI?

    Reply
    • Hi Joe, I responded to this question via email a couple days ago, please check your spambox if you didn't see it. But in any case, our redirect URL is set by Google Apps Script, which is the platform upon which Google Sheets extensions are created, so unfortunately we're unable to change it.

      Reply

Leave a Comment

Jump To