Appearance
Connect to Excel
Overview
This guide explains how to use the 'Connect Power BI / Excel' feature in Tube2Sheet to generate Power Query code and import data into Excel. Excel uses a PowerQuery integration with less features than Power BI, so we have to first publish the Google Sheets spreadsheet.
Note: Afer publishing to the web, everyone who knows the URL to the spreadsheet can access it. If you have sensitive data in your spreadsheet, check if you can use Power BI for data ingestion instead.
Step-by-Step Guide
Publish Google Sheets spreadsheet to Web
Open Connection Options
- In Google Sheets click 'File' -> 'Share' -> 'Publish to web'.
Publish to the web
- Select 'Entire Document' -> 'Microsoft Excel (.xlsx)' then click 'Publish'
Copy published spreadsheet URL
- Press Ctrl+C to copy the URL.
Generate Power Query Code in Tube2Sheet
Open Connection Options
- In the Tube2Sheet sidebar, click the "Options" tab.
- Select 'Excel / PowerBI Connect'.
Paste URL and select Sheet
- In the "Connect" window select 'Excel' Then paste the published spreadsheet URL from above step 3 into the first input field.
- Then select the desired sheet.
- After the PowerQuery code is generated, click 'Copy to clipboard'.
Note: If you see the error message 'Make sure to select 'Microsoft Excel' in the publish options.' after you pasted the URL, go back to the sharing options and make sure that you have selected 'Microsoft Excel (.xlsx)'. Then copy and paste the URL again.
Import Data into Excel
Create a Blank Query
- Open Excel and create a new document.
- Click the 'Data' tab, then from 'From Other Sources' select 'Blank Query'.
Open the 'Advanced Editor'
- In the Query Editor, click 'Advanced Editor'.
- Replace the existing code with the copied PowerQuery code from above step 3.
- Click 'Done'.
Finalize Data Import
- Review the imported data.
- Click 'Close & Load' to save and load the query.
Troubleshooting
For additional support, please contact our support team at support@tube2sheet.com.