Connect Google Sheets to an External REST API Using Apps Script

Google Sheets is great for storing and displaying data — but it becomes even more powerful when you can pull in live data from external APIs. Apps Script's UrlFetchApp lets you call any REST API and write the results straight into your spreadsheet.

The basics: UrlFetchApp

UrlFetchApp.fetch(url) makes an HTTP request and returns a HTTPResponse object. You can then parse the JSON body and write it to the sheet.

function fetchFromApi() {
  const url = 'https://api.exchangerate-api.com/v4/latest/USD';
  const response = UrlFetchApp.fetch(url);
  const data = JSON.parse(response.getContentText());

  Logger.log(data.rates.EUR); // e.g. 0.92
}

Example: Pull currency exchange rates into Sheets

function importExchangeRates() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.clearContents();
  sheet.appendRow(['Currency', 'Rate vs USD', 'Last Updated']);

  const url = 'https://api.exchangerate-api.com/v4/latest/USD';
  const response = UrlFetchApp.fetch(url);
  const data = JSON.parse(response.getContentText());

  const currencies = ['EUR', 'GBP', 'JPY', 'CAD', 'AUD'];
  const updated = new Date(data.time_last_updated * 1000);

  currencies.forEach(currency => {
    sheet.appendRow([currency, data.rates[currency], updated]);
  });

  Logger.log('Exchange rates imported successfully.');
}

Example: Fetch weather data

function importWeather() {
  const city = 'London';
  const apiKey = 'YOUR_OPENWEATHERMAP_API_KEY';
  const url = `https://api.openweathermap.org/data/2.5/weather?q=${city}&appid=${apiKey}&units=metric`;

  const response = UrlFetchApp.fetch(url);
  const data = JSON.parse(response.getContentText());

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.appendRow([
    city,
    data.main.temp + '°C',
    data.weather[0].description,
    new Date()
  ]);
}

Making authenticated requests

Many APIs require an API key or Bearer token. Pass these in the request headers:

function fetchWithAuth() {
  const apiKey = 'YOUR_API_KEY';
  const url = 'https://api.example.com/data';

  const options = {
    method: 'GET',
    headers: {
      'Authorization': `Bearer ${apiKey}`,
      'Content-Type': 'application/json'
    },
    muteHttpExceptions: true // Prevents script from throwing on 4xx/5xx errors
  };

  const response = UrlFetchApp.fetch(url, options);
  const statusCode = response.getResponseCode();

  if (statusCode !== 200) {
    Logger.log(`Error: ${statusCode} - ${response.getContentText()}`);
    return;
  }

  const data = JSON.parse(response.getContentText());
  Logger.log(data);
}

POST requests: sending data to an API

function postToApi() {
  const url = 'https://api.example.com/submit';
  const payload = {
    name: 'John Doe',
    email: '[email protected]'
  };

  const options = {
    method: 'POST',
    contentType: 'application/json',
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  };

  const response = UrlFetchApp.fetch(url, options);
  Logger.log(response.getContentText());
}

Refresh data on a schedule

function createHourlyRefresh() {
  ScriptApp.newTrigger('importExchangeRates')
    .timeBased()
    .everyHours(1)
    .create();
}

Tips

Always use muteHttpExceptions: true and check response.getResponseCode() to handle errors gracefully.
Store API keys in PropertiesService rather than hardcoding them in the script — see our article on using PropertiesService to store secrets.
Apps Script has a daily URL fetch quota. If you're hitting it, reduce your trigger frequency or cache results in the sheet.
UrlFetchApp supports HTTP and HTTPS. Most modern APIs require HTTPS.