How to Import Data into Google Sheets - The Ultimate Guide | Coupler.io Blog (2024)

If your business handles a large amount of data then importing it into Google Sheets can be an efficient way of managing it. Why? Well, because Google Sheets gives you the tools you need to build reports, conduct data analysis, and share your data seamlessly with team members and other applications.

So, what kind of data can Google Sheets import? What kind of functions can you use to further process this data? How do its import features work? Let’s take a look at how we can answer these and many other questions in this article.

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 information necessary for business intelligence and analysis.

If you and your team need an application for your spreadsheet processing you can be sure to benefit from a number of advantages when you import data into Google Sheets, such as:

• Collaborative real-time 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 by members.

• 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.

What methods are available for importing data to Google Sheets?

Google Sheets provides you with several options for importing data the way that works best for your business. If your team is tech savvy, they can incorporate scripts into their data import process or even functions. If not, they can import data into Google Sheets using third-party tools. Add-ons can also provide a quick solution if you don’t have the time to program your imports yourself.

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 either select a file from your Google Drive, a file that was shared with you or to upload a file from your computer. Manually importing your data can be straightforward but can become error-prone and time-consuming if you have to manage hundreds of files.

Import data into Google Sheets with Apps Script

Google Apps Script is a platform that allows you to build applications that you can use within your Google workspace. With Apps Script you can create your own functions and macros to import your data with ease.

Import data into Google Sheets using functions

Google Sheets provides built-in formulas that can import data from a variety of source files including csv, xml, and html. A few can also import live data into Google Sheets. But which formula is the best one for the job? This all depends on the type of information you are trying to pull into your spreadsheet. We will take a look at Google Sheets functions later on in the manual import section of this article.

Import data into Google Sheets using add-ons

Google Sheets add-ons is a feature that allows you to integrate with user-built, customizable programs that help to enhance the functionality and power of Google Sheets. With add-ons you can gain a number of benefits in terms of data import including:

• Automating your data imports.
• Setting up your imports to run on a preferred schedule.
• Replacing old data or appending new data to your spreadsheets.

Import data into Google Sheets using third-party ETL tools

ETL tools can include business, accounting, sales, and customer service applications that can integrate with Google Sheets to enhance its features and allow you to find solutions to your data processing needs. With these tools, you can gain a number of benefits in terms of data import, such as:

• Integrating with several applications that can send data to your Google Sheets.
• Advanced tools that can help to keep your data imports up-to-date.
• Removal of data duplicity and errors.

To get started with a third-party solution you can look up the one you think works best for your business and connect your account.

Now that we’ve looked at the various methods that you can use to import data into Google Sheets, let’s look at some examples of where you can put some of them to good use.

We will start by looking at the manual import process.

Which data formats can be imported into Google Sheets manually?

There are a number of files that you can import manually into Google Sheets. The following main file formats are supported:

• 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 files are very common in spreadsheets today. In the following section, we will look at how we can import a CSV file into Google Sheets.

How to import CSV to Google Sheets manually?

First, head over to the main menu of Google Sheets to import your file. In this example, we have chosen to import our business employment data CSV file.

• Click on Import.

• 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.

Importing 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 listed below:

Import data to Google Sheets with IMPORTHTML

=IMPORTHTML(url, query, index)

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.

Import data to Google Sheets with IMPORTDATA

=IMPORTDATA(url)

IMPORTDATA function in Google Sheets allows you to fetch data from published online CSV or TSV files. 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.

Import data to Google Sheets with IMPORTXML

=IMPORTXML(url, xpathQuery)

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.

Import data to Google Sheets with 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.

How can you automatically import data into Google Sheets?

Third-party solutions can be a great alternative when it comes to automating data import to Google Sheets from different apps and platforms. Let’s take a look at how you can use the Coupler.io ETL tool for scheduling data imports to Google Sheets.

Coupler.io is a data automation and analytics platform. It provides an ETL tool to automate dataflows 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 experts service that can cope with different tasks from setting up custom integrations to creating interactive analytics dashboards.

In the following example, we will import data on our top Google searches from BigQuery to Google Sheets.

• Sign up to Coupler.io, click 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 destination Google account, then select a spreadsheet and a sheet where you want to send your data.

See Also

How to Use IMPORTXML in Google Sheets (Examples) | Layer Blog

• Optionally, you can choose your preferred cell range and change the import mode.
• The final step is actually the automation. You can turn on the Automatic data refresh and set up a schedule to import your data. Setting up a schedule helps you import data at a date and time most convenient for you and keep it up-to-date in the process.

• Click on Save and Run and watch as Coupler imports your BigQuery data to Google Sheets.

How to import data into Google Sheets using add-ons

Basically, Google Sheets data import with add-ons is similar to using third-party tools. The difference is that you need to install an add-on from Google Workspace Marketplace. In addition, you can manage the data import settings from the very spreadsheet.

• Click on Extensions in your Google Sheets menu.
• Navigate to Add-ons and then Get add-ons.

• In the Google Workspace Marketplace window, search or select the add-on that works best for your business.

The Coupler.io ETL tool is also available as a Google Sheets add-on, so you can install it and use it for importing data from your data source.

Note: To manage or see your add-ons, go back to the Extensions tab and navigate to Add-ons, then Manage add-ons.

How to import data with IMPORTRANGE?

=IMPORTRANGE(url, cellAddress)

IMPORTRANGE is a function in Google Sheets to link to another sheet. However, it only imports (actually 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.

How to import data using Apps Script?

Apps Script is a solution to extend the functionality of Google Sheets, automate processes, andreduce a lot of painstaking manual work for redundant tasks with the help of scripts. To get started with Apps Script, go to Extensions => Apps Script.

You will be navigated to the Apps Script interface, where you can begin creating your customizable script.

In the script editor, begin creating your script. There are also some libraries you can explore which can give you the import solution that you may be looking for if you don’t have a lot of time to invest in creating your own program.

In the example above, we have created a script that would import data from a CSV file stored online and send 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 your desired results:

We do recommend you check out our Google Apps Script tutorial 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 the essential information you need to capture in minutes the insights needed to fill the strategic gaps in your business. With live data, you can begin making analyses as soon as the data is imported into your database.

To import live data into Google Sheets, have on hand a publicly available website that you can begin pulling your data from. To begin extracting this data, we can use the IMPORTHTML function.

• First things first, 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 this example below, we wanted to import live data into Google Sheetsfrom our stock market website to build our Live Stock Market Values spreadsheet. The function ran quickly and was a success.

What can you do with your newly imported data?

There are many possibilities for your newly imported data but it all depends on how you want to process it and where you would like to store it.

Adding 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 of better quality when it is further processed down the line.

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 quicken your 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 benefit from data analytics by creating reports, charts, and dashboards to interpret data and to aid in discovering your data insights. If you are unsure of how to get started with this, then you can let our experts assist you in covering any advanced data analytics you may need.

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 artificial and business intelligence queries to dig deeper into your data and get the insights you may not get in Google Sheets on consumer behavior, financial forecasting, client satisfaction, and more.

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 feel like data automation is more your style then perhaps Coupler.io can help reduce your data import woes. Of course, the final decision is always up to you.

Thanks for reading and good luck with your data imports!

Home