API calls from Apps Script in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a data analyst at a retail company.

📋 Request: Your manager wants you to automatically fetch the latest currency exchange rates from a public API into Google Sheets every day.

📊 Data: You have a Google Sheet with columns for Currency Code and Exchange Rate. The Currency Code column lists currencies like USD, EUR, GBP, etc. You want to fill the Exchange Rate column with the latest rates against your base currency (e.g., USD).

🎯 Deliverable: Create a Google Apps Script that calls a free currency exchange API, retrieves the latest rates, and updates the Exchange Rate column in the sheet automatically.

Sample Data

Currency Code	Exchange Rate
EUR
GBP
JPY
AUD
CAD
CHF
CNY
INR

1

Step 1: Open the Google Sheet and go to Extensions > Apps Script to open the script editor.

No formula needed here.

Expected Result

You see a blank script editor ready for your code.

2

Step 2: Write a function named fetchExchangeRates that calls the free ExchangeRate-API to get latest rates against USD.

function fetchExchangeRates() { const url = 'https://open.er-api.com/v6/latest/USD'; const response = UrlFetchApp.fetch(url); const data = JSON.parse(response.getContentText()); return data.rates; }

Expected Result

The function fetchExchangeRates returns an object with currency codes as keys and exchange rates as values.

3

Step 3: Write a function named updateSheet that reads currency codes from column A and writes the corresponding exchange rates in column B.

function updateSheet() { const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); const lastRow = sheet.getLastRow(); const currencies = sheet.getRange(2, 1, lastRow - 1).getValues(); const rates = fetchExchangeRates(); const output = currencies.map(row => [rates[row[0]] || 'N/A']); sheet.getRange(2, 2, output.length).setValues(output); }

Expected Result

Column B is filled with the latest exchange rates matching the currency codes in column A.

4

Step 4: Run the updateSheet function manually from the Apps Script editor to test it.

No formula, just click the run button on updateSheet function.

Expected Result

The Exchange Rate column in the sheet updates with current rates for each currency code.

5

Step 5: Set a time-driven trigger to run updateSheet automatically every day.

In Apps Script editor, go to Triggers > Add Trigger > Choose updateSheet > Select Time-driven > Day timer > Select preferred time > Save.

Expected Result

The sheet updates exchange rates automatically every day without manual action.

Final Result

Currency Code | Exchange Rate
--------------|--------------
EUR           | 0.92
GBP           | 0.81
JPY           | 134.50
AUD           | 1.45
CAD           | 1.34
CHF           | 0.91
CNY           | 7.15
INR           | 82.30

✓The sheet now shows up-to-date exchange rates for each currency code.

✓Automation saves time by updating rates daily without manual input.

✓Using Apps Script and API calls makes Google Sheets a powerful live data tool.

Bonus Challenge

Modify the script to also fetch and display the date and time when the rates were last updated from the API response.

Show Hint

The API response has a 'time_last_update_utc' field. Use sheet.getRange(row, col).setValue() to write it in a cell.