How to Import Data into Google Sheets
Tired of manually copying and pasting data into your spreadsheets? Google Sheets is much more than a place to crunch numbers, it's a powerful hub for pulling in data from all over the web. This guide will walk you through several methods for importing data into Google Sheets, from simple uploads to fully automated, live-updating connections.
The Basics: Manual Imports
Let's start with the most common, everyday methods. These are perfect for one-off data pulls or when you're working with static files.
Method 1: The Classic Copy & Paste
This seems obvious, but a few pro tips can save you from formatting headaches. When you copy data from a webpage or another application and paste it directly into Google Sheets, you often bring over unwanted styles, fonts, and cell merging.
Instead, use Paste special for a clean import:
Copy the data from your source (using Ctrl+C or Cmd+C).
In your Google Sheet, select the cell where you want the data to start.
Right-click the cell, hover over Paste special, and choose Values only. Alternatively, use the keyboard shortcut: Ctrl+Shift+V (or Cmd+Shift+V on Mac).
This technique strips away all the formatting and pastes only the raw text and numbers, fitting them neatly into your existing sheet's style.
Method 2: Importing a File (CSV, XLS, etc.)
When you have a data file, like a CSV export from another tool or an Excel workbook, you can import it directly. This method gives you more control over where and how the data appears.
In Google Sheets, go to File > Import.
The "Import file" dialog will pop up. Select the Upload tab.
Drag your file into the window or click "Select a file from your device" to browse for it. Supported file types include .csv, .tsv, .txt, .xls, .xlsx, and more.
Once uploaded, you'll see the "Import file" settings box. Here are your options:
Import location:
Create new spreadsheet: This makes an entirely new Google Sheets file from your uploaded data. Great for keeping things separate.
Insert new sheet(s): This adds the data as a new tab (or tabs) in your current spreadsheet. This is the most common and useful option for consolidating reports.
Replace spreadsheet: This will delete all existing tabs in your current sheet and replace them with the data from the file. Use this with caution!
Replace current sheet: This overwrites only the data in the tab you are currently viewing.
Append rows to current sheet: This adds the data from your file to the first empty row below your existing data on the current tab. Perfect for adding new data to a running log.
Separator type: For CSV or plaintext files, Google Sheets usually does a good job of auto-detecting how the data is separated (e.g., by a comma or a tab). You can manually select the correct one if it gets it wrong.
Convert text to numbers, dates, and formulas: Keep this checked (it's the default). It tells Sheets to automatically format your data, so '8/25/2024' is treated as a date and not just text.
Click Import data, and your file will be neatly placed in your sheet.
Going Dynamic: Using Built-In Import Functions
This is where Google Sheets really starts to shine. Instead of manually uploading data, you can write formulas that pull information directly from other sources. The best part? The data can refresh automatically, meaning your sheet stays up to date without you lifting a finger.
IMPORTRANGE: The Best Way to Sync Two Google Sheets
Have you ever needed data from one Google Sheet to appear in another? Maybe you have a master dashboard and want to pull in KPIs from separate reports managed by different team members. IMPORTRANGE is the function for this job.
It "pulls" a range of cells from one spreadsheet into another. That means your destination sheet always has the latest data from the source sheet.
How to Use It:
The formula has two parts:
=IMPORTRANGE("spreadsheet_url", "range_string")
spreadsheet_url: The full URL of the Google Sheet you want to pull data from. Just copy it from your browser's address bar and paste it inside quotes.
range_string: The exact tab name and cell range you want to import. The format is
'Tab Name'!A1:D50. Remember to put the text inside quotes.
Example in Action:
Let's say you want to import cells A1 through F100 from a sheet named "Q3 Sales Data" located at https://docs.google.com/spreadsheets/d/123xyz-abc/...
Your formula would look like this:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/123xyz-abc/edit", "'Q3 Sales Data'!A1:F100")
Heads Up: The very first time you connect two sheets, you'll see a #REF! error with a message that says "You need to connect these sheets." Simply hover your mouse over the cell and click the blue "Allow access" button. This is a one-time security step per connection.
IMPORTHTML: Scrape Tables and Lists from Websites
Want to grab a table of data directly from a webpage? IMPORTHTML lets you do just that. It's incredibly useful for pulling public data, like financial tables, sports statistics, or any other data presented in a structured HTML table or list.
How to Use It:
The formula structure is:
=IMPORTHTML("url", "query", index)
url: The full URL of the webpage containing the table or list.
query: This must be either
"table"or"list", depending on what you're trying to scrape.index: A number that tells Google which table or list on the page you want. Since a webpage can have multiple tables, the first one is
1, the second is2, and so on. You might need to experiment a bit to find the right index number.
Example in Action:
Let's grab the list of countries by population from Wikipedia. The URL is https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_population. By checking the page's source code (or just through trial and error), we find that the main data table is the first table on the page.
The formula would be:
=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_population", "table", 1)
Within seconds, the entire table will appear in your sheet. The data will refresh periodically (around every hour), making it great for tracking data that changes over time.
IMPORTDATA: Pull Data from a CSV File URL
If the data you need is available as a CSV or TSV file hosted online, IMPORTDATA is the simplest way to get it. It takes a single argument: the URL of the file.
How to Use It:
=IMPORTDATA("url_of_csv_file")
Example in Action:
Let's say a government agency publishes daily weather stats as a CSV file at http://example.com/data/weather_stats.csv. To pull this data into your sheet, your formula would be:
=IMPORTDATA("http://example.com/data/weather_stats.csv")
That's it. As long as the file at the URL is publicly accessible, Sheets will import its contents. Like IMPORTHTML, the data refreshes about once an hour.
Advanced Methods: Add-Ons and Scripts
When you need to connect to services like your CRM, ad platforms, or other business apps, the built-in functions might not be enough. That's when you turn to more powerful solutions.
1. Connect APIs with Add-ons
The Google Workspace Marketplace is filled with third-party "add-ons" that extend the functionality of Google Sheets. Many of these are built specifically to connect to other applications and import data via their APIs, with no code required.
You can find add-ons that connect to:
Google Analytics
Salesforce & HubSpot
QuickBooks
Facebook Ads & Google Ads
And many, many more.
To find them, just go to Extensions > Add-ons > Get add-ons and search for the service you want to connect. Once installed, the add-on provides a user-friendly interface to configure your data import, select metrics and dimensions, and set a refresh schedule (e.g., update every morning at 6 AM).
2. Automate Everything with Google Apps Script
For ultimate flexibility, you can use Google Apps Script. This is a cloud-based JavaScript platform that lets you write your own custom functions and automations for Google Workspace products.
With Apps Script, you can write code to:
Connect to virtually any third-party API that Google Sheets doesn't have an add-on for.
Perform complex data cleaning and transformation steps on the data as it's being imported.
Set up custom "triggers" to run your import script on a specific schedule (e.g., every 15 minutes) or based on an event (e.g., when a user clicks a button).
Accessing it is simple: go to Extensions > Apps Script. While this requires coding knowledge, it's the most powerful way to create a completely custom data import pipeline tailored to your exact needs.
Final Thoughts
Google Sheets offers an incredible range of options for bringing data under one roof. Whether you’re a beginner working with a simple file upload, a data analyst scraping tables from the web with formulas, or a developer scripting API connections, there’s a method that fits your needs. Mastering these techniques transforms Sheets from a simple grid into a dynamic dashboard for all of your information.
Pulling data into a single spreadsheet is a great first step, but it often leads to hours of manual reporting work as you try to merge data from Google Analytics, your ad platforms, your CRM, and your e-commerce store. We built Graphed to eliminate this process entirely. Instead of piping all your data into sheets, you can connect your marketing and sales sources to Graphed in a few clicks. Then, just use plain English to build real-time, interactive dashboards instantly - no formulas or pivot tables required.