Google Sheets allows users to manually import data into a spreadsheet document.
For example, you may have a local .csv file containing a dataset you wish to explore in Google Sheets.
Using the Import tool, you’ll be able to upload this local file and add it to a spreadsheet online.
Here are the supported file formats you can import into a Google Sheets document.
With the Import feature, you’ll be able to import data from these file formats whether they have been uploaded to your Google Drive account or are stored locally in your device.
Here’s how to start importing data manually in Google Sheets:
First, create a new spreadsheet or open an existing spreadsheet where you want to import your data.
Once in the target spreadsheet, go to the File menu at the top-left corner of the screen.
In the File menu, click on the Import option.
In the Import file dialog box, select the file you wish to import.
You can upload a local file such as an Excel document (.xlsx) or a .csv file.
Alternatively, you can choose an existing file in your Google Drive account or enter a specific URL.
In this example, we’ll upload a local .csv file containing a dataset we want to use in Google Sheets.
To do this, we’ll go to the Upload tab and click the Browse button.
In the File picker dialog box, we’ll navigate to the folder containing the data source we want to import.
In Windows, click on the file you want to import and click on Open.
Click on OK to proceed.
You are given various options on where to place the imported data– either as a new sheet, replacing the current sheet, or appending data to the current sheet.
Other options include creating a new Google Sheets document altogether, replacing data at the selected cell, or replacing the current spreadsheet.
For this example, we’ll replace the current sheet with the imported data.
By default, Google Sheets will try to detect the right separator to use.
However, you can also manually set the separator by adjusting the value under Separator type.
The Import option even allows you to provide a custom separator to use with your data.
Ensure that the Convert text to numbers, dates, and formulas option is checked if you want your numerical data to be imported as numbers and not text.
Once you’ve set the right settings, click on Import data to proceed.
The data from your selected file should now be imported into a Google Sheets document.
Do note that sometimes data may not import correctly due to various issues.
Always review your data after importing to detect these anomalies.
Apps Script is a cloud-based scripting language for light-weight application development in the Google ecosystem.
It allows you to automate tasks across Google products, including Google Sheets, by writing custom scripts in JavaScript
We can create a custom script that will allow us to import data (such as a .CSV file) into our active spreadsheet.
To import data into Google Sheets using Apps Script, just follow these steps:
Open your Google Sheets document where you want to import data.
Click on Extensions in the menu bar, then select Apps Script. This will open the Google Apps Script editor in a new tab.
The Apps Script editor is where you’ll write our custom code to import data.
You can use the following sample code to set up a function called importCSVFromWeb().
Sample code:
function importCSVFromWeb() < // URL of the CSV file var csvUrl = "http://example.com/path/to/your/csv/file.csv"; // Fetch the CSV content using UrlFetchApp var response = UrlFetchApp.fetch(csvUrl); var csvContent = response.getContentText(); // Parse the CSV content var data = Utilities.parseCsv(csvContent); // Open the active spreadsheet and get the first sheet var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // Clear existing content in the sheet sheet.clear(); // Set the values in the sheet to the CSV data sheet.getRange(1, 1, data.length, data[0].length).setValues(data); >
You can replace the value in csvUrl with the actual URL for the CSV file you want to import.
In this example, we’ll try to import a CSV of mock employee data hosted on a Github repository.
Save your script with a meaningful name by clicking on the floppy disk icon or pressing Ctrl+S (Cmd+S on Mac).
This saves your script in the Apps Script project.
Ensure that the importCSVFromWeb() function is selected from the function dropdown list.
Afterward, you can run your script by clicking the Run button in the toolbar of the script editor.
If you’re running the script for the first time, you’ll need to authorize the script to access your Google Sheets data.
Switch back to your Google Sheets document and refresh the page if necessary.
You should see the imported data in your sheet.
In the table above, we can now view our imported data from our publicly-accessible CSV data.
Google Sheets comes with multiple functions you can use to import data into your spreadsheet.
Each of these functions differ from one another by the type of data they are designed to import.
Let’s take a closer look at each of these functions.
For each of these functions, we’ll explain the syntax of the function and go through a simple use case to show what type of data the function can import.
The IMPORTRANGE function allows you to import a range of cells from another Google Sheets document that you have access to.
Syntax:
The syntax for the IMPORTRANGE function in Google Sheets is as follows:
=IMPORTRANGE(spreadsheet_url, range_string)
Sample Usage:
Suppose you have a Google Sheet with employee data, and you want to import a specific range of this data (e.g., A1:E20 from “hr_data” sheet) into another Google Sheet.
We can use the IMPORTRANGE function to copy the data from a source spreadsheet and return it in a destination sheet.
This creates a dynamic copy of your data, which means any changes made in the source sheet will reflect in the destination sheet.
This function is used to import data from a table or list within an HTML webpage.
You specify the URL of the webpage and the type of data (either “table” or “list”), along with the index of the table or list on that page.
Syntax:
The IMPORTHTML function follows the following syntax:
=IMPORTHTML(url, query, index)
Sample Usage:
Imagine you want to import a table of the best-selling albums of all time from Wikipedia.
The URL of the webpage is “https://en.wikipedia.org/wiki/List_of_best-selling_albums” . On this page in particular, the table you’re interested in is the 2nd table.
You would use the IMPORTHTML function like this:
=IMPORTHTML(“https://en.wikipedia.org/wiki/List_of_best-selling_albums”,”table”,2)
After evaluating the formula, Google Sheets will now display the extracted table from the provided link.
This function imports data from a given URL in CSV (comma-separated values) or TSV (tab-separated values) format.
It’s the ideal way for you to import data from simple, structured data files hosted online.
Syntax:
The IMPORTDATA function follows the following syntax:
Sample Usage:
Suppose you have a URL that points directly to a CSV file hosted online containing population data for various regions.
This CSV file may be updated regularly and you want to have this data imported to your Google Sheet.
We can use the IMPORTDATA function to import the data from the CSV file into the current sheet.
Here, the only argument needed is the URL of the CSV file.
The function will import the contents of this CSV file into your Google Sheet, allowing you to work with up-to-date event data directly in your spreadsheet.
This function is designed to import data from any XML, HTML, or XHTML web page. You can use it to extract specific data using an XPath query.
XPath (XML Path Language) is a language used for selecting nodes from an XML document.
We can create custom XPath queries to help search for specific elements or data you want to extract from a given webpage.
Syntax:
The IMPORTXML function follows the following syntax:
Sample Usage:
Let’s say you want to extract article information from an online news publication.
You are particularly interested in fetching the headline of the top news stories, which are encapsulated in HTML tags.
To achieve this, you would use: IMPORTXML(, “//h1”)
In the example seen above, we were able to extract the headline from a news article using the XPath query “//h1”
This function is used to import an RSS or ATOM feed from a given URL.
It allows you to import a feed and specify parameters such as the number of items to import and whether to include historical items.
It’s useful for keeping track of updates from news sites, blogs, and other online sources that provide RSS or ATOM feeds.
Syntax:
The IMPORTFEED function follows the following syntax:
IMPORTFEED(url, [query], [headers], [num_items])
Sample Usage:
Let’s try retrieving the latest headlines from Google News.
We can use the URL “http://news.google.com/?output=atom” to access the publication’s ATOM feed.
You only want to import the latest 5 posts, and you want the first row to display the column headers.
You would use the IMPORTFEED function as follows: IMPORTFEED(“http://news.google.com/?output=atom”, “items”, TRUE, 5)
Google Sheets add-ons are third-party applications or extensions that integrate with Google Sheets to enhance its functionality.
Users can install these add-ons from the Google Workspace Marketplace, accessible by clicking Extensions > Add-ons > Get add-ons.
A variety of third-party add-ons have already been developed for Google Sheets to handle importing data.
For example, the ImportJSON add-on is a popular Google Sheets add-on that allows users to import JSON data from any API as a table.
To import API data into Google Sheets using the ImportJSON add-on, follow these steps:
First, we’ll need to install the ImportJSON add-on for our Google Sheets account.
You can search for “ImportJSON” in the Google Workspace Marketplace.
Click on Install in the add-on’s Marketplace page to start using the add-on with your document.
Click on Allow when Google Sheets asks if you want the add-on to have access to your Google Sheets documents.
Select an empty cell and type the IMPORTJSON formula.
We’ll use the first argument to determine the API endpoint to use.
The second argument is an optional argument we can use to determine which fields to output.
For this example, we’ll use an API endpoint that retrieves the monthly historical prices of Bitcoin.
We’ll set up our second argument to refer to the range A1:B1 which contains the two fields we want to output.
Since this range already acts as a header, we’ll add “hideHeaders” as our third argument.
After pressing Enter, the data from the API will populate into your Google Sheets document.
The IMPORTJSON add-on caches the API result every 24 hours by default.
You can customize the cache lifespan by adjusting the cacheLifespan option to the value you want the cache to last.
Importing data into Google Sheets using third-party ETL (Extract, Transform, Load) tools involves a process where data is extracted from various sources, transformed into a suitable format, and then loaded into Google Sheets.
These ETL tools are especially useful when dealing with large volumes of data or data from sources that are not natively supported by Google Sheets.
Here’s an overview of how this process typically works:
The ETL tool extracts data from the source, which could be databases, CRM systems, cloud storage, or other software applications.
This extraction process involves accessing data from these sources, often through APIs or direct database connections.
After extraction, the data might not be in the right format for direct use in Google Sheets.
The ‘Transform’ phase involves cleaning, restructuring, or enriching the data.
This could include tasks like filtering out unnecessary information, converting data types, merging data from multiple sources, or performing specific calculations.
Finally, the transformed data is loaded into Google Sheets.
The ETL tool will typically have a mechanism to connect to Google Sheets and upload the data.
This could be a direct integration or through a file format that Google Sheets can import, like CSV or Excel.
Some popular ETL tools that can be used for importing data into Google Sheets include Zapier, Stitch, and Talend.
Each tool has its own set of features and supported data sources, so the choice of tool would depend on the specific requirements of the data import task.
To import data from one Google Sheet to another automatically, you can utilize the IMPORTRANGE function in Google Sheets.
This function allows you to import a range of cells from a specified spreadsheet into another spreadsheet
Here’s how you can use the IMPORTRANGE function in Google Sheets:
Begin by opening the Google Sheet where you want the data to be imported to.
This is the sheet that will display data from another document.
Find and copy the URL of the Google Sheets document from which you want to import data.
You only need the part of the URL that contains the unique identifier (spreadsheet key), not the entire URL.
Decide which specific range of cells you want to import from the source document.
For example, you might want to import data from ‘Sheet1’ ranging from A1 to D10. The range would be noted as Sheet1!A1:D10.
In your destination Google Sheet, click on the cell where you want the imported data to appear.
Enter the IMPORTRANGE function using the syntax: =IMPORTRANGE(“source_spreadsheet_URL”, “range_string”).
Replace source_spreadsheet_URL with the spreadsheet key from the source sheet and replace range_string with the cell range you decided in Step 3.
In this example, we’ll place our spreadsheet_url and range_string arguments in cells B1 and B2 respectively.
We can then use the formula =IMPORTRANGE(B1,B2) to import the desired data.
If this is your first time connecting these two sheets with IMPORTRANGE, you’ll need to grant permission.
A dialog box will appear asking for access to the source sheet.
Click Allow Access to proceed.
In our current example, the range Sheet1!A1:E13 from the source sheet is now visible in the destination sheet.
To import data along with its formatting, you will need to use a manual process:
Open the source sheet and select the range of cells you want to copy, including the data and formatting.
Copy the selected range (using Ctrl+C or right-click and select Copy).
Go to the destination sheet and select the cell where you want to start pasting.
Paste the copied cells (using Ctrl+V or right-click and select Paste).
This method will copy both the values and formatting from the original sheet.
However, this transfer will only happen once. Any subsequent changes in the original sheet’s data or formatting will not automatically update in the destination sheet.
For continuous updates, you may want to consider using IMPORTRANGE to copy over data and either recreate the formatting manually or use conditional formatting to handle the formatting.
Do you have an Excel workbook or .csv file in your computer that you want to work with in Google Sheets?
Google Sheets comes with an easy-to-use Import tool that allows you to import data from local files.
Here’s how you can import spreadsheet data from a local file:
Open Google Sheets. Go to the Google Sheets website in your web browser.
This is where you’ll be importing your local Google Sheet file.
Once you’re in Google Sheets, look for the “File” menu in the upper left corner of the screen.
Click on it to reveal a dropdown menu with various options.
In the dropdown menu, find and select “Open.”
This action will open a new dialog box that allows you to open existing sheets or import new ones.
In the dialog box, you will see several tabs such as “My Drive,” “Shared with me,” and “Upload.”
Click on the “Upload” tab to proceed with importing your local file.
You can either drag your local Google Sheets file directly into the dialog box or click on “Browse” to browse and select your file using your device’s file picker.
Navigate through your files and select the Google Sheets file that you want to import.
This file should be in a format that Google Sheets can recognize, such as .xlsx or .ods.
Wait for the file to upload and convert.
After selecting the file, Google Sheets will upload and automatically convert it to the Google Sheets format.
This process may take a few moments depending on the size of your file.
Head to the Recent tab to select the local file you’ve just uploaded.
Click Open to convert the selected file into a Google Sheets document.
Once the upload and conversion process is complete, the file will open in Google Sheets.
You can now view and edit this sheet just like any other Google Sheets document.
Do note that there may be some issues when converting a local file into a Google Sheets document.
For example, some formatting features in Excel files may appear differently in Google Sheets.
Some Excel formulas may also behave differently in Google Sheets or are incompatible.
When uploading local files, you may also encounter issues when uploading large files.
Excel files are able to handle workbooks with up to 1,048,576 rows and 16,384 columns which leads to an upper limit of over 17 billion cells.
Google Sheets, on the other hand, can only handle documents with 10 million cells.
Hopefully this guide has given you an overview of the different ways you can import data into Google Sheets.
