Source: https://oguzerol.com/en/google-sheets-import-json-api/
[
View full size](https://oguzerol.com/gorseller/hizmet-kapaklari/gunluk-api-veri-aktarma-uygulamasi-webapp-v1-en.webp)
# Google Sheets Import JSON from an API with Apps Script
[Oğuz Erol](https://oguzerol.com/en/about-me/) | 3 min read | Published: 9 October 2026
## Quick summary
Import JSON API data into Google Sheets with Apps Script: validate responses, map nested fields and define clear rules when scheduled updates fail.
Importing JSON from an API into Google Sheets means fetching structured information through an authorised connection and writing selected fields to cells. A response might contain a product code and stock quantity. Before running a script, establish the endpoint, response structure and conditions under which you may use it.
## Inspect the fields first
Our illustrative response is `[{"code":"K101","stock":24},{"code":"A101","stock":8}]`. Your source may use other names, nested objects or several pages. Confirm the actual response rather than assuming it matches an example.
Open Extensions → Apps Script and create a target sheet named `Stock`. Replace the URL below with your authorised endpoint. The example does not implement authentication or scheduling. Product codes must be text containing only letters, digits, underscores or hyphens; stock must be numeric. Use a separate, empty target sheet: results are written starting at A1.
```
function importStock() {
const response = UrlFetchApp.fetch('https://example.com/stock.json',
{muteHttpExceptions: true});
if (response.getResponseCode() !== 200) throw new Error('Source unavailable');
const data = JSON.parse(response.getContentText());
if (!Array.isArray(data) || !data.length) throw new Error('List missing');
const rows = data.map(item => {
if (!item || typeof item !== 'object' ||
typeof item.code !== 'string' || !/^[-A-Za-z0-9_]+$/.test(item.code) ||
typeof item.stock !== 'number' || !Number.isFinite(item.stock)) {
throw new Error('Invalid fields');
}
return [item.code, item.stock];
});
const sheet = SpreadsheetApp.getActive().getSheetByName('Stock');
if (!sheet) throw new Error('Target sheet missing');
sheet.getRange(1, 1, rows.length, 2).setValues(rows);
}
```
Google’s [UrlFetchApp reference](https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app) covers the request; the [Range reference](https://developers.google.com/apps-script/reference/spreadsheet/range#setValues(Object%29) covers writing values. Review the permissions requested on the first run.
## Understand the example’s limits
The script does not clear the table before validating the response. It also does not remove old trailing rows when the new list is shorter. Decide whether a missing product was deleted at the source or simply omitted from a partial response before removing it from the target.
Test repeated runs, HTTP errors, invalid JSON and empty lists separately. If stock arrives as text, define the conversion rule explicitly. Missing data is not automatically zero stock. Duplicate product codes and paginated responses also require handling before this becomes a production import.
Keep stable product identifiers instead of matching only on display names. A renamed product should still be recognised as the same record. If staff maintain descriptions in the target sheet, decide whether an import may replace them. Field ownership prevents a new source copy from overwriting useful local work.
## Show the outcome of scheduled work
Apps Script can support scheduled updates, but quotas, expired access and response delays affect completion. Keep the last successful import separate from the latest attempted run. Retained data after an error should carry an old-data indication rather than silently appearing current.
A single source and simple table may remain in a small script. Review queues, permissions and controlled reprocessing can justify a [daily API import application](https://oguzerol.com/en/scheduled-api-data-import-app/). Two-way updates are a different scope, covered by [Sheets synchronisation](https://oguzerol.com/en/google-sheets-two-way-sync-app/). For the wider team process, see [Excel to a web app](https://oguzerol.com/en/turn-your-excel-into-a-web-app/).
## Frequently Asked Questions
### Do all APIs return the same JSON structure?
No. Field names, pagination and access rules belong to the source.
### Should a failed request set stock to zero?
Unavailable information and zero stock should be distinct states. Define how staff may use retained data.
## Discuss your workflow
[Discuss your source and target table](https://oguzerol.com/en/contact/).
[Back to top ↑](https://oguzerol.com/en/google-sheets-import-json-api/#icerik)
## You may also find these useful
[Google Sheets Import Data from a Website: IMPORTHTMLUse IMPORTHTML to import website data into Google Sheets. Check source tables, refresh behaviour and stale data before relying on daily results.](https://oguzerol.com/en/google-sheets-import-data-from-website/)[Google Sheets Email Notifications and Threshold AlertsCompare Google Sheets email notifications with threshold alerts. Define recipients, repeat-warning rules, incident ownership and closure for your team.](https://oguzerol.com/en/google-sheets-threshold-alerts/)[Scheduled API Data Imports with a Monitoring ScreenSchedule API imports with visible run history, validation and retry controls. Keep accepted records separate from exceptions and see when data last arrived.](https://oguzerol.com/en/scheduled-api-data-import-app/)
[Browse all articles →](https://oguzerol.com/en/articles/)