How to Pull API Data into Google Sheets

Bringing live data directly into Google Sheets automates your reporting and unlocks powerful custom dashboards without the busywork of manually exporting CSVs. Instead of copy-pasting numbers from different platforms, you can use Application Programming Interfaces (APIs) to pipe information directly into your spreadsheet. This article walks you through why this is so useful and covers three different methods - from simple formulas to custom code - for pulling API data into Google Sheets.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Bother Pulling API Data into Google Sheets?

Manually updating reports is a huge time-sink. You log into different apps, find the right date range, export a file, clean it up, and stitch everything together. It's a process that's slow, tedious, and prone to human error. By connecting directly to APIs, you create a system that updates itself - so you're always working with fresh, accurate data.

It's an incredibly practical skill for anyone in marketing, sales, or operations. You can:

• Build a performance dashboard: Pull advertising spend from Facebook Ads, campaign results from Google Analytics, and sales numbers from Shopify into one master view.
• Track your sales pipeline: Sync deal data from your CRM like Salesforce or HubSpot to keep a real-time leaderboard or funnel analysis sheet.
• Monitor social media engagement: Get live data on likes, shares, and comments from various social media APIs to create a single report.
• Consolidate financial data: Combine revenue from Stripe with expenses from QuickBooks for an up-to-the-minute look at your business health.

At its core, pulling API data into Google Sheets turns your spreadsheet from a static document into a dynamic, automated tool that does the data gathering for you.

First, What Even Is an API?

Before diving into the "how," let's quickly demystify what an API is. Think of an API as a waiter in a restaurant.

You (your Google Sheet) don't go directly into the kitchen (the application's server) to get your food (the data). Instead, you give your order to the waiter (the API). The waiter takes your request to the kitchen, gets the specific dishes you asked for, and brings them back to your table. The API is the intermediary that lets different applications talk to each other safely and efficiently.

Here are a few terms you'll encounter:

• Endpoint: This is the specific URL you send your "order" to. For example, a weather API might have different endpoints for getting the current forecast versus the historical daily temperature.
• API Key: Most APIs require an API key to track usage and prevent abuse. It's a unique string of letters and numbers you include with your request, acting like a password to prove you have permission to access the data.
• JSON (JavaScript Object Notation): This is the most common format you'll get data back in. It’s a clean, human-readable text format that organizes data in key-value pairs, making it easy for Google Sheets to understand and parse.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Method 1: The Simple Built-in Formula (IMPORTDATA)

The easiest way to get started is by using Google Sheets' built-in IMPORTDATA function. This function takes one argument: the URL of the data you want to import. This is best for public APIs or endpoints that don't require any special authorization besides what can be included in the URL itself.

How to Use It

Imagine you want to pull data from a public API listing United States population data. The API endpoint might look something like this: https://datausa.io/api/data?drilldowns=State&measures=Population. This URL returns a block of JSON data.

You can't directly use IMPORTDATA with a JSON file, as it’s designed for CSV or TSV formats. However, many simple REST APIs that return a CSV are compatible.

For example, if an API provides a link directly to a CSV file like this one for S&P 500 company info: https://pkgstore.datahub.io/core/s-and-p-500-companies/constituents_csv/data/constituents_csv.csv

You can simply type this formula into a cell:

=IMPORTDATA("https://pkgstore.datahub.io/core/s-and-p-500-companies/constituents_csv/data/constituents_csv.csv")

Google Sheets will automatically fetch the file and populate the cells with the data.

Limitations of IMPORTDATA

• Limited Authentication: It doesn't work well with APIs that require headers for authentication (like passing an API Key).
• Format Dependent: It’s designed for structured files like CSVs or TSVs, not the more common JSON format.
• Static Data: The data doesn't refresh on a schedule. It only updates when the formula is re-evaluated, which typically happens when the sheet is reopened.

This method is great for quick, one-off imports from simple, public data sources, but for anything more robust, you'll need a different approach.

Method 2: Full Control with Google Apps Script

For complete flexibility and power, Google Apps Script is the way to go. It's a scripting language based on JavaScript that runs on Google's servers, allowing you to create custom functions and automate workflows within Google Workspace.

With Apps Script, you can make complex API calls, handle authorization with API keys, parse JSON data, and schedule your script to run automatically.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step-by-Step Guide to Using Apps Script

Let's walk through building a script that pulls cryptocurrency prices from the CoinGecko API, which uses a simple public endpoint.

1. Open the Script Editor

In your Google Sheet, go to Extensions > Apps Script. This will open a new tab with the script editor.

2. Write the Function to Fetch Data

Delete any boilerplate code and paste the following function into the editor:

`function getCryptoPrices() { // Define the API endpoint URL var url = "https://api.coingecko.com/api/v3/simple/price?ids=bitcoin,ethereum,cardano&vs_currencies=usd",

// Make the API request var response = UrlFetchApp.fetch(url),

// Parse the JSON string into an object var data = JSON.parse(response.getContentText()),

// Get the active sheet to write to var sheet = SpreadsheetApp.getActiveSheet(),

// Clear previous content to avoid clutter sheet.clear(),

// Set headers sheet.getRange("A1").setValue("Cryptocurrency"), sheet.getRange("B1").setValue("Price (USD)"),

// Loop through the data and write it to the sheet var rowIndex = 2, // Start writing from the second row for (var crypto in data) { sheet.getRange(rowIndex, 1).setValue(crypto), // Column A sheet.getRange(rowIndex, 2).setValue(data[crypto].usd), // Column B rowIndex++, } }`

This script does four things:

• It specifies the API URL we want to call.
• It uses UrlFetchApp.fetch() to get the data from that URL.
• It uses JSON.parse() to turn the response text into an object.
• It writes the cryptocurrency names and prices into columns A and B.

To use an API that requires authentication, you would modify the fetch call to include your key in the headers, like this:

`var options = { 'method' : 'get', 'headers' : { 'Authorization' : 'Bearer YOUR_API_KEY' }, 'muteHttpExceptions' : true },

var response = UrlFetchApp.fetch(url, options),`

3. Run the Script

Save your script by clicking the floppy disk icon. Then, select the getCryptoPrices function from the dropdown menu and click the "Run" button. The first time you run it, you’ll be asked to grant permissions for the script to access your spreadsheet. After authorizing, the data will appear in your sheet.

4. Automate It with Triggers

The real power comes from automation. In the script editor sidebar, click on the clock icon ("Triggers").

• Click "Add Trigger" in the bottom-right corner.
• Choose your function (e.g., getCryptoPrices).
• Select "Time-driven" as the event type.
• Choose your desired frequency, such as "Hour timer" and "Every hour."
• Click "Save."

Now, your script will automatically run every hour, keeping your sheet updated with the latest crypto prices without you having to lift a finger.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Method 3: The Best of Both Worlds with Add-ons

If you don't feel comfortable writing code but need more power than IMPORTDATA can offer, third-party Google Sheets add-ons are the perfect middle ground. Tools like Supermetrics or API Connector handle the complexity of authentication and scheduling through a user-friendly interface.

How Add-ons Generally Work

1. Install the Add-on: Find your chosen add-on in the Google Workspace Marketplace and install it.
2. Connect Your Accounts: Launch the add-on from the Extensions menu. You'll use its interface to connect to your data sources (like Google Ads, Shopify, or HubSpot) via a simple authentication process.
3. Build a Query: Instead of writing code, you'll use dropdown menus and form fields to select the exact data you want to pull - like choosing metrics, dimensions, and date ranges.
4. Run and Schedule: Run your query to pull the data directly into your sheet. Then, use the add-on's built-in scheduler to set up automatic daily, weekly, or hourly refreshes.

Add-ons simplify access to complex business APIs, making them ideal for marketers and analysts who want reliable data connections without getting into the technical weeds of scripting.

Final Thoughts

Automating your data flows into Google Sheets can transform your reporting from a dreaded manual process into an efficient, always-on system. Whether you use the simple IMPORTDATA for quick jobs, a powerful Google Apps Script for full control, or a user-friendly add-on, you're putting your data to work for you so you can focus on analysis, not data entry.

We know wrestling with APIs, scripts, and add-ons can still be complex and time-consuming. That's why we built Graphed. We connect directly to your marketing and sales platforms - like Google Analytics, Shopify, Facebook Ads, and Salesforce - and let you build real-time dashboards using simple, natural language. Instead of managing scripts or spreadsheet functions, you can just ask, "Show me a comparison of Facebook Ads spend versus campaign revenue from Shopify this month," and get a live, interactive dashboard in seconds. It's a great way to skip the setup and get straight to the insights.