What trigger should I use for Fetch External REST API JSON Data Directly into Google Sheets?

This script is designed for: Hourly Trigger. Configure it under Apps Script > Triggers (clock icon).

API & Webhooks AutomationDifficulty: Intermediate⚡ Hourly Trigger

Fetch External REST API JSON Data Directly into Google Sheets

Q: What OAuth permissions does Fetch External REST API JSON Data Directly into Google Sheets require?

This script requests the following authorized scopes: https://www.googleapis.com/auth/script.external_requests, https://www.googleapis.com/auth/spreadsheets.

Pulls data from any public or authenticated REST API (weather, currency, stock prices, or internal microservices) and populates spreadsheet rows on a schedule.

Required OAuth Scopes: https://www.googleapis.com/auth/script.external_requests • https://www.googleapis.com/auth/spreadsheets

Code.gs (Apps Script V8 Runtime)

function fetchLiveExchangeRatesToSheet() {
  const apiUrl = 'https://open.er-api.com/v6/latest/USD';
  const response = UrlFetchApp.fetch(apiUrl);
  const json = JSON.parse(response.getContentText());
  
  if (json.result !== 'success') throw new Error('API failed');
  
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('FX_Rates');
  const rates = json.rates;
  const rows = [['Currency', 'Rate to USD', 'Last Updated']];
  const timestamp = new Date();
  
  for (const curr in rates) {
    rows.push([curr, rates[curr], timestamp]);
  }
  
  sheet.clearContents();
  sheet.getRange(1, 1, rows.length, 3).setValues(rows);
}

Line-by-Line Code Explanation

Line 2-4: Executes HTTP GET and parses JSON response.

Line 10-15: Transforms nested JSON object into a 2D array matrix.

Line 17-18: Overwrites spreadsheet tab with fresh rates.

Step-by-Step Setup & Deployment

Create a sheet tab named "FX_Rates".
Paste the code into Apps Script and set an hourly trigger.

Best Practices for Quota & Rate Limit Management

When deploying this automation in Google Workspace, adhere to these operational principles:

Batch Reads & Writes: Never execute getValue() or setValue() inside loops. Read ranges into memory in 1 call, process arrays in V8 memory, and commit changes with a single setValues() call.
Execution Timeouts: Apps Script terminates scripts exceeding 6 minutes. For multi-thousand row datasets, implement batch pagination with resume timestamps stored in PropertiesService.
OAuth Permissions: Scope your manifest (appsscript.json) narrowly to prevent security prompts from alarming external team members.

Frequently Asked Questions

Is Google Apps Script completely free to use?

Yes. Google Apps Script is built into all free Google accounts and Google Workspace editions. There are no server fees or subscription costs for running scripts within daily quotas.

Can I run this script on an automated schedule without keeping my computer on?

Yes. Time-driven triggers execute on Google's cloud infrastructure. Your script will run reliably even when your computer is closed or offline.

More API & Webhooks Automation Snippets

API & Webhooks