How to Use Google Sheets Data in an Excel Workbook on OneDrive
Question details
The user needs to connect an Excel workbook stored on OneDrive to Google Sheets data so that it refreshes correctly for multiple users, bypassing desktop-only import limitations.

- Product
- Excel, Google Sheets, OneDrive
- Device & OS
- not provided
- Scenario
- Sharing an Excel workbook on OneDrive that relies on live Google Sheets data.
- Observed behavior
- Data import and formulas function correctly in desktop Excel but fail to refresh or operate once the file is uploaded to OneDrive.
Ensure you have the necessary permissions to publish the Google Sheets file to the web, and verify that your version of Excel supports Power Query (Get & Transform Data).
Connect Google Sheets to Excel using Power Query via Web Endpoint
This method publishes the Google Sheet as a CSV endpoint on the web, allowing Excel to establish a stable cloud-based connection that can be refreshed.
By publishing your Google Sheet as a CSV, you bypass local file-path dependencies. Power Query can then fetch the data via a standard web URL, making it possible to update the data even when the workbook is stored in OneDrive.
Open your Google Sheet, navigate to File > Share > Publish to web. Select the specific sheet you want to export, change the format dropdown to 'Comma-separated values (.csv)', and click Publish. Copy the generated URL.
Open your Excel workbook. Go to the Data tab on the ribbon and click 'From Web' (or navigate to Get Data > From Other Sources > From Web).
Paste the copied Google Sheets CSV URL into the dialog box and click OK. When the Power Query preview window appears, verify the data looks correct and click 'Load' to bring it into your spreadsheet.
Go to Data > Queries & Connections, right-click your new connection, and select Properties. Check 'Enable background refresh' and optionally 'Refresh data when opening the file'. Save the workbook to your OneDrive folder.

Sync Google Sheets and Excel using Power Automate
Set up an automated cloud flow to push new rows from Google Sheets directly to an Excel Online table hosted on OneDrive, avoiding web query limitations.
Experience Seamless Spreadsheet Management with WPS Office
Managing external data connections across different cloud platforms like Google Sheets and OneDrive can be restrictive and complex. WPS Office provides a free, lightweight, and highly compatible alternative. Enjoy powerful data processing, a familiar interface, and seamless document management without the heavy overhead.

Frequently Asked Questions
Why doesn't my Excel web query refresh automatically on OneDrive?
Excel for the Web has restricted support for refreshing external data connections like web queries or Power Query. To successfully refresh the connected Google Sheets data, users typically need to open the workbook in the desktop version of Excel.
Can I link Google Sheets to Excel without making my data public?
Publishing to the web makes the endpoint accessible to anyone who has the exact link. For sensitive or confidential data, use an API-based integration tool like Power Automate or Zapier, which utilizes authenticated connections instead of public URLs.
How do I fix formula errors when importing Google Sheets data?
Ensure your Google Sheet is published strictly as a Comma-separated values (.csv) file rather than a web page. This guarantees that Excel imports raw data values cleanly, preventing HTML tags from disrupting your layout or breaking Excel formulas.
Are there alternatives to Power Query for linking Sheets to Excel?
Yes, you can use third-party automation tools like Zapier or Make, write custom Google Apps Script combined with Office Scripts, or utilize Microsoft Power Automate to sync row data directly between the two platforms automatically.




