I love building software collaboratively, especially when it is real-time.
On January, I and few other JavaScript Bangkok 1.0.0 organizers got together and built the website using CodeSandbox Live, and we also went on livestream. It was one of the most productive livestream I ever had, and I posted a recap video.
Last week, I ran a rapid prototyping remote workshop for the Young Creator’s Camp. In the workshop, we prototyped simple web-based applications to streamline the process of running our camp meetings.
We collaboratived live on Glitch. Unfortunately, having 30 people on the same Glitch project made the editor unusably slow. So we had to reduce the number of people working on the code down to ~4 people, then livestreamed it through Discord for the rest of the campers to spectate.
We also used InVision’s Freehand to collaborate on design mockups and to manage tasks. It handles 30 people on it simultaneously just fine.
Then last weekend, there was !!Con 2020. It’s a 2-day conference to celebrate “the joy, excitement, and surprise of computing.” Years before, I’ve watched !!Con talks on YouTube and I love the 10-minute talk format. I thought it would be great if I could attend it once in a lifetime. I have never been to the States, though.
I immediately bought a ticket when I heard that this year’s conference will be a remote one.
Now, I don't have high expectations about remote conferences; I've never attended one. I mean, usually they have free livestream and conference-goers are on Twitter anyways don’t they? Also I wasn’t really good at networking (very introverted).
But !!Con totally subverted my expectations. This tweet sums it up best:
Kate Compton thinks its universal healthcare time@galaxykateCalling it: @bangbangcon is the first quarantine event that nails "conference feel"
- Virtual rooms with population markers (which is busy and which is empty?) and auto-join Zoom chats
- Neighbor-matching for chats
- Jumpin' discord subthreads
- Creative spreadsheet party twitter.com/bangbangcon/st…23:22 PM - 10 May 2020!!Con @bangbangconduring #virtualbangbangcon we had a spreadsheet party! check it out here :) (spreadsheet is now locked) https://t.co/TEEbndg75s
Apart from the Discord chat (with a channel for each talk!), a “virtual conference space” where people can meet on Zoom (I joined a few sessions but didn’t talk much), awesome talks with amazing live captions, “unconference” talks, there’s also this “spreadsheet party…”
Kate Compton thinks its universal healthcare time@galaxykateWe're having a Spreadsheet party at @bangbangcon, inspired by @tigershungry and others twitter.com/tigershungry/s…17:27 PM - 10 May 2020marie foulston @tigershungry₊⁺˳✧༚˚✧₊⁺˳✧༚ ˚✧₊⁺ PARTY IN A SHARED GOOGLE DOC ₊⁺˳✧༚˚✧₊⁺˳✧༚ ˚✧₊⁺ WHEN!? Tonight, 9:30pm BST WHERE!? Friends & mutuals DM me a preferred google docs email B4 9pm. A spreadsheet invite will go out at 9:30pm. Once in, you’re then welcome to invite other friends https://t.co/jf3NqWTHFS
It’s an open spreadsheet for the attendees. Inside, people have made pixel art, zine fair, art gallery, etc. There’s also a “badge creation station” where people can create their own name badge. And a sheet full of cat pictures.
So I got an idea: Why not make a webpage on Google Spreadsheets together?
This shouldn’t be too hard to implement. After all, I once published JSON data directly from a Google Spreadsheet:
So I quickly made a Glitch project that loads a published TSV, and send it verbatim (with the text/html
MIME type).
const fetch = require('node-fetch')
const express = require('express')
const app = express()
app.get('/', (req, res, next) => {
fetch(process.env.SHEET_URL)
.then((r) => r.text())
.then((x) => res.send(x))
.catch(next)
})
const listener = app.listen(process.env.PORT, () => {
console.log('Your app is listening on port ' + listener.address().port)
})
…and created a sheet titled “A Web Page?!” in the spreadsheet party, with something like this…
…soon people noticed and started adding some links, as well as some JavaScript to check if it works (it does).
Soon I hit an obstacle:
Google only updates the published TSV once every 5 minutes.
I cannot change the spreadsheet and hit refresh to see the result; I had to wait 5 minutes. This would ruin the fun! So I looked for alternatives.
Thankfully, Google Sheets API v4 has a very simple endpoint to read a public (anyone with the link can view) spreadsheet. I only need to use this URL:
https://sheets.googleapis.com/v4/spreadsheets/<ID>/values/'<SHEET>'!A:Z?key=<KEY>
-
<ID>
is the spreadsheet ID from the URL -
<SHEET>
is the sheet name. -
<KEY>
is a Google Cloud Project API key.
It gives a JSON that looks like this:
{
"range": "'A Web Page?!'!A1:Z1061",
"majorDimension": "ROWS",
"values": [
["<!doctype html>"],
["<!--"],
["https://unleashed-simplistic-lingonberry.glitch.me/"],
["-->"],
["<h1>", "Hello", "</h1>"]
]
}
Then I changed the logic to this:
app.get("/", (req, res, next) => {
- fetch(process.env.SHEET_URL)
+ fetch(process.env.SHEET_URL2)
- .then((r) => r.text())
+ .then((r) => r.json())
+ .then((v) => v.values.map(v => v.join("\t")).join("\n"))
.then((x) => res.send(x))
.catch(next)
})
…and it now reloads in realtime! (I also added some extra caching logic to make sure that we are hitting the API at most once per second. See the full source code here.)
…and we got a webpage going!
my fav part is how @dtinth turned this sheet turned into a webpage using @glitch! 🎉 #virtualbangbangcon @bangbangcon
(cw: rapidly moving and flashing graphics) webpage here: …shed-simplistic-lingonberry.glitch.me21:54 PM - 10 May 2020
I was so amazed by the expression of creativity shown throughout the conference (including what people did to the spreadsheet).
I learned that you can force a style and script tag to show its content using CSS. And if you add contenteditable
to a style
tag, people can edit it and the CSS change will be reflected on the webpage immediately:
<style contenteditable>
style,
script {
display: block !important;
white-space: pre;
}
</style>
I learned that an editable Google Sheets can be embedded onto a web page using an iframe
, because someone embedded the source Google Sheet into the webpage itself!
Seeing how well this worked out, I'm definitely going to try making web pages with Google Sheets again in the future. I still see some untapped potential here.
- Google Sheets can store more than just text. The source spreadsheet can be annotated improve the readability with comments, borders, colors, and graphics that are stripped out when converting into the webpage. Cells can also be merged. This reminds me of a the Elastic tabstops concept.
-
Fragments of code and contents can be reused by using Excel formulas. It can reference cells from other sheets and perform data lookup e.g. using the
VLOOKUP
function. -
Google Sheets can translate text using the
GOOGLETRANSLATE
function. -
Google Sheets can scape other webpages using the
IMPORTXML
function. - A Google App Script can be used to automate some parts of the spreadsheet.
Maybe an entire static site can be generated using Google Sheets????
Top comments (0)