See a live demo here: https://dev.crunchprank.net/google-sheets-to-html/
Setting Your Google Sheet To Public
I'm starting this guide on the assumption you've already downloaded the necessary files from the GitHub repository, and have created a Google Sheets document in your Google Drive.
The next step in the process is assuring that your Google Sheet's sharing is set to "Public", specifically with the option "Anyone on the internet can find and view". However editing capabilities are completely restricted and the user can only read the data. Follow the steps below to accomplish this.
Open up your Google Sheet in your Google Drive and click the blue "Share" button at the top right of your browser:
You should now be looking at a dialog box with sharing properties. In the bottom right of that box is a small "Advanced" link. Click that.
The basic sharing settings have now been converted to the Advanced sharing settings. Where you need to make your adjustment is under the "Who can access" portion of this dialog box. Click on the "Change..." link off to the right.
Here is where you define two very important properties. First off, change the "Link Sharing" to "On - Public on the web". Second, the "Access: Anyone (no sign-in required)" option needs to be set to "Can view" - this is very, very important so your document is only viewable and not able to be edited by anonymous users. Once you're sure you've set the two properties to what the need to be, hit Save.
After hitting save on the previous box, you will be taken to another window that looks like the below. The information you need from here is under the "Link to share".
This will give you a link that looks similar to:
This means that your unique Google Sheet ID is:
The final step is super simple, and where the magic happens. Referring back to the files hosted in the GitHub repository, the
js/google-sheets-html.js is where you will need to place this unique identifier which will automatically populate your webpage with whatever Spreadsheet you're referencing to.
Line 18 of
js/google-sheets-html.js, there is a snippet that looks like this:
var query = new google.visualization.Query('https://spreadsheets.google.com/tq?key=XXXXX-XXXXX-XXXXX-X-XXXXX&output=html&usp=sharing');
All you need to do is replace the
XXXXX strings with your unique Google Sheets ID so that it looks, given the example link above, like this:
var query = new google.visualization.Query('https://spreadsheets.google.com/tq?key=1Pm0y3vbhD-kDMKP7gJIZU-RaDD9P2zpz-O-82OZNSrE&output=html&usp=sharing');
Clone / download the files from the GitHub repository and upload them to your website, create a Google Sheets document and set it to be publicly viewed (but not publicly edited), get the share link provided by Google, and replace the placeholder ID in
js/google-sheets-html.js with your Sheet's ID - that's it!
I think the included example is self-explanatory - it's a spreadsheet that only has 4 columns that you've designated header titles for. However if your Google Sheet has more than just the A, B, C, and D columns, then you will need to add those here or the script will generate an error. Say if you had 10 columns, you would need to add A, B, C, D, E, F, G, H, I, J as well as give each of them a header title.
You can also specify column sorting and direction (e.g. asc or desc). For example, if I wanted the Sheet to load by default with column B sorted ascending, I would make this adjustment:
query.setQuery('SELECT A, B, C, D order by B asc label A "Duration", B "Song", C "Requested By", D "URL"');
One last neat little trick is the ability to choose a certain tab of a Google Sheet in case you have a spreadsheet that has multiple tabs but you only want to show one. In this case, you will need to open up the spreadsheet, click on the tab in the spreadsheet you want to use, and look at the URL in your browser. You should see a
gid identifier that looks similar to this:
If you want to display just that one tab, all you need to do is insert that
var query = new google.visualization.Query('https://spreadsheets.google.com/tq?key=XXXXXXXXXXXXXXXXXXXXX&output=html&gid=XXXXX&usp=sharing');
CSS is obviously not necessary for this to function - but it is a necessary tool in making it look better than Google's default CSS styling. You are free to customize the included CSS stylesheet (
css/main.css) however you see fit. I just decided to style it similar to my own site/blog.
In the CSS file, I have commented around the section that relates to Google's CSS. You may also notice all the
!important isn't the most elegant solution, but it gets the job done! Google's class names are also well-named so that you know which is which: there are properties you can adjust for "even" and "odd" rows, "hovering" over the rows, "selecting" a row, and the table header styling.
There are many opportunities you could utilize this very simple script for. However I would highly, highly recommend you to not use this solution for large spreadsheets. I have done some stress-testing and while it can handle quite a bit, after a while it starts to slow down to a crawl, generate errors, etc.
If you have any questions or suggestions on improvements then please reach out to me. HTML/CSS/JS is not my profession so there are bound to be some ways to better the script and I would love the feedback. You can reach me via these contact methods, the comment section below, or forks/requests via the GitHub repository.