If your business handles a large amount of data, then, importing it into Google Sheets can be an efficient way of managing information. Google Sheets gives you the tools you need to build reports, conduct data analysis, and share your data seamlessly with team members and stakeholders.
So, what kind of data can you import into Google Sheets? How do its import features work? What functions can be used to further process this data? In this article, we provide answers to these and many other questions.
What methods can you use to import data into Google Sheets?
- Coupler.io – a reporting automation platform
This is one of the simplest and most efficient ways to transfer data into Google Sheets. Coupler.io allows you to automatically export data from over 60 apps and import it to Google Sheets on a custom schedule. As the tool is easy to use, this can be done in just a few minutes, and you don’t need any previous knowledge to use this method.
- Import data with add-ons
Google Sheets add-ons are third-party solutions that allow you to set up data integrations with various applications directly from the spreadsheet. Using this option, you can import data to Google Sheets automatically and manage imports without leaving your workbook. This is convenient, for example, when you need to integrate data from multiple sources. In this article, we will explain how to do this with the help of the Coupler.io add-on.
- Manual data import
For some cases, you can choose to manually import your data into Google Sheets. This allows you to either select a file from your Google Drive, a file that was shared with you or to upload a file from your computer. Importing your data manually is simple, but this process can become time-consuming if you need frequent updates or manage hundreds of files.
- Build integrations with Apps Script
Google Apps Script is a platform that allows you to build applications that can be used within your Google workspace. With Apps Script, you can create your own functions and macros to import your data with ease. This method gives you a lot of freedom and flexibility, but it requires technical expertise. You will need to work with APIs and write scripts to export data from your data source and then load it to Google Sheets.
- Load data with functions
Google Sheets provides built-in formulas that can import data from a variety of source files including CSV, XML, and HTML. Some functions can also import live data into Google Sheets, but the limitation is that it must be publicly available data, such as a public website. For data that is not public, it’s better to use other methods.
- Import a BigQuery table with Connected Sheets
This method only allows you to import data from BigQuery. However, as it’s a popular data source in the Google ecosystem, this option deserves to be mentioned. Using Connected Sheets, you can easily open a BigQuery table in Google Sheets and work with your data there.
Now that we’ve looked at the various methods you can use to import data into Google Sheets, let’s explore them in more detail.
1. How to import data to Google Sheets from cloud sources and apps with Coupler.io
Coupler.io allows you to connect multiple sources to Google Sheets and automate data import. These include financial apps, CRMs, sales and marketing apps, and data warehouses. Additionally, Coupler.io offers a JSON importer, which you can use to retrieve data via JSON APIs without any coding.
To start importing your data, select your data source in the form below and click Proceed.
You will be prompted to sign up for Coupler.io – it’s free, and no credit card is required.
Step 1. Connect your data source
Follow the onscreen instructions to connect your data source account. The exact steps depend on the application you selected. For example, here is what it may look like for Google Analytics 4.
Step 2. Preview and transform data
At the next stage, you can preview your data and transform it (optional) before loading to Google Sheets:
- Rearrange and rename columns
- Hide unnecessary columns
- Add calculated columns
- Sort and filter data
- Merge data from several apps or accounts
Step 3. Import data to Google Sheets and schedule refresh
Next, you need to connect your Google account and specify the spreadsheet and the sheet where to load your data. You can also create a new sheet – just type in a name for it, and Coupler.io will add it for you.
In the final step, specify your preferences for automatic updates. Toggle on Automatic data refresh and select the days and the times when you want your data to be refreshed. Your Google Sheets connector can update your dataset at an interval of up to 15 minutes. This provides you with access to always fresh and analysis-ready data.
Save and run the importer to transfer your dataset. As you can see, this is a convenient way to import data into Google Sheets in just a few minutes.
Additionally, Coupler.io allows you to transfer data from Google Sheets to apps such as Looker Studio, Tableau, PowerBI, BigQuery, PostgreSQL, Microsoft Excel, Qlik, and Monday.com.
2. How to import data into Google Sheets using add-ons
If you prefer to manage data connections directly from the spreadsheet, you can use an add-on. Let’s see how it works with a Coupler.io add-on, which has the same functionality as the web version of the tool, which we explored above.
Open Google Sheets, go to Extensions -> Add-ons -> Get add-ons.
At Google Workspace Marketplace, search for Coupler.io and install the add-on.
The Coupler.io add-on will be added to your spreadsheet. Then, sign in to Coupler.io or create an account for free if you don’t have one yet. After that, you will be able to add your first importer.
To do so, you will need to complete the same steps we described in the section above: connect your data source, select what data types to extract, then preview data and specify where to load it. In the last step, set up your schedule for the updates. Finally, run the importer.
If you want to import several different data types or data from multiple data sources, you can add a separate importer for each of them.
3. How to load data to Google Sheets manually
There are different file types that you can import manually into Google Sheets. Here are the main of them:
- CSV — Comma-separated values files
- TSV — Tab-separated values files
- XS — External subroutine files
- XLSX — Microsoft Excel spreadsheet files
- XLSM — XLM-based and macro-enabled files
- TXT — Text files
- TSB — 3D image files
- HTM/HTML — HyperText Markup Language files
- ODS — OpenDocument Spreadsheet
CSV is one of the most popular formats to use with spreadsheets, so we’ll take it as an example. Below, we explain how to import a CSV file into Google Sheets.
- Open Google Sheets and select File -> Import in the upper menu bar.
- Select or drag your desired file into Google Sheets.
- Choose your preferred separator from the drop-down menu.
Google Sheets gives you the option to create separators from tabs, commas, or with a customized symbol. It is also able to automatically detect a separator if you are not certain which one is the best option.
When importing these files, setting separators can tell Google Sheets how to divide your data into vertical columns. This important step can help organize your data and provide the clarity you need to filter, aggregate, and analyze your figures.
In our example, we have chosen to set a comma as the separator.
Next, click on the Import data button to import your file.
Google Sheets will ask you for the import location. You can choose to append, replace, import data only, or create a new spreadsheet. In the example below, we have chosen to create a new spreadsheet for our data.
And there you go!
You may have noticed that you can also choose to convert text to numbers, dates, and formulas. This can be incredibly useful if your data is not formatted and needs to be changed into numerical values for easier data processing. When your numerical data is correctly formatted, you can extract its values, concatenate them, or perform additional mathematical functions.
4. How to import data with Google Sheets functions
You can also use Google Sheets functions to pull your data from several sources. Some of the most important functions and their parameters are explored in the sections below.
IMPORTRANGE?
=IMPORTRANGE(url, cellAddress)
IMPORTRANGE is a function in Google Sheets to link to another sheet. However, it only imports (or, more precisely, references) data internally between sheets or spreadsheets. It takes a spreadsheet url and a cell range as parameters. You can check out our blog post 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 range of cells we want to target.
- First, we need to copy the address of the spreadsheet we want to pull the data from. In this case, remember you only need to copy everything before the ‘edit’ part. In our example, we copy the address of our business employment data sheet.
- Second, go to a free cell on the spreadsheet you want to import the data to and type
=importrange("https://address-of-your-file", "cell range")
Your cell range refers to the range of cells you want to import from the external spreadsheet. In our example, we want to import everything in cells H and I. This means we do not specify any row numbers as an additional parameter.
- Enclose both parameters in quotation marks and close the bracket. Press Enter. If this is the first time you are importing data into this sheet, you need to click on Allow access to connect your spreadsheets.
Your data will now be successfully imported into your new location.
IMPORTHTML
=IMPORTHTML(url, query, index)
The IMPORTHTML Google Sheets function allows you to import tabular data from websites into Google Sheets. It takes a URL, a query, and an index as parameters. Here is an example of what this function may look like in action:
=importhtml("https://www.marketwatch.com/market-data", "table","0")
- The URL must include the http protocol and must be enclosed in quotation marks.
- The query that you enter as the first parameter can be
"list"or"table". In this case, you would need to know if the data you are pulling from the website is a tabular or collection data type. The query must also be enclosed in quotations. - Lastly, the index tells Google Sheets where to look for the table or list on the website.
What if you have several tables on your website and you are not sure which are the correct list or table indices? Well, HTML will index these numerically starting at index 0, so you can check them by counting back this way. If, by doing so, you have found the index of the data structure, then perfect! If not, do not despair. Simply:
- Open the Developer Tools console from your browser.
- To discover the table or list index you want, create a similar command in console:
var index = 1;
[].forEach.call(document.querySelectorAll("ul,ol"),function(elements) { console.log("Index: " + index++,elements);});
We will revisit this function when we look at how to import live data into Google Sheets, later on. Or you can check out our blog post about IMPORTHTML in Google Sheets.
IMPORTDATA
=IMPORTDATA(url)
The IMPORTDATA function in Google Sheets allows you to fetch data from CSV or TSV files, published online. It takes a URL, including the http protocol, as a query parameter to return data into a spreadsheet. Optionally, you can specify a delimiter for parsing your data, like a comma or tab, and a language code or locale. The last two parameters are not required and Google Sheets will use a default value. Here is what it might look like:
- Go to the location of your file and copy the address.
- In Google Sheets, type the function with the URL address in the A1 cell like so:
- Save your newly imported data with a name if you prefer.
In our blog post, you can learn more about IMPORTDATA Google Sheets.
IMPORTXML
=IMPORTXML(url, xpathQuery)
The IMPORTXML Google Sheets function allows you to import XML data to your spreadsheet. It takes a URL, including the http protocol, as a query parameter and the xpath query to return data in .xml format. The xpath query takes as its parameters XML data such as headers, titles, hrefs, links, and metadata. The values of this parameter must be placed in enclosed quotation marks.
The language and region locale code are optional parameters. If unspecified, Google Sheets will use the default value for your location and timezone. You can learn more about IMPORTXML in Google Sheets in our blog post.
IMPORTFEED
=IMPORTFEED(url, sheetName, query, startRow, numItems)
This function imports an RSS or ATOM feed. This means that you can import data from websites that typically use really simple syndication. It takes:
- A URL address of the website in enclosed quotation marks.
- The name of the spreadsheet where you want to import the data.
- The name of the data structure you want to import. For example, list.
- The start row or cell address where you want to import the data.
- The number of rows in the webpage you want to import.
5. How to import data to Google Sheets using Apps Script?
Apps Script is a solution that allows you to extend the functionality of Google Sheets. It can automate various processes and eliminate a lot of painstaking manual work with the help of scripts. To get started with Apps Script, go to Extensions => Apps Script.
Next, you will be able to work in the Apps Script interface.
In the script editor, begin creating your custom script. There are also some libraries that can provide you with a ready import solution you may be looking for.
In the example above, we have created a script that imports data from a CSV file stored online and sends it to Google Sheets.
- When you are finished creating your script, click on Save or press CTRL+S on your keyboard. To run your script, click on Run.
If it is the first time you are running your script, you will have to authorize your application to access Google Sheets.
- Click on Review permissions in the pop-up window, select the account you want to authorize, then click on Advanced.
- Next, click on `The name of your script`(unsafe). Please only do this if you have created the application or trust it. If you have not named your script, you will see Go to Untitled project. In the pop-up window, click on Allow.
When you go back to your spreadsheet, you should see the imported data.
We recommend you check out our Google Apps Script tutorial to discover this awesome tool for custom automations.
6. How to import a BigQuery table with Connected Sheets
Connected Sheets is a BigQuery data connector that allows you to access your BigQuery Data from Google Sheets, run queries, and save the results in your workbook. It can also be used to import BigQuery data to Google Sheets.
To utilize this option, go to BigQuery and open a table you want to import in a separate tab. Then, click Export -> Explore with Sheets.
A new Google Sheets workbook will open, and you’ll see the message that your data is connected. Now you can work with the dataset, run queries, build charts, and so on. However, your data isn’t imported yet. To transfer it to Google Sheets, click Extract in the menu bar on the top.
Then, you can specify where you want to import this data. It can be an existing sheet or a new one. You will also be able to use the Extract editor, which will appear on the right side of the spreadsheet. It allows you to add filters, sort data, set a specific row limit, and specify the columns you want to import. For more information on using this method, please refer to the Connected Sheets documentation.
Or you can check out our blog post about connecting BigQuery to Google Sheets.
How to import live data into Google Sheets?
Real-time data can provide you with timely insights and let you adjust your business strategy on the go. With live data, you can conduct ongoing analysis and monitor important processes as they unfold.
You can use the IMPORTHTML function to import live data into Google Sheets, pulling it from a publicly available website. If you want to import data from a source that is not publicly available, consider using Coupler.io. At the beginning of this article, we already explained how to use this solution to import data into Google Sheets in near-real time.
Let’s see how to transfer data with the IMPORTHTML function.
- Go to the Google Sheets spreadsheet you want to target. Type in the IMPORTHTML function in the function bar. The function takes three parameters. The URL of the website, the query which represents a table or list structure, and the index of the table. Press Enter to run the function.
In the example below, we wanted to import live data into Google Sheets from a stock market website to build a Live Stock Market Values spreadsheet. The function ran quickly and was a success.
What can you do with the imported data?
There are many possibilities, but it eventually depends on how you want to process it and where you would like to store it.
Add charts and images
Add charts, diagrams, and images to further enhance your data story.
To add an image to the body of your Google Sheets:
- Click on your preferred cell.
- Go to the Google Sheets menu and select Insert.
- Click on Image and then, Insert image in cell.
Now, that your image is there, edit it the way you see fit for your spreadsheet.
You can also rely on Coupler.io to build reports on your behalf and ingest them into Google Sheets to present side-by-side the numerical data and its visual representation, to your stakeholders.
Clean your data
Use functions to get rid of irrelevant or duplicated data so that your data is more accurate and has better quality.
To clean your data you can use the Google Sheets CLEAN function in 3 ways:
=CLEAN(text)This removes non-printable characters from your text.=CLEAN(text, removeHTML)This removes any unwanted HTML tags.=CLEAN(text, removePunctuation)This removes all punctuation from your text.
Conditional formatting
Use If statements and conditions to test equations, auto-fill cells, and speed up data-processing time. You can create rules that govern the way cells are color-coded or calculated, allowing you to identify recurring patterns in your data.
Data analytics
You can proceed further with data analytics by creating reports, charts, and dashboards that help you interpret data and discover actionable insights. Explore Google Sheets marketing dashboard templates like this
Data warehousing
You can ingest your data into larger data warehouses and analytical platforms such as BigQuery or Microsoft BI. This means that you can use AI and business intelligence queries to dig deeper into your data and uncover the insights you may not get in Google Sheets. For example, this can be helpful for understanding consumer behavior, client satisfaction trends, financial forecasting, and more.
What are the benefits when you import data into Google Sheets?
Google Sheets allows you to create, modify, and ingest large amounts of data into spreadsheets using import functions such as IMPORTDATA or with third-party solutions like Coupler.io. Once there, you can organize, manage, and perform mathematical calculations, turning your raw numbers into valuable insights you can act upon.
When you import data into Google Sheets, you get important advantages, such as:
- Real-time collaboration and editing — Multiple team members can simultaneously edit a spreadsheet and discuss these edits in real time. All changes can be tracked and unwanted changes can be easily reverted.
- Integrations — You can integrate and ingest data from multiple apps such as Asana, Dropbox, Facebook Ads, and others. This allows you to not only collect data from your team’s work applications but to share data more seamlessly.
- Work with live data — You can use functions like IMPORTHTML and other software solutions to import live data. This means that your team can benefit from dynamic data that is constantly changing such as stock exchange numbers or scores for a sports tournament that your marketing team may require.
Making the most of your Google Sheets integration
Choosing the right method to import your data can sometimes be a challenge. If you feel confident with Apps Script or even the straightforward manual import process, then great! But perhaps you need to work with tabular data? In this case, IMPORTHTML Google Sheets function is your best bet. However, programming a new script each time, or having to deal with hundreds of file imports manually can become tiresome and lead to errors.
If you need to keep your data in Google Sheets fresh or you want to import data from multiple sources, then, you should consider Coupler.io. This is a time-efficient and convenient solution that can channel data to your spreadsheets according to your schedule. This, in turn, allows you to create live reports and dashboards displaying the latest data in near real-time. Thanks for reading and good luck with your data!
Automate data import to Google Sheets with Coupler.io
Get started for free