Search API Connector Documentation

Print

Connect to an OAuth2 API

Most APIs require that you enter some credentials to prove your identity. Usually this will be an API key, which is a unique value similar to a password. API keys are easy to use, as they can be simply entered as Header keys or URL query strings.

As an alternative to API keys, many APIs provide access using OAuth 2.0, which is a more secure (and complex) method of authentication and authorization that works through a process of exchanging and refreshing tokens. To enable API access via OAuth 2.0, API Connector includes OAuth 2.0 integrations for several popular API platforms, or lets you add your own custom OAuth2 connection.

This feature is available only for business-plan subscribers, please install API Connector for a free trial or upgrade to access.

Contents

Why Use OAuth 2.0?

  • some APIs provide access only through OAuth 2.0
  • connect by clicking a button rather than digging through documentation for API keys
  • avoid manually managing the process of exchanging & refreshing tokens
  • connect securely, without needing to enter sensitive access credentials.

Before You Begin

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

How to Connect

  1. When you select an OAuth connection from the dropdown connections list, you will be prompted to connect.
    oauth2-img9
  2. Alternatively, click the “Manage connections” link to see the full set of integrated APIs. This screen can also be accessed via the main menu.
    oauth2-img1
  3. The Manage Connections screen lists all available platforms.
    oauth2-img2
  4. Clicking a Connect button will produce a screen on the target site prompting you to connect your account. Every service will look a little different but you will be asked to accept the connection.
    oauth2-img3
  5. You’ll now see your new connection reflected in the list and under the OAuth dropdown menu. This means you’ve now successfully connected via OAuth 2.0.
oauth2-img4

How to Add a Custom OAuth2 API Connection

If API Connector does not provide a pre-built OAuth2 integration for your platform, you can add your own custom OAuth2 connection.

Please note that the custom OAuth2 connection manager only supports the Authorization Code grant type, which is the most common OAuth2 flow. If your API uses another flow like the Client Credentials grant type, get your token by sending a "grant_type":"client_credentials" request body in a standard API request.
  1. If you haven’t already, enter the Manage Connections screen by clicking Add-ons > API Connector > Manage Connections, or by clicking the “Manage connections” link on the Create screen. Once there, scroll to the bottom and click Add Custom OAuth:
    oauth2-img7
  2. You will be presented with a screen containing input values for Name, Authorization Base URL, Token URL, Client ID, and Client Secret:
    oauth2-img8
  3. Choose a name for your custom connection and enter it in the Name field. The other values will be provided by your API platform. Typically you will need to begin by creating an “app”, after which the API will give you a Client ID and Client Secret.
  4. During this setup process, your API platform may request that you enter a redirect or callback URL. This is the URL: https://script.google.com/macros/d/12COOkin8nodCH7fZGIBu0D2jWY8-AEA0uvElt4Ph4wRbLUD4wslqQUfG/usercallback
  5. Every API will work a bit differently, but as an example of what to look out for, if you were connecting to the Google Analytics API, you’d first create an app and register it using the Google API Console (for a more in-depth example, see this article). They then provide a client ID and a client secret, and you’d read their documentation to find the following base and token URLs:
    • Authorization Base URL: https://accounts.google.com/o/oauth2/v2/auth?scope=https://www.googleapis.com/auth/analytics.readonly
    • Token URL: https://oauth2.googleapis.com/token
      API Connector’s OAuth2 connection manager will automatically handle all the standard OAuth2 parameters: client ID, client secret, code, grant_type, redirect_uri, response type, state. Therefore you generally don’t need to add any parameters unless you’re choosing a specific scope, like in the Google Analytics example above.
  6. After you’ve entered your custom OAuth2 values correctly, click Save. It will then appear on your list of Connections, where you can connect, disconnect, edit, and delete the connection.
  7. Custom connections will be saved only to the sheet in which they are created.

How to Make an OAuth 2.0 API Request

Once you have your OAuth 2.0 connection in place, you can select it from the drop-down menu in the Create screen:
oauth2-img5

The rest of your request will be constructed as usual. Consult your API platform’s documentation for detailed instructions.
oauth2-img6

Notes

  • 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 make sure you’re connected.
  • For preset connections, API Connector accounts only support one OAuth2 connection per service. For example, you can not authenticate to two different FB Ads Manager accounts at the same time (but there’s no problem connecting to multiple client accounts if they’re all under the same FB Ads Manager account).
  • API Connector currently provides pre-built OAuth 2.0 integrations for the AdRoll API, Ahrefs API, Facebook Ads API, GitHub API, Google Ads API, Google Analytics API, Google Calendar API, Google Classroom API, Google PageSpeed Insights API, Google Search Console API, Harvest API, Instagram Insights API, Jira API, LinkedIn Ads API, QuickBooks API, Quora Ads API, Spotify API, Strava API, Vimeo API, Xero API, YouTube Data/Analytics API, and the Zoho CRM API. More integrations are coming soon. Please let us know if there are specific APIs you’d like to see.

Previous Basic Authentication
Next Filter Fields (JMESPath)

33 thoughts on “Connect to an OAuth2 API”

    • Hi Adam, thank you for the suggestion. IAP is something I really know nothing about. I’ve mostly been adding high-demand connections like Facebook and YouTube etc, so this one might make more sense as a custom OAuth2 connection that you add privately, but I will investigate.

      Reply
  1. Hey,

    I have a question about Connections. Is there a way to create a global connection for all the accounts editing the google spreadsheet? For example, we would like to create a Quickbook connection that can be used by all the editors to create requests. Thank you for your answer!

    Reply
    • Hi Erik! Thank you for your message. The OAuth2 connections are per user, since OAuth2 is an authentication framework based around allowing access only for the specific users that have authorized the application. If you would like multiple people to access the connection, you could either have each user authorize the connection separately, or you could create a shared gmail account for use with Sheets, or you could designate someone to set up / schedule requests and just give everyone else read access to the data. I hope that helps clarify, if not, please let me know.

      Reply
    • Hey David, I haven’t connected to the Google Ads API myself but I think you could do this by following Google’s documentation on how to get your OAuth2 details and connect to the API. I’ll probably go through this process myself and add it as a default connection.

      Reply
    • 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 . 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
  2. Hello. I suggest a connection to Mercadolibre API, the most important market place in Latinamerica and one of the largest of the world.

    Reply
  3. 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
    • Hey Paul, that sounds like all the parts to a standard OAuth2 flow, which part is missing? But in any case, 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
  4. 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, and I must admit I’m not totally sure what you’re trying to do. Generally you don’t need to pass in the value for the ’email’ scope yourself, 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
  5. 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
  6. ‘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
    • I just checked this out and it was pretty straightforward to set up OAuth, I was able to connect and retrieve test data using our OAuth connection manager. However, the confusing part is that they don’t seem to provide any simple mechanism for an individual merchant to get their own data, instead they require that you set up a sandbox account, set up a test app, then get approved for a production account, then create a production app to be published in their marketplace, etc. I just applied for a production account and will report back on how that goes (and may end up adding this to our list of preset apps).

      Reply

Leave a Comment

Table of Contents