How to import JSON or an API into Google Sheets

1. What Google Sheets can do by itself

• IMPORTDATA. =IMPORTDATA("https://example.com/prices.csv") loads a CSV or TSV file from a public address into the sheet. It cannot read JSON.
• IMPORTXML and IMPORTHTML. IMPORTXML(url, xpath) reads XML or HTML with an XPath query, and IMPORTHTML(url, "table", 1) takes a table or list from a web page. IMPORTFEED reads RSS and Atom feeds. None of them reads JSON either.
• File → Import. File → Import takes a CSV, TSV or Excel file you downloaded, once, by hand.

What all the formulas leave out:

• No headers. A formula cannot send Authorization: Bearer … or X-API-Key. If an API accepts the key as a parameter in the address, it works, but the key is then in the formula, and everyone who can open the spreadsheet can read it.
• No say over the timing. Google Sheets decides when to fetch the address again. You cannot ask for every hour, or for Monday at 8 AM.
• No say over the columns. You get what the address returns, as it returns it.

For JSON, for an API that wants a key in a header, or for a refresh on a timetable, you write a script or use an add-on.

2. The Apps Script way: an API with a key, into a tab

1. In your spreadsheet, choose Extensions → Apps Script.
2. Keep the key out of the code. Paste this function, put your key in it, click Run once, then delete the key from the code again. It stores the key in your own user properties, which other editors of the script cannot read.

   function saveToken() {
     PropertiesService.getUserProperties().setProperty('API_TOKEN', 'paste-your-token-here');
   }

3. Add the import itself. Change the address, the path to the list (data.items here), the columns and the tab name, and click Save.
4. Click Run on importOrders. The first time, Google asks you to allow the script to see your spreadsheets and connect to an external service. Then look at the tab.

function importOrders() {
  const token = PropertiesService.getUserProperties().getProperty('API_TOKEN');
  const res = UrlFetchApp.fetch('https://api.example.com/v1/orders?limit=250', {
    headers: { Authorization: 'Bearer ' + token },
    muteHttpExceptions: true
  });
  if (res.getResponseCode() !== 200) {
    throw new Error('HTTP ' + res.getResponseCode() + ': ' + res.getContentText().slice(0, 200));
  }
  const items = JSON.parse(res.getContentText()).data.items;   // the list you want
  const cols = ['id', 'status', 'total'];                       // the fields you want
  const rows = items.map(item => cols.map(c => item[c] ?? ''));
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Orders');
  sheet.clearContents();
  sheet.getRange(1, 1, rows.length + 1, cols.length).setValues([cols, ...rows]);
}

For a CSV address, replace the JSON.parse line with Utilities.parseCsv(res.getContentText()), which already gives you rows.

3. Make it run on a schedule

1. In the Apps Script editor, click Triggers (the clock icon on the left), then Add Trigger.
2. Choose the function importOrders, the event source Time-driven, the type Hour timer and Every hour.
3. Click Save.

From then on Google runs the script every hour, while the spreadsheet is closed. A Day timer imports daily and a Week timer weekly. The trigger can also be made in code: ScriptApp.newTrigger('importOrders').timeBased().everyHours(1).create().

4. Where the simple script falls short

• Nested JSON. item[c] reads only the top level. A field such as customer.name needs code that walks into objects, and lists inside a record need joining, or you get [object Object] in a cell.
• Pages. Most APIs return 50 to 250 records at a time, with a next-page link, a cursor or a page number. Reading them all means a loop that knows when to stop, and that never follows a next link to another site with your key.
• It wipes the tab. clearContents() clears your own notes and formulas on that tab too. Clearing only the block the script wrote last time means remembering where it was.
• History needs more code. Adding new rows under the old ones, skipping the IDs already there, or keeping a dated copy of every run for a chart, is a different program from replacing the tab.
• Formulas from the answer. setValues writes a text that starts with = as a formula. A value from an API can end up as a working formula in your sheet unless you escape it.
• Errors go quietly. When a timed run fails, Google emails a failure summary to the person who made the trigger, by default once a day. The tab simply keeps the old data, and nobody else knows.
• Limits. A run stops after about 6 minutes, and an account has a daily number of web requests: 20,000 for a personal account and 100,000 for Google Workspace™. A busy API that answers 429 needs a wait and a retry.
• One script per source. Each new API, or each change of columns, means editing code, and each spreadsheet needs its own copy and its own trigger.

None of this is hard on its own. Together, it is a small program to keep up every time someone needs one more feed.

5. With an add-on: LoadLane

LoadLane is our Google Sheets™ add-on for this job. It does what the script above leaves to you:

• Any https:// address that answers with CSV, TSV or JSON, with parameters and headers such as Authorization: Bearer … or X-API-Key. Header values and secret parameters are kept in your own Google account, never in the spreadsheet, and sent only to the site you saved them for.
• Preview of the first 20 rows. Nested JSON becomes columns such as customer.name; tick, rename and reorder them. For JSON with several lists, click the one you want.
• Replace clears only the block LoadLane wrote last time. With Pro, Append adds new rows and can skip IDs already in the sheet, and Snapshot keeps a history with an Imported at time.
• Pages (Pro): next-page links, cursors, page numbers, offsets or the Link header, up to 50 pages. A next page on another site is refused.
• Text that looks like a formula is written as text. Every source shows its last result and its last error, with the HTTP code and the first 200 characters of the site’s answer, keys removed.
• Import now is free, for up to 3 sources per spreadsheet. Pro, for $4.99 a month or $39 a year, adds schedules (every hour, every few hours, daily or weekly), Append and Snapshot, POST requests, pages, up to 25 sources and a run log.

How to set it up: LoadLane help.

LoadLane — URL to SheetsAPI and JSON imports on a schedule, without a script.

Read the setup guide