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.
- Authorization Errors
- Error Messages
- Incorrect Content-Type
- Missing Records
- Non-JSON/CSV Response Types
- Quotas and Limits
- Request Form Syntax Errors
- Requests Timing Out
- Restricted User Agent
- Restricted IP Address
- Scheduling Issues
- Un-even/Mismatched Output Rows
Make sure you've entered any required authorization credentials into the Headers table, and that you've encoded them to Base 64 if required.
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.
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.
By default, many API servers return only a limited number of rows in each response. To receive additional rows, there is usually some kind of pagination system, where you need to add parameters for page number and starting index. Consult the API documentation for specifics, looking for terms like "page", "limit", and "offset".
API Connector currently only supports JSON and text/CSV responses. This means that if your API responds with XML, the request won't work. Most APIs provide a JSON option, so look for a JSON endpoint to resolve this error. If you're requesting public (no authentication needed) XML files, Google Sheets supports this natively with the IMPORTXML() function.
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.
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.
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.
Very large or deeply nested data sets may cause API Connector to time out or stop responding.
To resolve this issue, please try the following:
1) limit the size of your request. Most APIs let you limit request size via date- or ID-based query strings.
2) 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.
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.
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.
If you are experiencing issues with your scheduled reports, please consult this list of troubleshooting tips specific to triggers: Troubleshooting Triggers
As JSON API responses aren’t specifically designed for spreadsheets, they 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.