Google Sheets is a great tool to use for business intelligence and data analysis. If you want to eliminate manual data imports and save time, then let me will show you how you can automatically connect and import data from external sources into Google Sheets.
The 3 best ways to import data into Google Sheets are:
- Google Sheets Functions
- Google Sheets Add-ons
- Google Apps Script
Let's dive into each of these 3 methods in more detail.
1. Google Sheets Functions
Google Sheets has 5 built-in functions that can help you import data:
- IMPORTDATA
- IMPORTFEED
- IMPORTHTML
- IMPORTRANGE
- IMPORTXML
Let's see how we can use each of these Google Sheets functions to import external data automatically.
1a.) ImportData
You can use the =IMPORTDATA function to pull data from a given URL in CSV or TSV format. Just use the function =IMPORTDATA("X") and replace X with an a URL. The URL must be enclosed in quotation marks or it can be a reference to a cell that contains the appropriate text. You can only use a max of 50 ImportData functions per Google Sheet.
IMPORTDATA Syntax
IMPORTDATA(url)IMPORTDATA Examples
1b.) ImportFeed
Use =ImportFeed to pull an RSS or ATOM feed.
IMPORTFEED Syntax
IMPORTFEED(url, [query], [headers], [num_items])- url: URL of the RSS or ATOM feed, including the protocol (e.g. http:// or https://). The url has to be in quotes or it can reference a cell that contains the appropriate text.
- headers: Whether to include column headers as an extra row on top of the returned value. The default is false.
- num_items: For queries of certain items, the number of items to return, starting with the most recent. Specifying this is optional, if num_items is not specified, then all the items that are currently published on the feed will be pulled.
- query: This specifies what data to fetch from url. This is optional, as the default is "items".
query options:
- "feed" pulls a single row that contains feed information including the title, description, and the URL.
- "feed <type>" pulls a specific attribute of the feed, where <type> can be the title, description, author, or the URL.
- "items" pulls a full table of information that contains items from the feed. If num_items is not specified, then all the items that are currently published on the feed will be pulled.
- "items <type>" pulls an attribute of the requested item. <type> is the title, summary of the content, url, or created (the post date of the item).
IMPORTFEED Examples
- IMPORTFEED("http://news.google.com/?output=atom")
- IMPORTFEED(A1,B1,C1,D1)
1c.) ImportHTML
Use the =ImportHTML function to pull table or list data from a HTML page. Just use the function =ImportHTML(X) and replace "X" with a URL, query, or index.
IMPORTHTML Syntax
IMPORTHTML(url, query, index)- url: URL of the page you want to pull information from, including the protocol (e.g. http:// or https://). The url must be enclosed in quotation marks or it can be a reference to a cell that contains the appropriate text.
- query: You can use either "list" or "table" depending on the type of structure that contains the data you want to pull.
- index: This is the index, starting with 1, that identifies the table or list as defined in the HTML source that you want to pull.
IMPORTHTML Examples
- IMPORTHTML("http://en.wikipedia.org/wiki/Demographics_of_India","table",4)
- IMPORTHTML(A2,B2,C2)
1d.) ImportRange
Use the =ImportRange function to pull a range of cells from a specific spreadsheet. This function requires that the sheet must have permission to import data from another spreadsheet. When you use this function for the first time, you will be prompted to enable permission. Once you give access, then any user on the destination sheet can use the IMPORTRANGE function to import data the source spreadsheet. You can revoke permission at any time. Also, if the data set you're trying to import is too big, then you'll get an error message.
IMPORTRANGE Syntax
IMPORTRANGE(spreadsheet_url, range_string)- spreadsheet_url: This is the URL of the spreadsheet that you want to import data from. The value for spreadsheet_url must be in quotation marks or it can be a reference to a cell containing the URL of a spreadsheet.
- range_string: This is a string, formatted as "[sheet_name!]range", which specifies the range to pull. The sheet_name of range_string is optional. The default IMPORTRANGE will pull from the given range on the first sheet. The value of range_string must either be in quotation marks or it can be a reference to a cell that contains the appropriate text.
IMPORTRANGE Examples
1f.) ImportXML
Use =ImportXML to pull data from structured data types like XML, HTML, CSV, TSV, RSS, or ATOM.
IMPORTXML Syntax
IMPORTXML(url, xpath_query)- url: This is the URL of the webpage, which must include the protocol (e.g. http:// or https://). The value of the url must either be in quotation marks or it can be a reference to the cell that contains the appropriate text.
- xpath_query: This is the XPath query that will run on the structured data. You can find more information on XPath here.
IMPORTXML Examples:
2. Google Sheets Add-ons
There's several Google Sheets Add-ons (aka Google Sheets apps) available in the Google Workspace Marketplace that you can use to connect different types of external data sources. A couple of the most popular Google Sheets add-ons for connecting a data source to Google Sheets are Apipheny and Supermetrics.
Apipheny
Apipheny.io is a Google Sheets add-on that you can use to connect any JSON or CSV API to Google Sheets. With Apipheny you can import an API to Google Sheets just by entering your API URL and key and then clicking "Run".
It's a simple no-code tool for connecting an API to Google Sheets. You can pull in API data from any JSON or CSV API data source and you can save your API request or schedule it to run on a recurring basis.
It works with GET and POST requests and it also includes advanced features like the ability to save your API requests for easy access and the ability to schedule your API request to refresh the data in your spreadsheet on a recurring basis.
There's also a custom =APIPHENY() function you can use to call an API request in your spreadsheet or you can reference the value of a cell in your API query.
Click here to install Apipheny or click here to visit the website and learn more.
Disclosure: The author is a co-founder of Apipheny.
Supermetrics
Supermetrics is a reporting and marketing automation tool that you can use to pull in paid ads, SEO, social media, and other analytics data. It has a lot of pre-built data connectors ready to help you connect different data sources so you can easily pull your data into Google Sheets.
Supermetrics vs Apipheny
There's two key differences between Apipheny and Supermetrics:
- Features: Supermetrics is an all-in-one data integration and reporting solution that helps you do everything from connecting your data source to providing you with reporting templates so you can visualize your data, plus many other features. Apipheny is a DIY API data connector, you have to set up and connect your own API data sources and you have to create your own reports with the data.
- Price: Supermetrics starts at $69/month while Apipheny starts at $12/month
3. Google Apps Script
You can use Google Apps Script to write a simple code that will connect and import data into Google Sheets, or you can copy and paste one of the publicly shared scripts from a site like Github or Stack Overflow.
For example, here's how you can use Google Apps Script to import JSON data into Google Sheets:
- Open an existing Google Sheet or create a new Sheet
- Click "Tools" > "Script Editor" in the menu to open the Google Apps Script editor
- Delete all the placeholder content that's in the script editor so you can enter your own script
- Copy and paste a script from Github or Stack Overflow such as this one into the editor
- Choose "File" > "Save"
- Title the code "ImportJSON" or something similar
- Close the Google Apps Script editor and go back to your Google Sheets
- In the cell of your choice, enter =ImportJSON("X") but replace X with your JSON API URL
- Press enter and your JSON API data will get pulled into your Google Sheet
Script vs Function vs Add-on: Which Will You Choose?
Each method has it's own pros and cons. The Google Sheets functions and Google Apps Script methods are limited in functionality but they are free to use. The Google Sheets Add-ons are more feature-rich but they cost money. Depending on how simple or complex your query is will determine the right tool for your needs.
Final Thoughts
Importing data from various data sources into Google Sheets is easier than you might think. There's an abundance of available functions and tools you can use and there's more coming out all the time. If none of the above methods are what you're looking for, you could code your own tool using Google Sheets API and Google Apps Script. Check out this tutorial to learn how. Or you could hire someone to code it for you so you can get exactly what you're looking for. Getting a custom made solution is pretty cheap these days when you hire a freelancer from a website like Fiverr or Upwork.
Related article: how to pull JSON API data into Google Sheets.