If your business deals with a large amount of data, importing it into Google Sheets can be an efficient way to manage it. Why? Well, because Google Sheets provides you with the tools you need to create reports, perform data analysis, and seamlessly share your data with team members and other applications.
So, what kind of data can you import into Google Sheets? What types of functions can you use to further process that data? How do the import features work? Let’s explore how we can answer these questions and more in this article.
What are the benefits of importing data into Google Sheets?
Google Sheets allows you to create, edit, and ingest large amounts of data into spreadsheets using import functions such as IMPORTDATA or with third-party solutions like Coupler.io. Once the data is imported, you can organize, manage, and perform mathematical calculations, transforming raw numbers into valuable insights needed for business intelligence and analysis.
If you and your team need an application for spreadsheet processing, you can rest assured that there are several advantages to importing data into Google Sheets, such as:
-
Real-time collaborative editing – Multiple team members can simultaneously edit a spreadsheet and discuss those edits in real-time. All changes can be tracked, and unwanted modifications can be easily undone by team members.
-
Integrations – You can integrate and import data from multiple applications such as Asana, Dropbox, Facebook Ads, and more. This allows you not only to gather data from your team’s working applications but also to share data more seamlessly.
-
Working with real-time data – You can use functions like IMPORTHTML and other software solutions to import real-time data. This means your team can benefit from dynamic data that constantly changes, such as stock market figures or scores from a sports tournament that your marketing team may need.
What methods are available for importing data into Google Sheets?
Google Sheets offers several options for importing data in a way that best suits your business. If your team is tech-savvy, they can incorporate scripts into their data import process, or even create their own functions. Alternatively, they can import data into Google Sheets using third-party tools. Add-ons can also provide a quick solution if you don’t have time to program your own imports.
Let’s take a look at some of the methods below.
Manual data import into Google Sheets
You can choose to manually import your data into Google Sheets. This allows you to select a file from your Google Drive, a file that has been shared with you, or upload a file from your computer. Manually importing your data can be straightforward, but it can become error-prone and time-consuming if you have to manage hundreds of files.
Importing data into Google Sheets with Apps Script
Google Apps Script is a platform that allows you to create applications that you can use within your Google Workspace. With Apps Script, you can create your own functions and macros to easily import your data.
Importing data into Google Sheets using functions
Google Sheets offers built-in formulas that can import data from various source files, including CSV, XML, and HTML files. Some of them can also import real-time data into Google Sheets. But which formula is best for the job? It all depends on the type of information you’re trying to import into your spreadsheet. We’ll explore Google Sheets functions later in the “Manual Import” section of this article.
Importing data into Google Sheets using add-ons
Google Sheets add-ons are a feature that allows you to integrate with customizable programs created by users and helps enhance the functionality and power of Google Sheets. With add-ons, you can benefit from several advantages in terms of data import, such as:
- Automating your data imports.
- Setting up your imports to run on a preferred schedule.
- Replacing old data or adding new data to your spreadsheets.
Importing data into Google Sheets using third-party ETL tools
ETL tools can include enterprise management, accounting, sales, and customer service applications that can integrate with Google Sheets to enhance its functionality and help you find solutions for your data processing needs. With these tools, you can benefit from several advantages in terms of data import, such as:
- Integration with multiple applications that can send data to your Google Sheets.
- Advanced tools that can help you keep your data imports up to date.
- Removing duplicates and data errors.
To get started with a third-party solution, you can search for one that suits your business best and connect your account.
Now that we have explored the different methods you can use to import data into Google Sheets, let’s look at some examples of using these methods.
In what data formats can you manually import into Google Sheets?
There are several file formats that you can manually import into Google Sheets. Here are the main supported file formats:
- CSV – Comma-separated values files.
- TSV – Tab-separated values files.
- XLS – External subroutines files.
- XLSX – Microsoft Excel spreadsheets files.
- XLSM – XLM-based files enabled with macros.
- TXT – Text files.
- TSB – 3D image files.
- HTM/HTML – HTML files.
- ODS – OpenDocument spreadsheets.
CSV files are very common in spreadsheets today. In the next section, we will see how to import a CSV file into Google Sheets.
How to manually import a CSV file into Google Sheets?
First, go to the main menu of Google Sheets to import your file. In this example, we have chosen to import our employment data CSV file.
- Click on Import.
- Select the desired file from your Google Drive or upload it from your computer.
- Choose the desired separator from the dropdown menu.
Google Sheets allows you to create separators from tabs, commas, or with a custom symbol. It can also automatically detect a separator if you’re unsure of the best choice.
When importing these files, setting the separators lets Google Sheets know how to divide your data into vertical columns. This important step helps organize your data and provides the necessary clarity for filtering, grouping, and analyzing it.
In our example, we chose to set a comma as the separator.
Next, click the Import data button to import your file.
Google Sheets will prompt you for where you want to import the data. You can choose to add, replace, import only the data, or create a new spreadsheet. In the example below, we chose to create a new spreadsheet for our data.
And voila!
You may have also noticed that you can choose to convert text to numbers, dates, and formulas. This can be extremely useful if your data is not formatted and needs to be transformed into numeric values for easier data processing. When your numeric data is properly formatted, you can extract values, concatenate them, or perform additional mathematical functions.
Importing Data with Google Sheets Functions
You can also use Google Sheets functions to retrieve data from various sources. Here are some of the most important functions and their parameters:
Importing data to Google Sheets with IMPORTHTML
=IMPORTHTML(url, query, index)
The IMPORTHTML function in Google Sheets allows you to import tabular data from websites into Google Sheets. It takes into account a URL, a query, and an index as parameters. Here’s an example of what this function could look like in action:
=importhtml("https://www.marketwatch.com/market-data", "table","0")
- The URL must include the http protocol and should be enclosed in quotation marks.
- The query you enter as the first parameter can be “list” or “table”. In this case, you need to know if the data you are importing from the website is in a table or list format. The query should also be enclosed in quotation marks.
- Finally, the index tells Google Sheets where to look for the table or list on the website.
And what if you have multiple tables on your website and are unsure about the correct indices of the lists or tables? Well, HTML indexes these tables numerically starting from index 0, so you can check them by counting that way. If by doing that, you found the index of the data structure, then great! If not, don’t despair. Here’s what you can do:
- Open the Developer Tools Console in your browser.
- To find out the index of the table or list you want, create a similar command in the console:
var index = 1; [].forEach.call(document.querySelectorAll("ul,ol"),function(elements) { console.log("Index: " + index++,elements);});We’ll come back to this function when we cover importing live data into Google Sheets later. Or you can check out our blog article on IMPORTHTML in Google Sheets.
Importing Data into Google Sheets with IMPORTDATA
=IMPORTDATA(url)
The IMPORTDATA function in Google Sheets allows you to retrieve data from published CSV or TSV files online. It takes a URL, including the http protocol, as the query parameter to return the data into a spreadsheet. Optionally, you can specify a delimiter for parsing your data, such as a comma or a tab, and a language or locale code. The last two parameters are not mandatory, and Google Sheets will use default values. Here’s how it could look:
- Go to the location of your file and copy the address.
- In Google Sheets, type the function with the URL address in cell A1, like this:
=IMPORTDATA("your-file-address")
- Save your newly imported data with a name if you prefer.
In our blog article, you can learn more about IMPORTDATA in Google Sheets.
Importing Data into Google Sheets with IMPORTXML
=IMPORTXML(url, xpathQuery)
The IMPORTXML function in Google Sheets allows you to import XML data into your spreadsheet. It takes a URL, including the http protocol, as the query parameter and an xpath query to return the data in .xml format. The xpath query accepts XML data such as headers, titles, hrefs, links, and metadata as parameters. The values for this parameter should be enclosed in quotation marks.
The language and region code is optional. If not specified, Google Sheets will use the default value for your region and time zone. You can learn more about IMPORTXML in Google Sheets in our blog article.
Importing Data into Google Sheets with IMPORTFEED
=IMPORTFEED(url, sheetName, query, startRow, numItems)
This function imports an RSS or ATOM feed. This means you can import data from websites that commonly use syndication. It takes into account:
- A URL of the website in quotation marks.
- The name of the spreadsheet in which you want to import the data.
- The name of the data structure you want to import, such as a list.
- The starting row or the cell address where you want to import the data.
- The number of rows in the web page you want to import.
How to automatically import data into Google Sheets?
Third-party solutions can be a great alternative for automating the import of data into Google Sheets from different applications and platforms. Let’s see how you can use the Coupler.io ETL tool to schedule data import into Google Sheets.
Coupler.io is a data automation and analytics platform. It offers an ETL tool to automate data flows between multiple data sources and three destinations: Google Sheets, Microsoft Excel, and Google BigQuery. For more advanced analytics and reporting, Coupler.io offers a data expert service that can handle different tasks, from setting up custom integrations to creating interactive analytics dashboards.
In the following example, we will import data about our most frequent Google searches from BigQuery to Google Sheets.
-
Sign up for Coupler.io, click on the Add New Importer button, and select your source application, BigQuery, and the destination application, Google Sheets.
-
Connect your source application account, then enter the SQL query for your data.
-
Connect your Google destination account, then select a spreadsheet and sheet where you want to send your data.
-
Optionally, you can choose the desired cell range and modify the import mode.
-
The final step is actually automation. You can enable Automatic Data Refresh and schedule the import of your data. Scheduling allows you to import data at a date and time that suits you best and keep it up to date in the process.
-
Click on Save and Run, and watch as Coupler imports your data from BigQuery into Google Sheets.
How to import data into Google Sheets using add-ons?
Fundamentally, importing data into Google Sheets with add-ons is similar to using third-party tools. The difference is that you need to install an add-on from the Google Workspace Marketplace. Additionally, you can manage data import settings directly in the spreadsheet.
- Click on Extensions in the Google Sheets menu.
- Go to Add-ons and then Get add-ons.
- In the Google Workspace Marketplace window, search for or select the add-on that best suits your business.
Coupler.io ETL tool is also available as a Google Sheets add-on, so you can install it and use it to import data from your data source.
Note: To manage or view your add-ons, go back to the Extensions tab and go to Add-ons, then Manage add-ons.
How to import data with IMPORTRANGE in Google Sheets?
=IMPORTRANGE(url, cellAddress)
IMPORTRANGE is a function in Google Sheets that allows you to link to another sheet. However, it (actually, it just references) imports the data internally between sheets or spreadsheets. It takes a spreadsheet URL and a cell range as query parameters. You can check our blog article to learn more about IMPORTRANGE in Google Sheets.
In the example below, we want to collect our data from one spreadsheet and send it to another. To do this, we will use the IMPORTRANGE function. With it, we can easily specify the address and cell range we want to target.
-
First, we need to copy the address of the spreadsheet from which we want to extract the data. In this case, remember that you only need to copy everything before the ‘edit’ part. In our example, we copy the address of our employment data spreadsheet.
-
Then, go to a free cell in the spreadsheet you want to import the data into and type:
=importrange("https://your-file-address", "cell range")
Your cell range refers to the cell range you want to import from the external spreadsheet. In our example, we want to import everything into cells H and I. This means we don’t specify any additional row numbers as an additional parameter.
- Save your newly imported data with a name if you prefer.
Your data will now be successfully imported into your new location.
How to import data using Google Apps Script?
Apps Script is a solution for extending the functionality of Google Sheets, automating processes, and reducing a lot of tedious manual work for repetitive tasks using scripts. To get started with Apps Script, go to Extensions => Apps Script.
You will be taken to the Apps Script editor interface, where you can start creating your customizable script.
Note: In the script editor, start creating your script. There are also libraries you can explore, which may provide you with the import solution you’re looking for if you don’t have much time to spend creating your own program.
In the example above, we created a script that imports data from a CSV file stored online and sends it into Google Sheets.
-
When you’re done creating your script, click on Save or press CTRL+S on your keyboard. To run your script, click on Run.
-
If it’s the first time running your script, you will need to authorize your application to access Google Sheets.
- Click on Review Permissions in the pop-up window, select the account you want to authorize, and click on Advanced.
- Then, click on Your script name (unsafe). Only do this if you have created the application yourself or trust it. If you haven’t named your script, you will see Go to the untitled project. In the pop-up window, click on Allow.
When you return to your spreadsheet, you should see the desired results.
We recommend checking out our tutorial on Google Apps Script to discover this awesome tool for custom automations.
How to import live data into Google Sheets?
Live data, or real-time data, can provide you with essential insights to quickly capture the necessary knowledge to fill strategic gaps in your business. With live data, you can start performing analysis as soon as the data is imported into your database.
To import live data into Google Sheets, you’ll need a publicly accessible website from which you can start extracting your data. To start extracting this data, we can use the IMPORTHTML function.
- First, go to the Google Sheets spreadsheet you want to target. Type the IMPORTHTML function in the function bar. The function takes three parameters: the website URL, the query that represents a table or list structure, and the index of the table. Press Enter to execute the function.
In this example below, we wanted to import live data into Google Sheets from our stock market website to build our live stock values spreadsheet. The function executed quickly and successfully.
What can you do with your newly imported data?
There are numerous possibilities for your newly imported data, but it all depends on how you want to process it and where you want to store it.
-
Adding charts and images: Enhance your data story even further by adding charts, graphs, and images.
-
Cleaning your data: Use functions to get rid of irrelevant or duplicate data, so your data is more accurate and of better quality for further processing.
-
Conditional formatting: Use IF statements and conditions to test equations, automatically fill in cells, and speed up your data processing. You can create rules that govern how cells are color-coded or calculated, enabling you to spot recurring patterns in your data.
-
Data analysis: You can benefit from data analysis by creating reports, charts, and dashboards to interpret the data and help you uncover insights hidden within it. If you’re not sure where to start, our experts can assist you with all the advanced data analytics you may need.
-
Data Warehousing: You can import your data into larger data warehouses and analytics platforms like BigQuery or Microsoft BI. This means you can use AI and enterprise-grade queries to dig deeper into your data and gain insights that you may not be able to achieve within Google Sheets, such as consumer behavior, financial forecasting, customer satisfaction, and more.
Making the most of your integration with Google Sheets
Choosing the most suitable method for importing your data can sometimes be a challenge. If you feel comfortable with Apps Script or even the simple manual import process, that’s great! But maybe you need to work with tabular data? In that case, Google Sheets’ IMPORTHTML function is your best bet. However, having to script a new script every time or having to manage hundreds of manual file imports can become tedious and error-prone. If you feel like data automation is more your style, then perhaps Coupler.io can help alleviate your data import worries. Of course, the final decision is always up to you.
Thank you for reading, and good luck with your data imports!
This article was originally published on Crawlan.com.
