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
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
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
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.
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.
In Part II, I will write on how use Power Query Editor to do more with your data.
Applies to Excel 2010,2013, 2016and 2019 (Download Power Query Add-in if using 2010 and 2013