Collaboratively building web pages in a Google Spreadsheet?!
Tags: showdev, watercooler
Published: 2020-05-12 16:49:06

---
title: 'Collaboratively building web pages in a Google Spreadsheet?!'
published: true
description:
tags: showdev, watercooler
cover_image: https://dev-to-uploads.s3.amazonaws.com/i/loie0g9lu7nx6jwi3v64.png
---

I love building software collaboratively, especially when it is real-time.

On January, I and few other [JavaScript Bangkok 1.0.0](https://javascriptbangkok.com/) organizers got together and built the website using [CodeSandbox](http://codesandbox.io/) Live, and we also went on livestream. It was one of the most productive livestream I ever had, and I posted a recap video.

{% youtube uQH2R-BE1lw %}

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

---

Last week, I ran a rapid prototyping remote workshop for the [Young Creator’s Camp](https://ycc.in.th/). In the workshop, we prototyped simple web-based applications to streamline the process of running our camp meetings.

We collaboratived live on [Glitch](https://glitch.com/). 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.](https://dev-to-uploads.s3.amazonaws.com/i/ktnca48q2xb4ru2dcr6r.jpg)

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.](https://dev-to-uploads.s3.amazonaws.com/i/799bwix1cbis1sni0kj9.jpg)

---

Then last weekend, there was [!!Con 2020](http://bangbangcon.com/). 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.

{% twitter 1238319003543756800 %}

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:

{% twitter 1259624844708024320 %}

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

{% twitter 1259535509803503616 %}

It’s an open spreadsheet for the attendees. [Inside](https://docs.google.com/spreadsheets/d/15yh9gABz2ZgHwbkOD5cXxgT_RQx7-ituTVy6dIQ11DM/edit#gid=0), 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.](https://dev-to-uploads.s3.amazonaws.com/i/lrilwdn9v2ca62f21rts.png)

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

---

So I quickly made a Glitch project that loads a published TSV, and send it verbatim (with the `text/html` MIME type).

```js
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](https://dev-to-uploads.s3.amazonaws.com/i/1w09tey884wmlxiefnmm.png)

…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:<br />**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](https://developers.google.com/sheets/api) has [a very simple endpoint](https://developers.google.com/sheets/api/samples/reading) 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:

```json
{
  "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:

```diff
 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](https://unleashed-simplistic-lingonberry.glitch.me/server.js).)

…and we got a webpage going!

{% twitter 1259602643825102853 %}

---

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:

```html
<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](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](http://nickgravgaard.com/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`](https://support.google.com/docs/answer/3093318?hl=en) function.
- **Google Sheets can translate text** using the [`GOOGLETRANSLATE`](https://support.google.com/docs/answer/3093331?hl=en) function.
- **Google Sheets can scape other webpages** using the [`IMPORTXML`](https://support.google.com/docs/answer/3093342?hl=en) 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????](https://website-from-google-sheets.glitch.me/)