Fetch JSON Data from an API

Most REST APIs return data as JSON. With UrlFetchApp.fetch() and JSON.parse(), you can retrieve any API response and work with it as a regular JavaScript object in your script.

Basic JSON Fetch

function fetchPublicApi() {
  var url = "https://restcountries.com/v3.1/name/canada";
  var response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });

  if (response.getResponseCode() !== 200) {
    Logger.log("Request failed: " + response.getResponseCode());
    return;
  }

  var data = JSON.parse(response.getContentText());
  var country = data[0];

  Logger.log("Country: " + country.name.common);
  Logger.log("Capital: " + country.capital[0]);
  Logger.log("Population: " + country.population);
}

Fetching Company Data for Each Customer Domain

This example extracts the email domain for each customer in the Sales Tracker and calls a company enrichment API to fetch company details:

function enrichCustomerData() {
  var sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Sales Tracker");

  var data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 8).getValues();
  // Columns: [Customer Name, Email, Product, Amount, Region, Sales Rep, Status, Date]

  // Ensure a Company column exists (column J)
  sheet.getRange(1, 10).setValue("Company Domain");

  data.forEach(function(row, i) {
    var email = row[1];
    if (!email) return;

    var domain = email.split("@")[1];

    // Example: using a public domain lookup API
    var url = "https://autocomplete.clearbit.com/v1/companies/suggest?query=" + domain;
    var response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });

    if (response.getResponseCode() !== 200) return;

    var results = JSON.parse(response.getContentText());
    var companyName = results.length > 0 ? results[0].name : domain;

    sheet.getRange(i + 2, 10).setValue(companyName);
    Logger.log("Enriched " + row[0] + " → " + companyName);

    Utilities.sleep(300); // Respect rate limits
  });
}

Fetching Currency Exchange Rates

Useful for converting deal amounts to a base currency in multi-region sales:

function fetchExchangeRates() {
  var url = "https://open.er-api.com/v6/latest/USD";
  var response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });

  if (response.getResponseCode() !== 200) {
    Logger.log("Failed to fetch rates.");
    return;
  }

  var data = JSON.parse(response.getContentText());
  var rates = data.rates;

  Logger.log("GBP: " + rates.GBP);
  Logger.log("EUR: " + rates.EUR);
  Logger.log("CAD: " + rates.CAD);

  return rates;
}

Fetching Paginated Results

Some APIs return results across multiple pages. Iterate with a page parameter:

function fetchAllDealsFromCRM() {
  var allDeals = [];
  var page = 1;
  var hasMore = true;

  while (hasMore) {
    var url = "https://api.example-crm.com/deals?page=" + page + "&per_page=50";
    var options = {
      method: "GET",
      headers: { "Authorization": "Bearer YOUR_API_TOKEN" },
      muteHttpExceptions: true
    };

    var response = UrlFetchApp.fetch(url, options);
    if (response.getResponseCode() !== 200) break;

    var data = JSON.parse(response.getContentText());
    allDeals = allDeals.concat(data.deals);

    hasMore = data.has_more;
    page++;

    Utilities.sleep(200);
  }

  Logger.log("Total deals fetched: " + allDeals.length);
  return allDeals;
}

Safely Accessing Nested JSON Properties

Avoid errors when a property might be missing:

function safeGet(obj, path, fallback) {
  return path.split(".").reduce(function(acc, key) {
    return acc && acc[key] !== undefined ? acc[key] : fallback;
  }, obj);
}

// Usage
var data = JSON.parse(response.getContentText());
var companyName = safeGet(data, "company.name", "Unknown");
var region = safeGet(data, "address.region", "N/A");