Google Sheets + JS class = DX prototyping dream

Developer experience is something our team #HAXTheWeb takes very seriously. While always working to improve our docs (and they need it!) we like to focus on utilities and comments that can take our local development experience to the next level. One approach I stumbled across recently, was the idea of using Google Sheets as an "API backend" for application prototyping.

I can haz code

This will be published in the next release of our codebase to npm, but for now you can access it in our mono repo directly.

Here's that code inline for reference.

import { CSVtoArray } from "@lrnwebcomponents/utils/utils.js";

export class gSheetInterface {
  constructor(target = null, sheetGids = {}) {
    // machineName you want to use => gid from google
    this.sheetGids = sheetGids;
    // sheet
    this.sheet = null;
    this.target = target;
  }
  /**
   * load data from sheet via API
   */
  async loadSheetData(page) {
    return await this.loadCSVData(
      `https://docs.google.com/spreadsheets/d/e/${this.sheet}/pub?output=csv&gid=${this.sheetGids[page]}`,
      page
    );
  }
  /**
   * generate appstore query
   */
  async loadCSVData(source, sheet) {
    return await fetch(source, {
      method: this.method,
    })
      .then((response) => {
        if (response.ok) return response.text();
      })
      .then((text) => {
        return this.handleResponse(text, sheet);
      });
  }
  /**
   * Convert from csv text to an array in the table function
   */
  async handleResponse(text, sheet) {
    // Set helps performantly assemble possible collapsed areas
    let table = CSVtoArray(text);
    let tmp = table.shift();
    let headings = {};
    let data = [];
    for (var i in tmp) {
      headings[tmp[i]] = i;
    }
    for (var i in table) {
      let item = {};
      for (var j in headings) {
        item[j] = table[i][headings[j]];
      }
      // push data onto the database of all data we have now as objects
      data.push(item);
    }
    // allow for deeper processing on the data or just return the data found
    return typeof this.target[`process${sheet}Data`] === "function"
      ? this.target[`process${sheet}Data`](table, headings, data)
      : data;
  }
}

Steps to use this in YOUR code

• Create a sheet, then publish the sheet
• Grab the KEYNAMEHERE part of this address it generates: https://docs.google.com/spreadsheets/d/e/[KEYNAMEHERE]/pubhtml
• this is the this.sheet value
• then call an object of the structure {yourNameYouWant: 0}
• this forms the gid in the URL for each additional page you create in the Google Sheet.

Example integration boilerplate for web components

import { gSheetInterface } from "@lrnwebcomponents/utils/lib/gSheetInterface.js";
export class MyElement extends HTMLElement {
  constructor() {
    super();
    // whatever variable you want for your storage internally
    this.database = {};
    // instance of the class to interface with, add more
    // page gid's to the object with nameYouWant being a key
    // so that you can reference it after the fact
    this.gSI = new gSheetInterface(this, { nameYouWant: 0 });
    this.gSI.sheet = "what-ever-your-shee-id-is-after-publish"; /* replace with your sheetID here */
  }

  async connectedCallback() {
    // loop through all the sheet GIDs and load the data
    for (var i in this.gSheet.sheetGids) {
      this.database[i] = await this.gSheet.loadSheetData(i);
    }
    // render the data however you want now that the
    // "database" has been populated with values
  }
  static get tag() {
    return "my-element";
  }
}
customElements.define(MyElement.tag, MyElement);

Seeing an implemented use-case

This can be seen being used in the grade-book element we're currently working on. This creates an instance of the gSheetInterface class and then leverages it in order to populate a grading interface (still heavily in development but functional).

Short video of the load sequence

Here's a short video (no sound) of the grade-book about a month ago:

Reasons to use this instead of a full Google Docs API setup

• this requires no API setup and junior devs (and non-devs) can set up the API by publishing their google sheet
• the pages within a sheet (gid's) stay consistent, improving DX when cloning the Sheet over to other locations to wire up other "apps"
• Speed to develop / mess with a thing rapidly while it not just being raw JSON blob edits

Limitations of this vs full API access

• Read only; can't write back
• not sure if it gets rate-limited (I have yet to run into this)
• you get ALL the data so enormous sheets / pages will load sequentially very slowly (potentially)
• Caching policy unknown as far as speed of update

I hope this was useful to someone. It's a pretty cool way of interfacing with something that ANYONE can easily go and update the "database". I love these kind of low barrier to entry ways of accessing and manipulating data and I hope to provide some future videos and posts as the UX of the grade-book app becomes more refined.

Comments

[Brad Fisher]

Thanks for this. I'm going to play with it this week.