Google Sheets is a cloud-based device independent application, that works on all current web browsers. And its flexibility and compatibility don’t end there. It can also read and write in multiple spreadsheet formats, making it
The usual suspects
The existing functions that usually comes to mind are Share and Download as. While they are fantastic features in themselves, they come with inherent problems.
With Share, the other users who have access to the spreadsheet will only be able to access the file using Google Sheets application. They do not have it readily available for their own spreadsheet applications like Open Office or Microsoft Excel.
Download as overcomes the limitations of Share to some extent. With this we can share the file in multiple formats such as .xlsx, .ods, .pdf, .html, .csv or .tsv. But again, there’s a caveat. The files we download and send over to others, will not remain current. What if we changed the Google Sheets spreadsheet after we download it in one of the formats? There’s no way these changes are going to reflect in the downloaded files.
Solution: Use ‘Publish to web’ functionality to automatically export Google Sheets to other formats
Google Sheets has an excellent inbuilt utility called Publish to the web. By using this, not only can we derive the benefits of Share and Download as, but also the limitations discussed above can be handled. To access this, navigate to File > Publish to the web… option.
Once we invoke this functionality, we see the pop-up window that gives us options to link or embed. Publishing through the link enables us to share a URL which the end users can download the file from. The format they get to download is the one we choose from any of the following options.
For a quick demonstration, let us choose the Web page format, and click the
When they navigate to that URL, they will see a web page, separately built out of the spreadsheet data we were working on.
When we choose one of the other formats (i.e. .csv, .tsv, .pdf, .xlsx, .ods) for publishing, and the user accesses the URL, they will download the corresponding file to their computer automatically.
If instead of just sharing the link, we want to embed this document or part of it, in another web page, Google Sheets lets us do this! Please see the snapshot below. All we need to do is copy the code and embed it
If at any point in time, we wish to stop publishing, we can do so using the Stop publishing button at the bottom of the Publish to the web window pane.
One of the best things about this functionality is that the content remains current (not older than 5 minutes). Irrespective of the day and time the link (or embedded code) is accessed on!
Congratulations! We just explored the process to automatically export Google Sheets to other formats, using the ‘Publish to web’ functionality.
For the other way around, to automatically import CSV or Excel files to Google Sheets, check the following blog posts: How to Automatically Import CSV data to Google Sheets and Import Filtered Data from Excel to Google Sheets.