Embedding a Google Sheet as an HTML Table

Stevie Howard
Sep 7, 2017 · 2 min read

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:

The fully rendered Google Sheet.

Here’s the HTML table alone:

The resulting HTML table.

If you inspect the HTML, you’ll see that the entire body consists solely of a <table>.


BUT HOW?


To do this, generally, you will need four things:

  1. 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.
  2. The URL of your Google Sheet.
  3. The ✨magic string*/gviz/tq?tqx=out:html&tq&
  4. 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.
Finding the Sheet ID

To walk through a specific example, here (again) is our sample Sheet with some data.

  1. Double check the permissions on the file. In this case, the file is a publicly-viewable example spreadsheet, so we’re good to go.
  2. Take the URL of the Google Sheet:https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0
  3. Notice that the Sheet ID in this case is zero:
    gid=0
  4. Remove the trailing edit#gid=… from the URL:
    https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/
  5. Append the ✨magic string✨ (gviz/tq?tqx=out:html&tq&) to the URL:
    https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/gviz/tq?tqx=out:html&tq&
  6. Append the Sheet ID (gid=0) to the URL:
    https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/gviz/tq?tqx=out:html&tq&gid=0

Additionally, you can set the refresh/recalculation rate under File > Spreadsheet settings to be as often as every minute, if necessary for your spreadsheet.

Boom.


* OK, fine. It’s not actually magic. It’s a Google web service endpoint.

Stevie Howard

Written by

Technology, Mechanics, and Engineering. Currently trying to solve more problems than I create at Microsoft.