Search API Connector Documentation
You may encounter various error messages while using the API Connector add-on for Google Sheets. While there may be some overlap, these error messages can generally be categorized as follows:
API Connector Errors
- Error: Count of request URLs should match count of request bodies
- Error: Please enter destination sheet
- Error: Please enter API URL path
- Failed to save request as the name already exists
- OAuth2 connections are available only for Business plan users.
- Request failed: Access not granted or expired.
- Request failed: Data response not in JSON, XML, or CSV format
- Request failed: Invalid JSON in request body
- Request failed: Range not found
- Request failed: The number of columns in the data does not match the number of columns in the range
- Request processed, no records found
- Too many errors, request paused
- Users can only run X requests per month
Google Quotas and Limits
Since these limits or issues are on Google's side, we can't always resolve them, but we've listed solutions and workarounds where possible.
- Bad Request Error 400
- Exceeded maximum execution time
- Exception: You have exceeded the property storage quota. Please remove some properties and try again
- Failed to run request
- Failed to save custom headers
- Limit Exceeded: URLFetch URL Length.
- Request failed: Attribute provided with invalid value: Header:######
- Request failed: Bad request
- Request failed: Data storage error
- Request failed: DNS error
- Request failed: Service Spreadsheets timed out while accessing document with id
- Request failed: This action would increase the number of cells in the workbook above the limit of 10000000 cells.
- Request failed: Your input contains more than the maximum of 50000 characters in a single cell
- Service invoked too many times for one day: urlfetch
- Service using too much computer time for one day
API Server Errors
Sometimes we successfully send the request, but the API itself responds with an error message. These are reported as follows:
- We received an error from xyz.com (###)
- We received an error from googleapis.com (403) - Request had insufficient authentication scopes
IMPORTAPI Custom Function Errors
Certain error messages are encountered only while using the custom ImportAPI function. For more information on these errors, see this article:
Bad Request Error 400
This error occurs when logging into a Google service (e.g. Google Analytics, Google Ads, etc.) that is different from the Google account you're using with Google Sheets.
Unfortunately there is a long-standing issue on Google's side related to signing in with multiple Google accounts at the same time (info). Google doesn't support logging into extensions with multiple Google accounts.
Currently the only solution is to use the same account with Google Sheets that you are using with your Google service. You may want to transfer your Mixed Analytics account to a different email address.
Error: Count of request URLs should match count of request bodies
When running a multi-query request with both multiple request URLs and multiple request bodies, the counts must match, e.g. if you have 3 request URLs, you must have 3 request bodies.
Error: Please enter destination sheet
You'll see this error if you run your request without a destination sheet. To resolve, set a destination sheet in your request before clicking Run.
Error: Please enter API URL path
All custom API requests must contain a request URL. This URL should consist of at least a domain name and endpoint, e.g.
www.apisite.com/v1/data. Optionally, it can also contain query strings, e.g.
Exception: You have exceeded the property storage quota. Please remove some properties and try again
Failed to save custom headers
These errors mean that you've run into Google's 500kb properties limit mentioned here: https://developers.google.com/apps-script/guides/services/quotas#current_limitations. These storage properties are your own personal data store used for saving data tied to extensions and scripts you're using.
To reduce your saved properties, disconnect from any extra saved OAuth connectors, delete unused saved requests wherever possible, and refresh fields in the field editor to remove saved field headers.
Failed to run request
This error indicates that the request is unable to complete successfully, but neither the API nor Google returned any specific information about the cause of the problem. The primary known cause of this error is a request that takes too long to complete and therefore hits Google's 6 min/request processing limits as described here.
To address, 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. You can usually use these query strings to fetch new data only instead of re-fetching an entire data set.
- switch to 'compact' or 'grid' 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.
- if you are using pagination handling, set the request to stop running once a page count has been reached or a specific field is empty. Running pagination until no data is returned may cause some requests to time out.
- use the field editor or JMESPath filters to reduce the number of fields printed into your sheet.
- processing time may increase if your sheet contains a lot of formulas that update as the API request changes cell values. Therefore, running your requests in a fresh new sheet may help.
Failed to save request as the name already exists
All API requests must have a unique name. If you re-use a name that already exists, the request will not save. To resolve, change the request name and retry.
Limit Exceeded: URLFetch URL Length
API Request URLs can not be longer than 2048 characters (info).
As a workaround you can use a URL shortener tool, e.g. https://tinyurl.com/
OAuth2 connections are available only for Business plan users.
This error occurs when an OAuth connection is selected and run by a user that doesn't have access to OAuth.
OAuth connections are currently only available on the Business plan, and are not available for users on the Free or Pro plans.
If your OAuth requests are now failing with this error message, when they previously ran without issue, a likely explanation is that your 14 day free trial has expired.
Request failed: Access not granted or expired.
This error occurs when you use the OAuth 2.0 connection feature of API Connector. It means your connection hasn't been activated, or access has expired. To resolve, navigate to the Connections screen and make sure your OAuth 2.0 connection is active. If you are not using a preset OAuth 2.0 connection, make sure you've selected Authentication = None.
Request failed: Attribute provided with invalid value: Header:#######
This means you've entered a header key that is not accepted by Google. The affected header key will be printed out where it reads
##### above. To resolve:
- Try removing the header key entirely. Certain keys (e.g. "Host" and "X-Forwarded-For") are unacceptable to Google because they are automatically added by Apps Script and can't be overridden.
- The header key may be incorrect, so also check the API's documentation and make sure you've entered the header key exactly as written.
Request failed: Bad request
This means the API URL hostname is malformed. A request like
http://87000 would trigger this error.
- Make sure you've entered a valid API URL, including a full hostname.
- Make sure your request doesn't inadvertently include line breaks. Each line needs to contain the complete URL; if there's a line break, API Connector reads it as a "new" request.
Request failed: Data storage error
This is an occasional, temporary bug on Google's side related to the getProperty() method for saving triggers and requests. Bug reports have been filed and acknowledged.
If you receive this error, please wait and try your request again later.
Request failed: DNS error
This means the server you're trying to reach doesn't exist or isn't responding. Check if your server has reported any outages. If not, double-check your request URL and run again.
Request failed: Data response not in JSON, XML, or CSV format
This error occurs when the data is returned in a format that isn't supported and can't be converted into a tabular format for Sheets, e.g. an image or invalid JSON.
A common cause of this error is a request containing an invalid JMESPath expression. To determine if this is the issue, export the raw API response, and run it through the JMESPath expression tester to check if the query produces valid JSON.
Request failed: Invalid JSON in request body
This error occurs when your request body contains invalid JSON.
To automatically fix invalid JSON, you can run your request body through a tool like https://codebeautify.org/json-fixer.
Request failed: Range not found
This occurs when the request's Destination sheet settings don't point to a valid cell.
To resolve, make sure that the value in the "cell" field is the location of a cell, e.g.
Request failed: The number of columns in the data does not match the number of columns in the range
This error is related to merge mode. When using merge mode, the new data being merged must have the same number of columns as the existing data set.
Request failed: This action would increase the number of cells in the workbook above the limit of 10000000 cells.
Google Sheets permits a maximum of 10 million cells per sheet. Therefore, you will see this error message from Google if your request would cause your sheet to surpass the limit. To address, try any or all of the following:
- Delete any unused tabs, rows, and columns from your worksheet. Empty cells count towards the total cell limit.
- Run your request in a fresh new sheet
- Split your requests into separate worksheets
- Limit the size of the data response you’re requesting
- Use the field editor or JMESPath filters to print out fewer fields
- Change the report style to compact or grid as these styles generally occupy fewer columns.
Request failed: Your input contains more than the maximum of 50000 characters in a single cell
This error occurs when the data response includes a field that contains more than 50k characters. This occasionally occurs when pulling from APIs containing text fields with a lot of content. To resolve, use the field editor or JMESPath filters to filter out the field containing too many characters.
Request processed, no records found
This means there are no records to display. If you were expecting data in the response, check the following:
- make sure you've entered the correct API request URL
- remove or edit query strings that could be filtering the results, such as dates and IDs
- remove any JMESPath expressions or field filters as they could be filtering out your results.
Request failed: Service Spreadsheets timed out while accessing document with id
This is an error from Google typically associated with large or complex sheets. You can see some reports and information about this error in Google's bug tracker here (add a star for visibility and updates). The problem seems to be related to sheets that require a large amount of processing time.
Until Google fixes this reliably, one of the ways to fix this issue is to simply make a copy of the sheet.
Another possibility could be to fetch your data into a new, fresh sheet, and then use a function like IMPORTRANGE to pull the data into your existing sheet.
Service invoked too many times for one day: urlfetch
Service using too much computer time for one day
Exceeded maximum execution time
Too many simultaneous invocations
These (and similar) error messages come from Google and are described here: https://developers.google.com/apps-script/guides/services/quotas.
Google applies these limits on a per-user basis (i.e. each email address gets its own limit), and sums together all the processing time and fetches from all the sheets you're using. This includes all scripts, functions, and other extensions that you may be running in addition to API Connector.
The most common cause of this issue is that you've exceeded Google's 20k/day "urlfetch" limit. To resolve this issue, check the following steps:
- First check all the scripts you're running through Google's servers by navigating to https://script.google.com/home/executions.
- In the column named Projects, if you primarily the names of extensions other than API Connector (e.g. "ImportJSON"), then you'll need to reduce usage of those scripts and extensions.
- If you primarily see "API Connector", then this issue is likely due to excess requests run through API Connector. In that case, one common source of this issue excessive re-calculations. Please check this article for information on preventing the IMPORTAPI custom function from re-calculating.
Too many errors, request paused
API Connector may automatically stop running a request through automated functions (IMPORTAPI or triggers) if the request returns too many errors.
To reset the counter and enable the request to run automatically again, successfully run the request once, or simply open and re-save it.
Requests run manually through the sidebar are not affected by error limits.
Users can only run X requests per month
Each API Connector plan has a monthly request limit, as shown on the pricing page. These monthly requests reset on the 1st of the month (UTC time). If the user's request count exceeds the limit, no further requests will be run until the new month begins.
We received an error from xyz.com (###)
This error message occurs when the API you're trying to connect to refuses to run your request and has returned an HTTP Response Status error code.
First, click "show response" to see the exact error message returned by the server. This will often alert you to the specific nature of the problem.
Since these errors come from the application's server, not from API Connector, you may need to check their API documentation to resolve the issue. You can also look up the HTTP error code displayed within the parentheses. This site contains brief explanations for all error codes: https://httpstatuses.com.
We received an error from googleapis.com (403) - Request had insufficient authentication scopes
This error is a specific case of the more general "We received an error" error message above. This error occurs when the connector's OAuth service has not been fully authorized for Google Ads, Google Analytics, or other Google services.
To resolve, disconnect and then reconnect the Google application, making sure to enable any permissions requested during the authorization process.
In some cases, API platforms will accept a request but return an error message containing additional requirements or information as their response. In those cases, their custom error message will be displayed in the output sheet.