Google Sheets’ IMPORTDATA function and Excel’s Power Query tool are useful for automated data updates. Both allow users to import external data into their spreadsheets, but they offer different kinds of data automation. This post will show you how to automate data updates using Google Sheets’ IMPORTDATA function and Excel’s Power Query while comparing them.
What is the IMPORTDATA function in Google Sheets?
The IMPORTDATA function in Google Sheets imports data from a web-based CSV (Comma Separated Values) or TSV (Tab Separated Values) file. The function auto-updates data at regular intervals or whenever you open the sheet, and users cannot manually set the frequency of updates.
Syntax:
=IMPORTDATA(URL, delimiter, locale)
- URL: It is the URL from where you want to import the CSV and TSV formatted data.must be
- delimiter: The delimiter to use when parsing the data. It must be a single character and may not be a double quote or whitespace character other than a tab. If unspecified, the delimiter is inferred from the file name: comma for CSV, tab for TSV.
- locale: A language and region locale code to use when parsing the data. If unspecified, the document locale will be used.
N.B: The external data you want to import must be hosted online and publicly accessible or at least provide direct access to the CSV/TSV file.
What is Power Query in Excel?
Excel’s Power Query is a powerful tool to import, transform, and auto-update external data. It can import data from websites, databases, cloud sources, APIs, and even local files. It supports various data sources and allows you to customize the refresh settings. It offers more control over the data refresh schedule and data handling to auto-update the data.
Using Google Sheets’ IMPORTDATA Function and Excel’s Power Query to Automate Data Updates
Let’s show you the use and auto updates of imported data by using the IMPORTDATA function and Power Query tool. We have created a CSV file of supply chain data and published it on the web. Now, we will update the data to see the automatic data update.
Sample Dataset:
Google Sheets’ IMPORTDATA Function
Let’s use the IMPORTDATA function to import data and automate the data updates. Select cell A1 and insert the following formula.
Formula:
=IMPORTDATA(“https://docs.google.com/spreadsheets/d/e/2PACX-1vTqUPqsIVr7_rO8gtnq9_HEayDfoKXhKBTF0WwMRLiRDnZuKaJVOuFRVcUp5XfU2oorxpi7Ldh_92t9/pub?gid=0&single=true&output=csv“)
Explanation:
- “https://docs.google.com/spreadsheets/d/e/2PACX-1vTqUPqsIVr7_rO8gtnq9_HEayDfoKXhKBTF0WwMRLiRDnZuKaJVOuFRVcUp5XfU2oorxpi7Ldh_92t9/pub?gid=0&single=true&output=csv“: This is the URL of the supply chain data from where you can import the data.
This formula will load the entire dataset. To see the automated data updates remove the Product Name column and add Quantity Ordered and Cost Per Unit columns in supply chain data.
Output: After regular intervals, data updates automatically.
Excel’s Power Query
Now use the Power Query tool to automate data updates. First, load data via Power Query:
- Go to the Data tab >> select From Web >> insert the URL or location >> click OK.
- To import the data from the web, select Load.
Now, to set up automatic data updates,
- Go to Data > Queries & Connections.
- Right-click on the query >> select Properties.
- Select Refresh every >> 5 minutes >> click OK.
Every 5 minutes it will refresh to auto-import data from the source.
Conclusion
Google Sheets IMPORTDATA function is easy to use for automated data imports of CSV or TSV files. It’s a quick and easy solution but only supports basic file types (CSV/TSV) and doesn’t allow much control over the refresh interval, as Google handles updates automatically. Excel’s Power Query is a more robust tool for data automation. It supports a wide range of data sources you can have control over how often the data will update. Power Query is particularly useful for complex data workflows and it requires some additional steps Based on the data type format and complexity you can choose any of these tools.
