Troubleshooting API Requests

While using the API Connector add-on for Google Sheets, there may be cases where your API request doesn’t work, or returns an error message. This article will describe some known issues and ways to troubleshoot issues with your API requests.

As a general tip, you can also try running your request in a different online API tool like Swagger Inspector to help identify whether the issue lies with API Connector settings or with the API request itself.

Add-on Menus Don’t Work

If you’re having issues with menus and tabs not responding, this indicates a problem with your browser. Browsers like Brave, certain ad blockers, cookie settings, and other extensions may prevent API Connector from functioning normally. To resolve, run through the following suggestions. The exact cause of the issue depends on your browser, so you may need to try multiple suggestions from this list until your add-on works normally.

  • In Chrome, enable third party cookies for GSuite add-ons by clicking the three dots in the top right corner of your browser, then clicking Settings > Privacy and Security > Cookies and other site data. If you see the “Block third-party cookies” radio button selected, scroll to the section that says “Sites that can always use cookies”, click Add, and enter https://[*.]googleusercontent.com in the input box. Reload your sheet after making the change.
    troubleshooting-img1
  • disable all extensions and re-run. In Chrome, you can view all your browser extensions by clicking the three-dot menu in the top right of your browser and navigating to More tools > Extensions.
  • Clear cache and cookies:
    • Google Chrome users, click here 
    • Mozilla Firefox users, click here 
    • Microsoft Edge users, click here 
    • Safari users, click here 
  • run the add-on in incognito/private browser mode.
  • run the add-on in another browser.

For Safari, it is possible to encounter an issue where the entire add-on interface is blank or doesn’t load (The same issue in Chrome would result in a ‘drive.google.com refused to connect’ error message). This is a bug on Google’s side related to being logged into multiple Google accounts at the same time. To resolve, log out from all Google accounts and log back in with only the account you’re using with Google Sheets.

Authorization Errors

Make sure you’ve entered any required authorization credentials into the Headers table, and that you’ve encoded them to Base 64 if basic access authentication is required.

Columns Shifting

API Connector displays fields in the order they appear from the API. In some cases, the API returns data in a different order, or doesn’t send keys that aren’t populated with values, resulting in mismatched column orders between pulls. There are a few ways to resolve this:

  1. If your API supports filters natively (e.g. via a query string like &fields=abc), make use of these filters to limit your fields to just those that consistently contain data. This often resolves the issue.
  2. JMESPath filtering (paid feature) allows you to choose which fields you’d like and set their position in the sheet, so that column ordering will remain consistent each time. This method is very reliable since it will enforce column ordering even when the API itself changes the fields that it returns.
  3. You can query individual columns by name into a second sheet using this Sheets’ function, replacing ‘data’ with your sheet name, and ‘field_name’ with your field name:
=QUERY(data!$1:$10000,"SELECT "&SUBSTITUTE(ADDRESS(1,MATCH("field_name",data!$1:$1,0),4),1,""),1)

Cookies

API Connector can’t access and attach cookies to your request. This means that API Connector will not work with any API that requires cookies.

Error Messages

Pay attention to any error details as they will alert you to specific fields that need to be addressed. This article on Error Messages provides more details on each message.

Incorrect Content-Type

When making POST requests, we tell the server how to parse the content using a content-type header that describes the object’s format. For URL-encoded types, API Connector automatically sets the content type to application/x-www-form-urlencoded, so you don’t have to set it yourself. If your server expects a different content type, you can manually set content type of application/json to override it, by entering Key = content-type, Value = application/json in the Headers table.

Missing Records (Pagination)

By default, many API servers return only a limited number of rows in each response. This is known as “pagination”.

There are several common methods of pagination:

  • pagination parameters, e.g. www.demo.com/api?page=2
  • offset-based pagination, e.g. www.demo.com/api?offset=0&limit=100
  • cursor IDs, e.g. www.demo.com/api?starting_after=123456

The exact parameters vary for each API, so if you need to retrieve paginated responses, please consult your API documentation for details. You can usually find this information by searching for terms like “paging”, “pagination”, and “limit”.

In API Connector, you can run these requests separately, or automatically loop through using pagination handling (paid feature).

Non-JSON/CSV/XML Response Types

API Connector supports all three major data formats: JSON, XML, and text/CSV. If you request data in another format, or request a ‘regular’ HTML website URL rather than an API endpoint, the request won’t work.

Quotas and Limits

The API you’re accessing may limit the number or rate of requests. Google Sheets itself also has built-in limits, as documented here: https://developers.google.com/apps-script/guides/services/quotas. There is a limit of 20,000 API calls quota per day per user, per sheet.

To avoid inadvertently reaching these limits, limit triggers and queries where possible (e.g. don’t run hourly refreshes if you only need daily). Since these limits are per user, you may also hit the API limit if you’re running your own custom scripts or functions in addition to requests from API Connector.

Request Form Syntax Errors

When you’re reading API documentation, you’ll likely see examples based on different coding languages. Don’t copy these in directly — API Connector is a no-code method of interaction with APIs, so pasting in code snippets won’t work.

Wrong: https://api.football-v1.com/v2/players/player/{player_id}
Right: https://api.football-v1.com/v2/players/player/29

Just type in the URL, any headers, and other options. Check the quick start for an example of what a completed API request looks like.

Requests Timing Out

Very large or deeply nested data sets may cause API Connector to time out or stop responding, due to Google Sheets’ 6-minute per request limit.

To resolve this issue, try any or all of the following:

  • limit the size of your request. Most APIs let you limit request size via date- or ID-based query strings.
  • switch to ‘compact’ report style. The algorithm for the default single-row output takes a little longer to run, since it needs to process and flatten all the nested data into a single row. The ‘compact’ output is faster as it doesn’t perform this extra flattening.
  • if you are using pagination handling, reduce the total number of fetches.
  • use JMESPath filtering (paid feature) to reduce the number of fields printed into your sheet.

Restricted User Agent

Google Apps script doesn’t enable custom user agents. This means any request from API Connector will use Google script’s default user agent ‘Mozilla/5.0 (compatible; Google-Apps-Script)’. Unfortunately certain APIs may block requests from Google’s User Agent. If that is the case, you will need to ask the platform to lift this restriction on their API.

Restricted IP Address

All API requests through Google Sheets originate from a set pool of IP ranges associated with Google’s servers, and will send over an IP address associated with Google Inc along with your actual IP address. This may prevent a request from resolving if the API server has blocked requests from Google’s IP addresses, or requires all IP addresses to be whitelisted in advance.

Google’s documentation links to a method of looking up these IP addresses if you need to whitelist or approve these requests.

Scheduling Issues

If you are experiencing issues with your scheduled reports, please consult this list of troubleshooting tips specific to triggers: Troubleshooting Triggers

Un-even/Mismatched Output Rows

JSON API responses frequently contain nested data rather than the column-by-column output you’d expect to see in a spreadsheet. Therefore API Connector add-on uses logic to convert JSON responses into a tabular format for Google Sheets. In certain cases, this results in mismatched output rows. If you experience this issue, try changing to a different report style.

Other

Still having trouble? Check out the knowledge base or leave a post in the user community. You’ll receive a response shortly!

Leave a Comment