Trying to build a connection between a Google spreadsheet and an HTML table. I also want this html page and the Google sheet to be able to be editable by one another, and to include on change events that update back and forth. I want to accomplish this using only JS/Jquery and mayyyybe using one of the available Google apis.
<html>
<head>
<style>
table {
font-family: Arial, Helvetica, sans-serif;
border-collapse: collapse;
width: 100%;
}
table td, table th {
border: 1px solid #ddd;
padding: 8px;
}
table tr:nth-child(even){background-color: #f2f2f2;}
table tr:hover {background-color: #ddd;}
table th {
padding-top: 12px;
padding-bottom: 12px;
text-align: left;
background-color: #ff7b00;
color: white;
}
</style>
</head>
<body>
<h1 style="text-align: center;">User Data</h1>
<table class="output"></table>
</body>
</html>
CSS:
<style>
table {
font-family: Arial, Helvetica, sans-serif;
border-collapse: collapse;
width: 100%;
}
table td, table th {
border: 1px solid #ddd;
padding: 8px;
}
table tr:nth-child(even){background-color: #f2f2f2;}
table tr:hover {background-color: #ddd;}
table th {
padding-top: 12px;
padding-bottom: 12px;
text-align: left;
background-color: #ff7b00;
color: white;
}
</style>
JS (currently inline, attempting to avoid indexing in a database, which would defeat the purpose of eventually using a google sheet as a makeshift database):
<script>
const sheetId = '1SdAVVSyqpsyHzOrdfq-w7gDtqmfS8wzxsOfMmre1Guo';
const base = `https://docs.google.com/spreadsheets/d/${sheetId}/gviz/tq?`;
const sheetName = 'user-data';
const query = encodeURIComponent('Select *')
const url = `${base}&sheet=${sheetName}&tq=${query}`
const data = []
document.addEventListener('DOMContentLoaded', init)
const output = document.querySelector('.output')
function init() {
fetch(url)
.then(res => res.text())
.then(rep => {
//Remove additional text and extract only JSON:
const jsonData = JSON.parse(rep.substring(47).slice(0, -2));
console.log(rep)
const colz = [];
const tr = document.createElement('tr');
//Extract column labels
jsonData.table.cols.forEach((heading) => {
if (heading.label) {
let column = heading.label;
colz.push(column);
const th = document.createElement('th');
th.innerText = column;
tr.appendChild(th);
}
})
output.appendChild(tr);
//extract row data:
jsonData.table.rows.forEach((rowData) => {
const row = {};
colz.forEach((ele, ind) => {
row[ele] = (rowData.c[ind] != null) ? rowData.c[ind].v : '';
})
data.push(row);
})
processRows(data);
})
}
function processRows(json) {
json.forEach((row) => {
const tr = document.createElement('tr');
const keys = Object.keys(row);
keys.forEach((key) => {
const td = document.createElement('td');
td.textContent = row[key];
tr.appendChild(td);
})
output.appendChild(tr);
})
}
</script>