Convert a Google Sheet to a HTML Table

Setup Google Account

If you haven't already created an account to work with Google APIs, you will need to do that first. Visit https://console.developers.google.com/ to get started. The unofficial google-spreadsheet module has documentation and the Google page has a search bar at the top to find up-to-date docs.

The process may change, so follow any updated documentation. You will need to create a project, link it to the Google Sheets API, and create a service account so you can download a token for authentication. If you already have a Gmail account then the process should be painless. It only takes a minute and once it is done you can very quickly add more spreadsheets.

Create Sheet

Visit spreadsheet.new or open up an existing doc. The sheet needs to be publicly available so click share at the top and make it available to anyone with the link. People will only be able to view (not edit) the spreadsheet and only if they know the id/link.

In the next step, you will need to know the id of the sheet. The id is the long string of characters between "/" in the url. For now it immediately follows the "https://docs.google.com/spreadsheets/d/".

Once you have created the spreadsheet you just need to fill it with data. You can import from another file or manually add the data to each cell, and the API handles multiple sheets if needed.

Add JavaScript to Sheet

If you like writing JavaScript, then it is quite simple to add arbitrary JavaScript formulas to your spreadsheet. Create an Apps Script from the Extensions button in the top menu.

Create functions in the Code.gs file and save after making changes. These functions can be called from any cell in the spreadsheet using the values of other cells as inputs. For more information, view Google's documentation.

Load Sheet Data

We will use the google-spreadsheet NPM package. You can use the official Google package that includes more APIs, but if you just need Sheets access then I like this module. Visit the documentation to see if anything has changed or if you have special requirements.

const { GoogleSpreadsheet } = require('google-spreadsheet');
async function loadSheet() {
	const doc = new GoogleSpreadsheet('ID_HERE');
	await doc.useServiceAccountAuth({AUTH_TOKEN_HERE});
	await doc.loadInfo();
	const sheet = doc.sheetsByIndex[0];//grabs the first sheet
	const rows = await sheet.getRows();
	var table = {
		head: [],
		body: []
	}
	//the headerValues array is loaded when getRows() is called.
	table.head.push(sheet.headerValues);
	for (var i=0;i<rows.length;i++){
		table.body.push(rows[i]._rawData);
	}
}

The GoogleSpreadsheet function will load the speadsheet with the given ID (the long string of characters in the url of the sheet). You can set up authentication for your specific purposes, but downloading a service account object is one option.

If your data needs are more complex than loading the entire first sheet, go to the documentation for the module. I like to create an object with head and body arrays, but you can create whatever object you want to use with the templating style you prefer.

Filter with CSS

Once we have created the HTML table, we can add some simple functionality to filter the table with just CSS. If you prefer to filter with JavaScript then you can see this repl for a simple example.

We will add radio boxes that hide some rows. The labels will be placed on the header row. Some filters can be implemented within the nunjucks syntax. For more complex filters, pre-filter with JavaScript and then send a binary 0 or 1 to the template. Each row that needs to be filtered will have classes that identify which filters should hide that row.

{% for row in head %}
	{% for i in range(1,row.length-2) %}
	<input type="radio" name="token" id="token-0" checked></input>
	<input type="radio" name="token" id="token-1"></input>
	{% endfor %}
{% endfor %}
<table>
	<thead>
		{% for row in head %}<tr>{% for i in row %}<th>{% if loop.index > 3 %}<label id="labelt{{loop.index - 3 | int}}-0" for="token{{loop.index - 3 | int}}-0">{{ i }}</label><label id="labelt{{loop.index - 3 | int}}-1" for="token{{loop.index - 3 | int}}-1">{{ i }}</label>{% else %}{% endif %}</th>{% endfor %}</tr>{% endfor %}
	</thead>
	<tbody>
		{% for row in body %}<tr class="{% for i in range(3,row.length) %}{% if row[i] < 1 %}nott{{ i - 2 }}-1 {% endif %}{% endfor %}">{% for i in row %}<td>{{ i | safe}}</td>{% endfor %}</tr>{% endfor %}
	</tbody>
</table>

In the CSS, we simply look for which filters are checked and then hide the rows with the appropriate class. We also only want to display one label in each header cell depending on which filter is currently selected for that column.

{% for row in head %}
	{% for i in range(1,row.length-2) %}
	#token{{ i }}-1:checked ~ table tbody tr.nott{{ i }}-1 {
		display: none;
	}
	#token{{ i }}-0:checked ~ table #labelt{{ i }}-0 {
		display: none;
	}
	#token{{ i }}-1:checked ~ table #labelt{{ i }}-1 {
		display: none;
	}
	{% endfor %}
{% endfor %}

You can also add filter buttons anywhere outside of the table for filters that might depend on multiple columns. The input element needs to be placed above the table so that the checked value can cascade down, but the labels can go anywhere. You can even create multiple label elements for one input.

It is probably a good idea to add some information about which filters are active and an option to deselect them. You can get creative about how best to implement this feature.

Final Product

To see an implementation of this table, go here. Here is a repl on linking up with Google Sheets and here is a blog post with more information on creating a variety of HTML tables.