Chat with us, powered by LiveChat
Home > Blog > Fetch Google Sheet Data to Excel using Power Query – Part I

Fetch Google Sheet Data to Excel using Power Query – Part I

Fetch Google Sheet Data to Excel using Power Query – Part I

When working with Google Sheets you always get the latest data even from forms that are entered remotely. Integrating Microsoft Excel with Google sheets enables users to get the latest version of their data  – with Google sheets link as the data source in Excel Power Query.

Organizations and Individuals collecting data in multiple locations can design forms that dump data to Google Sheets.

Step 1: Go to Google Forms then click the plus sign to create a new form

Step 2: After creating your form, enable responses and create a new google sheet

Downloading Google Sheets as CSV
Downloading Google Sheets as CSV

Step 3: Download the google sheet as CSV and locate the download link (We really are not after the downloaded file just the link)

Step 4: Locate the download link from your downloaded file. In Mozzila, the link is located on the top right on your window.

Step 4: Open Microsoft Excel and locate the Data Tab on the menu.

Microsoft Power Query part of the Get & Transform Data in Microsoft Excel 2016 and 2019. If you are using Microsoft Excel 2010 and 2013 then you may need to download the Addin from this Microsoft Website

 

Get & Transform Data
Get & Transform Data

Step 5: Click on “From Web” or Click on Get Data > From other Sources > From Web.

Step 6: Paste the link on the Data Source under basic, then click OK for the Power Query Editor to load

Get Data from Web to Excel
Get Data from Web to Excel

Step 7: When the Power Query Editor loads, you can click on edit to do more with your data or just click LOAD so that the data is loaded to Microsoft Excel from Google Sheets.

Power Query Editor
Power Query Editor

Once loaded to Excel the Power Query data model will be updating the Excel sheet when data is entered in the google sheets. Under the data tab > Properties you can set the refresh intervals and how you want Excel to behave on open.

The default refresh interval is 60 Minutes.

Watch Video

In Part II, I will write on how use Power Query Editor to do more with your data.

 

READ: How to Merge Multiple Sheets with Power Query for Excel 2016 & 2019

READ: How to Consolidate Multiple Excel Sheets with Tables

 

Applies to Excel 2010,2013, 2016and 2019 (Download Power Query Add-in if using 2010 and 2013

 

Request for Proposal

 

 

 

 

 

Share with your followers...
Email this to someone
email
Share on Facebook
Facebook
Tweet about this on Twitter
Twitter
Share on LinkedIn
Linkedin
ga('send', 'pageview');
× Chat with us!