Collaboratively building web pages in a Google Spreadsheet?!

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.

Synchronous remote collaboration | Ship an MVP within hours, not days — 3 hours of livestream condensed into a 21-minute-long video. English subtitles available

---

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.

!!Con

@bangbangcon

Due to COVID-19, !!Con 2020 will be a remote/virtual conference. We'll post more information on Monday, March 16.

04:20 AM - 13 Mar 2020

31 170

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

@galaxykate

Calling 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 @bangbangcon

during #virtualbangbangcon we had a spreadsheet party! check it out here :) (spreadsheet is now locked) https://t.co/TEEbndg75s

12 48

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

@galaxykate

We're having a Spreadsheet party at @bangbangcon, inspired by @tigershungry and others twitter.com/tigershungry/s…

17:27 PM - 10 May 2020

marie 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

1 13

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:

Go to File → Publish to the web and publish the spreadsheet as a TSV file. This will give you a URL. It is CORS-enabled and can be fetch’d from JavaScript. So fetch(url).then(r => r.json()) works! By the way, notice how you can use formulas in the spreadsheet and publish the resulting values!

---

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!

melody! ✨

@pixelyunicorn

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.me

21:54 PM - 10 May 2020

1 6

---

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????