Fetch data from google sheets as a JavaScript object!!

spreadsheets.md

Get data from google sheets!

This script gets data from a google sheets table and returns it in easy to use JSON. Tables must have a header 😃

How to get the spreadsheet ID:

Publish it to the web:

The ID is in the link that publish to the web gives you, NOT the webpage URL

https://docs.google.com/spreadsheets/d/e/2PACX-1vQvFX6gBzJOJEU01O-R_iuMfCr-5k8aQAvEzumMh9nPKjoe3PcOKiZgfG7OWXX1ahV8Alv325H6UqUu/pubhtml

The code:

/*
If you have data in this format:

+==============+====================+======================+
|     Name     |   Date of birth    |    Email address     |
+==============+====================+======================+
| John Doe     |  January 1st 2000  | johndoe@gmail.com    |
+--------------+--------------------+----------------------+
| Someone Else | December 30th 1995 | someonelse@gmail.com |
+--------------+--------------------+----------------------+

it will return:

[
  {
  	name: "John Doe",
    date_of_birth: "January 1st 2000",
    email_address: "johndoe@gmail.com"
  },
  {
  	name: "Someone Else",
    date_of_birth: "December 30th 1995",
    email_address: "someonelse@gmail.com"
  }
]
*/

async function getSpreadsheet(id, pageNum = 1) {
  if (id.includes("/")){
    //If the ID is a URL
    id = id.split("/")[6]
  }
  //Fetch data from google sheets and get JSON
  const out = await fetch(`https://docs.google.com/spreadsheets/d/e/${id}/pub?output=tsv`).then(res => res.text()).then((res) => {
    res = res.split("\n").map(i => i.trim()).map(i => i.split("\t"));
    return res  
  })
  //Now we need to make it into an object based on the table headers.
  var out2 = [];
  for (let item of out) {
    let _temp = {};
    for (let i in item) {
      //Make it a nice object key.
      _temp[
        out[0][i]
          //Lowercase
          .toLowerCase()
          //Replace all non alphanumeric characters
          .replace(/[^a-zA-Z0-9 _]/g, "")
          //Replace spaces with underscores
          .replace(/ /g, "_")
      ] = item[i];
    }
    out2.push(_temp);
  }
  //We assume that the table has headers, so we take off the header row.
  return out2.slice(1);
}

Try it with this:

await getSpreadsheet("https://docs.google.com/spreadsheets/d/e/2PACX-1vSJnsIuvYA3agfO8qIm74r5oTJ3msWmb2ArnAgz_Zp8H7ipmBpT9uwxhO0-w-hXToyPTB4GgUAqZ4yA/pubhtml")

Or this:

await getSpreadsheet("2PACX-1vSJnsIuvYA3agfO8qIm74r5oTJ3msWmb2ArnAgz_Zp8H7ipmBpT9uwxhO0-w-hXToyPTB4GgUAqZ4yA");

(Returns the data that I put into that comment at the top of the code)

Weird, no longer works

Update: Fixed it