DEV Community

loading...
Cover image for Collaboratively building web pages in a Google Spreadsheet?!

Collaboratively building web pages in a Google Spreadsheet?!

dtinth profile image Thai Pangsakulyanont ・5 min read

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.

23 people in the same Glitch project, the browser is stuttering.

We also used InVision’s Freehand to collaborate on design mockups and to manage tasks. It handles 30 people on it simultaneously just fine.

A screenshot of InVision Freehand. Within, there is an agenda, a rough wireframe design, and a task board.


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:

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…”

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:

An example of JSON in a spreadsheet.

Go to FilePublish 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…

Initial sheet

…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!


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!

https://dev-to-uploads.s3.amazonaws.com/i/q1iq8ejxgtqk9p4hkxlj.png

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

Discussion (0)

pic
Editor guide