Import JSON from an API in Google Sheets

Originally written for bulldo.gs — republished here with the canonical link pointing home.

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

// 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);
}

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.

FAQ

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.

---

Want the plain-English version? Describe the automation at bulldo.gs and get working Apps Script back — free, no login.