<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<link rel="icon" type="image/png" href="favicon.png">
<title></title>
<meta name="description" content="" />
<style>
</style>
</head>
<body>
<table>
<thead>
<tr><th>1</th><th>2</th><th>3</th><th>4</th></tr>
</thead>
<tbody>
<tr><td>a</td><td>2</td><td>3</td><td>4</td></tr><tr><td>b</td><td>2</td><td>3</td><td>4</td></tr>
</tbody>
</body>
</html>
const fs = require('fs');
const nunjucks = require('nunjucks');
const express = require('express');
const { GoogleSpreadsheet } = require('google-spreadsheet');
const app = express();
async function loadSheet() {
const doc = new GoogleSpreadsheet('');
await doc.useServiceAccountAuth({});
await doc.loadInfo();
console.log(doc.title);
const sheet = doc.sheetsByIndex[0];
console.log(sheet.title);
const rows = await sheet.getRows();
console.log(sheet.headerValues);
var table = {
head: [],
body: []
}
table.head.push(sheet.headerValues);
for (var i=0;i<rows.length;i++){
table.body.push(rows[i]._rawData);
}
console.log(table);
var template = `<table>
<thead>
{% for row in head %}<tr>{% for i in row %}<th>{{ i }}</th>{% endfor %}</tr>{% endfor %}
</thead>
<tbody>
{% for row in body %}<tr>{% for i in row %}<td>{{ i }}</td>{% endfor %}</tr>{% endfor %}
</tbody>`;
var tables = {table1: nunjucks.renderString(template,table)};
var html = nunjucks.render("template.html",tables);
fs.writeFileSync("index.html",html);
}
var table = {
head: [[1,2,3,4]],
body: [['a',2,3,4],['b',2,3,4]]
}
var template = `<table>
<thead>
{% for row in head %}<tr>{% for i in row %}<th>{{ i }}</th>{% endfor %}</tr>{% endfor %}
</thead>
<tbody>
{% for row in body %}<tr>{% for i in row %}<td>{{ i }}</td>{% endfor %}</tr>{% endfor %}
</tbody>`;
var tables = {table1: nunjucks.renderString(template,table)};
var html = nunjucks.render("template.html",tables);
fs.writeFileSync("index.html",html);
app.use('/', express.static('./'))
app.listen(3000, () => {
console.log('server started');
});
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<link rel="icon" type="image/png" href="favicon.png">
<title></title>
<meta name="description" content="" />
<style>
</style>
</head>
<body>
{{ table1 | safe }}
</body>
</html>
About
This repl generates HTML tables automatically from Google Sheets. Auto-update the tables on a schedule.
This repl won't work until you add a link to a Google Sheet and authenticate.
To view this on TripleLog, click here
Google Sheets
Create a sheets doc and make it accessible.
Create the API account if you haven't already and add a service worker account key.
View the google-spreadsheet documentation to get started.
Read my blog post about how to convert a Google Sheet to an HTML table.
HTML
The HTML currently is a barebones table, but you can style to meet your needs.
NodeJS Server
The server will generate a table from the current version of the spreadsheet each time it starts up. You can make it automatically regenerate every few hours, manually generate whenever you make changes, or do whatever makes sense.
The generated file is a static file so it can be served by your preferred method of serving static files.
TripleLog