UrlFetchApp — pulling live data from an API

1. What is an API?

A URL that returns data (usually JSON) instead of a web page. Exchange rates, weather, stock prices, your own ERP or shop website — if it has an API, Sheets can pull from it.

2. First fetch

This example uses Frankfurter, a free exchange-rate API that needs no key (check its website if the address changes):

function getUsdInr() {
  const url = 'https://api.frankfurter.app/latest?from=USD&to=INR';
  const res = UrlFetchApp.fetch(url, { muteHttpExceptions: true });

  if (res.getResponseCode() !== 200) {
    throw new Error('API error ' + res.getResponseCode() + ': ' + res.getContentText());
  }
  const json = JSON.parse(res.getContentText());
  // json looks like: { amount: 1, base: "USD", date: "2026-09-30", rates: { INR: 83.9 } }
  Logger.log(json.rates.INR);
  return json;
}

• muteHttpExceptions: true — don't crash on 4xx/5xx; we check the code ourselves.
• JSON.parse turns the text into a JavaScript object you can read with dots.

3. Write it into a sheet as a history log

function logRate() {
  const json = getUsdInr();
  const sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Rates')
          || SpreadsheetApp.getActiveSpreadsheet().insertSheet('Rates');
  if (sh.getLastRow() === 0) sh.appendRow(['Fetched at', 'Rate date', 'USD→INR']);
  sh.appendRow([new Date(), json.date, json.rates.INR]);
}

Add a daily time trigger (Lesson 4) on logRate, and you build a rate history automatically. Chart it with Insert → Chart.

4. APIs that return a list

Many APIs return an array of objects. Convert it into rows for setValues:

// items = [{ id: 1, name: 'Laptop', price: 55000 }, { id: 2, name: 'Mobile', price: 18000 }]
const rows = items.map(o => [o.id, o.name, o.price]);
sh.getRange(2, 1, rows.length, 3).setValues(rows);

5. API keys and headers

Most real APIs need a key. Never hard-code it in the script (anyone with edit access can see it). Store it in Script Properties:

Project Settings ⚙️ → Script Properties → Add API_KEY = your key. Then:

const key = PropertiesService.getScriptProperties().getProperty('API_KEY');
const res = UrlFetchApp.fetch('https://api.example.com/v1/products', {
  method: 'get',
  headers: { Authorization: 'Bearer ' + key },
  muteHttpExceptions: true
});

Sending data (POST):

UrlFetchApp.fetch('https://api.example.com/v1/orders', {
  method: 'post',
  contentType: 'application/json',
  payload: JSON.stringify({ product: 'P101', qty: 2 }),
  headers: { Authorization: 'Bearer ' + key },
  muteHttpExceptions: true
});

6. Be polite to APIs

• Check the API's rate limit and terms of use.
• Don't call the API inside a loop for each row; fetch once and process in memory.
• Cache results you reuse within a few minutes:

const cache = CacheService.getScriptCache();
let text = cache.get('usdinr');
if (!text) {
  text = UrlFetchApp.fetch(url).getContentText();
  cache.put('usdinr', text, 600);          // 10 minutes
}

• Apps Script has daily UrlFetch quotas too (tens of thousands of calls per day — rarely a problem if you batch).

7. As a custom function

UrlFetchApp is allowed inside custom functions (Lesson 3):

/** @customfunction */
function FXRATE(from, to) {
  const res = UrlFetchApp.fetch(`https://api.frankfurter.app/latest?from=${from}&to=${to}`);
  return JSON.parse(res.getContentText()).rates[to];
}

=FXRATE("USD","INR"). Note it won't auto-refresh unless its arguments change — use a trigger for regular updates.

Common mistakes

Hard-coding API keys. Not checking the response code, then failing on JSON.parse of an error page. Calling the API once per row. Expecting a custom function to refresh by itself.