Sometimes it’s handy to use the powers of a spreadsheet to process and manipulate data, and sometimes it’s even more useful to display that data in real time, with the functionality of the spreadsheet running on the back end.
You can do this for free with Google Sheets.
Here’s an example spreadsheet:
Here’s the HTML table alone:
If you inspect the HTML, you’ll see that the entire body consists solely of a
To do this, generally, you will need four things:
- Set the permissions on the Google Sheet to “Anyone with the link can view”. You can double-check that you’ve done it properly by trying the link in an Incognito/Private Browsing window in your browser. If the permissions are set correctly, you’ll be able to view the spreadsheet without having to sign in.
- The URL of your Google Sheet.
- The ✨magic string✨* —
- The Sheet ID (i.e. gid) in the URL. You may have several different sheets in the “workbook”, and each has a different Sheet ID, so be sure that you’re looking at the correct sheet for your purposes.
To walk through a specific example, here (again) is our sample Sheet with some data.
- Double check the permissions on the file. In this case, the file is a publicly-viewable example spreadsheet, so we’re good to go.
- Take the URL of the Google Sheet:
- Notice that the Sheet ID in this case is zero:
- Remove the trailing
edit#gid=…from the URL:
- Append the ✨magic string✨ (
gviz/tq?tqx=out:html&tq&) to the URL:
- Append the Sheet ID (
gid=0) to the URL:
Additionally, you can set the refresh/recalculation rate under File > Spreadsheet settings to be as often as every minute, if necessary for your spreadsheet.
* OK, fine. It’s not actually magic. It’s a Google web service endpoint.