How to Connect a Public Google Sheet with Your Excel File

How to Connect a Public Google Sheet with Your Excel File

Introduction

Are you looking to link a public Google Sheet with your Excel file, ensuring it auto-refreshes and stays in sync? This guide walks you through a step-by-step process. Note that this method applies to Google Sheets shared as "Anyone with the Link." If your Google Sheet has restricted sharing, check out our other blog for restricted file setups.

Step-by-Step Guide

Step 1: Share Your Google Sheet

Ensure the Google Sheet is shared as "Anyone with the Link." To do this:

  • Open your Google Sheet.
  • Click on Share in the top-right corner.
  • Under General access, select "Anyone with the link."

Once done, you will have a shareable link, such as:
https://docs.google.com/spreadsheets/d/1S2JmGIWppILFFQoB5IIdk1GVukvWyms-tcRPKnhmIr0/edit?usp=sharing

Step 2: Modify the URL

Neither the Share Link URL nor the Public URL will work directly for linking with Excel. Follow one of the two options below to modify the URL.

Option 1: Export Link

Replace edit?usp=sharing in the Share Link URL with export?format=xlsx.
For example:
Original URL:
https://docs.google.com/spreadsheets/d/1S2JmGIWppILFFQoB5IIdk1GVukvWyms-tcRPKnhmIr0/edit?usp=sharing
Modified URL:
https://docs.google.com/spreadsheets/d/1S2JmGIWppILFFQoB5IIdk1GVukvWyms-tcRPKnhmIr0/export?format=xlsx

Option 2: Publish to Web

If you prefer, you can publish the Google Sheet to the web:

  1. Go to File > Share > Publish to web.
  2. Select the Entire Document and choose the Excel format.
  3. Copy the generated link.

Use the published link directly in Excel Power Query.

Step 3: Connect to Excel Using Power Query

Follow these steps to link your modified URL in Excel:

  1. Open Excel and go to Data > Get Data > From Web.
  2. Paste the modified URL (from Option 1 or Option 2).
  3. Click OK and follow the prompts to load the data.
  4. Once loaded, the data will appear in your Excel sheet. You can refresh it anytime by clicking Refresh All under the Data tab.

Conclusion

By following these steps, you can seamlessly connect a public Google Sheet with your Excel file, ensuring it stays updated and in sync. If you have any questions or face issues, feel free to leave a comment below.

© 2024 Your Name. All rights reserved.

Core Features of all EffCorp Tools:

  • Test Files Available: We also provide you test files to test the tool once.
  • Fully Secured: All User files Uploaded in our Server get deleted instantly and no file is stored.
  • User-Friendly: Our tool is designed to be intuitive, so even if you're not tech-savvy, you'll find it easy to use.
  • Demo Video Available: Eff Corp Tools also come along with a Demo of its usage wherever required.
  • New Feature is just a Feedback Away: Our Product is actively listening to the feedback for the users and can add features instantly based on your feedback.
  • Coins Per Usage: All tools are charged based on listed coin prices. Refer to the FAQ - Coin System to know more.

Easy Step by Step Guide

Using our tool is as easy as 1-2-3! Just follow these simple steps:

  1. Click on this link Tool Description to access the tool.
  2. Click on the Try Tool Button to use the tool. User should be logged in to use the tool.
  3. Click on "Choose Files" to upload your GSTR1 JSON files (Ensure to follow the Technical Limits of the tool).
  4. Click on the "Convert" button to process the file.
  5. Download the file by clicking on the "Download" button.

Note: If the response is "Failure", please rectify the error or send your feedback for quick action.

And that's it! Your desired output is ready for further processing.

Ready to Give It a Try?

Don't wait! Click here to try the tool and experience the convenience for yourself.

Watch the Demo

Need more guidance? Watch our step-by-step video tutorial and see how easy it is to convert GSTR1 JSON to Excel.

Sign Up for More Features

Want more? Sign up now and get 1000 bonus coins to access all available tools!

Want to Host Your Tool on Our Platform?

Please reach out and our team will connect with you. Contact Us

Want a Custom Tool for Your Company?

Please reach out and our team will connect with you. Contact Us