The Limitation of Static Data
Google Sheets is a phenomenal tool for organizing data, but manually copying and pasting information from external websites is inefficient. If you are tracking the live price of a cryptocurrency, the current exchange rate of the Euro, or the daily weather forecast for a shipping route, that data changes constantly.
Instead of manually updating the spreadsheet, you can transform Google Sheets into a live, dynamic dashboard by connecting it directly to external REST APIs using Google Apps Script.
Using the built-in UrlFetchApp class, Apps Script can reach out across the internet, query a public API, extract the precise JSON data you need, and write it directly into your spreadsheet cells automatically.
Step 1: Understand the API Endpoint
For this tutorial, we will use a free, public API that returns the current location of the International Space Station (ISS).
The API endpoint is: http://api.open-notify.org/iss-now.json
If you click that link in a web browser, you will see it returns a simple JSON object containing the timestamp and the exact latitude and longitude of the station.
Step 2: Write the Apps Script
We will write a script that fetches this data and writes it into a Google Sheet.
- Create a new Google Sheet. Name the first tab “Live Data”.
- In Row 1, set up your headers:
- A1: Timestamp
- B1: Latitude
- C1: Longitude
- Click on Extensions > Apps Script.
- Delete the default code and paste the following:
function fetchISSLocation() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Live Data");
// 1. Define the API endpoint URL
var apiUrl = "http://api.open-notify.org/iss-now.json";
// 2. Fetch the data from the internet
var response = UrlFetchApp.fetch(apiUrl);
// 3. Parse the raw text response into a usable JSON object
var json = JSON.parse(response.getContentText());
// 4. Extract the specific data points we care about
// We navigate the JSON tree using dot notation
var latitude = json.iss_position.latitude;
var longitude = json.iss_position.longitude;
// The API returns the time as a Unix timestamp, we convert it to a readable Date
var timestamp = new Date(json.timestamp * 1000);
// 5. Append the new data to the bottom of the spreadsheet
sheet.appendRow([timestamp, latitude, longitude]);
}
Understanding the Code
UrlFetchApp.fetch()is the engine. It performs standard HTTP GET requests (and can be configured to perform POST requests with headers and authentication tokens for more complex APIs).JSON.parse()is crucial. APIs return raw text strings. This function converts that text into a JavaScript object, allowing you to extract data easily using dot notation (e.g.,json.iss_position.latitude).appendRow()finds the last empty row in the sheet and drops the array of data into it perfectly.
Click the Save (floppy disk) icon.
Step 3: Test and Authorize
- Click the Run button at the top of the editor.
- Google will block the script and require authorization because the script is attempting to connect to an external service (a potential security risk if you didn’t write the code yourself).
- Click Review Permissions, select your account, click Advanced, and click Go to script to allow it.
Return to your Google Sheet. You will see a new row has been added, displaying the exact, real-time location of the ISS.
Step 4: Automate the Pull
To make the dashboard truly live, configure the script to run automatically.
- In the Apps Script editor, click the Clock icon (Triggers) on the left sidebar.
- Click + Add Trigger in the bottom right.
- Choose the function
fetchISSLocation. - Set the event source to Time-driven.
- Set the type of time based trigger to Minutes timer.
- Select Every 5 minutes (or whatever interval the API allows).
- Click Save.
Conclusion
By leveraging UrlFetchApp, Google Sheets transforms from a static ledger into a dynamic, internet-connected database. Whether you are pulling live stock prices, CRM data from Salesforce, or shipping logistics from FedEx, Apps Script allows you to centralize the world’s data directly inside your spreadsheet.