Why do I get 'Exception: Request failed' when fetching the URL?

Apps Script blocks URLs that return non-2xx status codes by default. Either the endpoint is down, requires authentication, or is returning a 4xx. Add { muteHttpExceptions: true } as the second argument to UrlFetchApp.fetch() to get the raw response body and status code back so you can see the actual error message.

Can I import JSON that has nested objects, like address.city?

Yes, but you have to flatten it manually. In the headers.map() callback, instead of obj[key], use a resolver like function getNestedValue(obj, path) that splits on '.' and walks the object. There is no built-in flattening — you decide which leaf values become columns.

The sheet only shows the first row of data. What went wrong?

The API probably returned a single object, not an array. JSON.parse on { ... } gives an object, and calling .map() on an object returns undefined. Wrap the parsed result: var data = Array.isArray(parsed) ? parsed : [parsed]. That normalizes both shapes.

How do I pass an API key without hardcoding it in the script?

Store it with PropertiesService: PropertiesService.getScriptProperties().setProperty('API_KEY', 'yourkey'). Read it back with PropertiesService.getScriptProperties().getProperty('API_KEY'). Properties are scoped to the script and not visible in version history, so they don't leak through shared spreadsheets or accidental screenshots of the editor.

// Sheets · Apps Script

Import JSON from an API in Google Sheets.

How to fetch JSON from any REST API in Google Sheets using Apps Script — parse the response, map objects to rows, and write a rectangular 2D array with setValues.

“I want to pull live JSON data from an API endpoint directly into a Google Sheet without installing an add-on.”

The script

copy · paste · trigger

importJson.gs

Apps Script

// Fetch JSON from an API and write it to the active sheet
// Adjust API_URL and the field list to match your endpoint
function importJsonFromApi() {
  var API_URL = 'https://jsonplaceholder.typicode.com/users';
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var response = UrlFetchApp.fetch(API_URL);
  var raw = response.getContentText();
  var data = JSON.parse(raw);

  var headers = ['id', 'name', 'username', 'email', 'phone'];
  var rows = data.map(function(obj) {
    return headers.map(function(key) { return obj[key] || ''; });
  });

  sheet.clearContents();
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
}

Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

Why getContentText() comes before JSON.parse

UrlFetchApp.fetch() returns an HTTPResponse object, not a string. The first time I skipped getContentText() and passed the response object directly to JSON.parse(), it silently parsed to null and the sheet wrote nothing. You need raw = response.getContentText() to get the actual body as a string, then JSON.parse(raw) turns it into a JavaScript object or array.

The URL must be publicly accessible or accept an API key via a query parameter or Authorization header. Add headers like this: UrlFetchApp.fetch(url, { headers: { Authorization: 'Bearer ' + token } }). Apps Script's UrlFetchApp quota is 20,000 calls per day on a free Google account, 100,000 on Workspace.

The rectangular array constraint — why setValues fails without mapping

setValues() is strict: it requires a 2D array where every row has the same number of columns. If you hand it an array of plain JSON objects, it throws 'The number of rows or columns in the range does not match the number of rows or columns in the data.' The fix is to map each object to an array of values in a consistent column order, which is exactly what the headers.map() call inside data.map() does.

Picking a fixed headers array also protects you when the API adds new fields — the sheet schema stays stable instead of silently growing extra columns. If a field is missing from a particular object, the fallback || '' keeps every row the same width so the range and the data never disagree.

One practical note: if your API returns a single object rather than an array, wrap it: var data = [JSON.parse(raw)]. The map chain works identically.

Running it on a schedule without touching the sheet manually

Open Extensions > Apps Script, paste the function, and save. To run it once, hit the play button (you'll be prompted to grant UrlFetchApp and SpreadsheetApp permissions on first run — both are required). To run it automatically, go to Triggers (the clock icon in the left sidebar), add a new trigger, choose importJsonFromApi, and set a time-based interval. Hourly or daily covers most data-freshness needs and stays well within the free quota.

If the API requires OAuth rather than a static key, Apps Script's OAuth2 library (GitHub: googleworkspace/apps-script-oauth2) handles the token dance. That's a separate setup, but the fetch-parse-setValues pipeline here doesn't change.

Review record structure before mapping columns

Paste a JSON array of records into the free browser check to find missing fields, mixed types and nested values that need an explicit mapping. If your response wraps records in a data or results field, extract that array first. The check fetches no URL and writes no Sheet; it does not validate this script or detect duplicate JSON keys or numeric precision loss. Test your chosen mapping on a copy before clearing existing sheet contents. Preserve valid zero and false values explicitly instead of treating them as missing.

Check JSON record structure

Sort records by an exact string field

Choose ascending or descending text order and keep tied records in their original order. Missing fields or non-string values stop the run; numeric and locale sorting are not inferred.

Sort JSON string records

Download JSON string-field sort source JSON string-field sort instructions

Rename one JSON field before mapping columns

Choose an exact source field and replacement name for flat string records. Every record must contain the source field; an existing replacement field stops the whole conversion. Values and other fields stay intact. Review the result before copying; nothing is uploaded or written to a Sheet.

Rename a JSON field

Download JSON field renamer source JSON field renamer instructions

Keep records with an exact value

Choose a field and an exact string value to keep matching records. Spaces and case matter; an empty value matches blank strings. Every record must contain the field, and all values must be strings. The whole input is checked before output; nothing is uploaded or written to a Sheet.

Filter JSON records

Download JSON filter source JSON filter instructions

Keep only the fields you need

Choose exact field names, one per line, and preview a smaller JSON array. Every record must contain the selected names and use string values only. Missing fields stop the whole conversion; no values are guessed or sent anywhere.

Select JSON fields

Download JSON field selector source JSON field selector instructions

Preview string records as table rows

For flat records whose values are all strings, choose an explicit header order and missing-field policy. Preview literal tab-separated cells before adapting your import. Numbers, nested values, extra fields and embedded tabs or line breaks stop the conversion; nothing writes to your Sheet.

Convert JSON strings to table rows

Download JSON-to-TSV source JSON-to-TSV instructions

Going the other direction? Use unique table headers as JSON field names, keeping leading zeros and blank cells as strings.

Convert table rows to JSON strings

Download TSV-to-JSON source TSV-to-JSON instructions

Combine JSON Lines into an array

Have one JSON record per line? Combine the records into a formatted JSON array before choosing your table columns. Each record must be a flat object with string values; leading zeros and blank strings stay intact. Blank lines are rejected, and the conversion stops at the first invalid line. It fetches no URL and writes no Sheet.

Use up to 499 records with 50 fields each. Repeated keys within a record keep the last value when JSON is parsed; review your source if that could hide a conflict.

Convert JSON Lines to an array

Download JSON Lines source JSON Lines instructions

Export a JSON array as one record per line

For a line-oriented import, turn an array of flat string-valued objects into compact JSON Lines. Up to 499 records with 50 fields each; numbers, nested values and an empty array stop conversion. Blank strings and escaped tabs or newlines remain literal values. Repeated keys keep the last parsed value; field order is not canonical. Nothing is uploaded or written to a Sheet.

Export a JSON array as JSON Lines

Download JSON Lines export source JSON Lines export instructions

For a local import script, use the offline JSON checker contract and runnable example. Source retrieval is free and needs no account.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions

Why do I get 'Exception: Request failed' when fetching the URL?: Apps Script blocks URLs that return non-2xx status codes by default. Either the endpoint is down, requires authentication, or is returning a 4xx. Add { muteHttpExceptions: true } as the second argument to UrlFetchApp.fetch() to get the raw response body and status code back so you can see the actual error message.
Can I import JSON that has nested objects, like address.city?: Yes, but you have to flatten it manually. In the headers.map() callback, instead of obj[key], use a resolver like function getNestedValue(obj, path) that splits on '.' and walks the object. There is no built-in flattening — you decide which leaf values become columns.
The sheet only shows the first row of data. What went wrong?: The API probably returned a single object, not an array. JSON.parse on { ... } gives an object, and calling .map() on an object returns undefined. Wrap the parsed result: var data = Array.isArray(parsed) ? parsed : [parsed]. That normalizes both shapes.
How do I pass an API key without hardcoding it in the script?: Store it with PropertiesService: PropertiesService.getScriptProperties().setProperty('API_KEY', 'yourkey'). Read it back with PropertiesService.getScriptProperties().getProperty('API_KEY'). Properties are scoped to the script and not visible in version history, so they don't leak through shared spreadsheets or accidental screenshots of the editor.

// one good script a week