loading data from a database

the idea

before touching any code, it helps to understand what's actually happening here. your site is a handful of static files sitting on a server somewhere, html, css, and js, nothing more. it has no memory and no way to "remember" your book log between visits unless you write that data directly into the html, which means editing and re-uploading a file every single time you finish a book. that gets old fast.

the fix is to keep your data somewhere else, somewhere built for editing rows and columns, like a spreadsheet or a notion page, and have your page ask that service for the current data every time someone loads it. that's really all a "database" is doing here, it's just a place your data lives that isn't your html file. every method in this tutorial, no matter which service is holding the data, follows the exact same three steps:

• put your data somewhere that can hand it back as json (a sheet, a notion database, whatever) when your page asks for it. json is just a text format for describing structured data, lists, objects, key and value pairs, that both humans and javascript can read easily
• use javascript's fetch() to request that json from your page. fetch() is a built in browser function that sends a request to a url and waits for whatever comes back
• loop through what comes back and build html out of it, so each row in your data turns into a card, a list item, or whatever markup you want on the page

that third step looks the same no matter where the data came from, so it's worth understanding this snippet fully before moving on, since you'll see a version of it in every section below:

fetch("YOUR_DATA_URL_HERE")
  .then(response => response.json())
  .then(data => {
    data.forEach(item => {
      console.log(item);
      // build and insert html here
    });
  })
  .catch(error => console.error("couldn't load data:", error));

• fetch(url): sends a request to that url and immediately returns a "promise", which is basically a placeholder for a response that hasn't arrived yet. your code doesn't pause and wait for it, it moves on and comes back to it once the response is ready
• .then(response => response.json()): once the response arrives, this line reads its body and parses it as json, handing you back a normal javascript array or object you can actually work with. this itself returns another promise, which is why there's a second .then() chained right after it
• .then(data => {...}): this is where the parsed data finally lands in a variable named data, and where you actually do something with it, usually looping over it with .forEach() to build html for each entry
• .catch(...): runs if anything goes wrong anywhere in the chain above it, a bad url, no internet connection, the data source being offline or renamed. always include this, or a broken data source will just fail silently and your page will look empty with no clue why

so really, the only thing that changes between the three sections below is what url you put inside fetch(), and what shape the json comes back in. everything else, the promise chain, the loop, the error handling, is identical. keep that in mind as you read through each method, you're not learning three unrelated things, you're learning one pattern applied to three different data sources.

google sheets

the easiest option if your data is just simple rows and columns, like our book log, and you don't need to run any code of your own. we'll use one consistent example the whole way through: a sheet called "books" with three columns, title, author, and status, holding rows like this:

title                 author               status
the cruel prince       holly black          finished
the foxhole court      nora sakavic         finished
the raven king         nora sakavic         reading
the king's men         nora sakavic         want to read

step 1 — publish the sheet

open your spreadsheet, then go to file → share → publish to web. a dialog will ask what you want to publish, either the entire document or a single sheet/tab, pick whichever matches your data, then hit publish. this doesn't change who can edit your sheet, only who can read it, and it makes that reading possible without anyone needing a google account or an invite.

step 2 — find your sheet id and gid

every google sheet has two ids you'll need in the next step: one that identifies the whole spreadsheet document, and one that identifies the specific tab inside it you want to read from. both are sitting right there in the address bar while you have that sheet open, you don't need to dig through any settings to find them:

https://docs.google.com/spreadsheets/d/1AbXWk9zR3mPQeYcVnFhTgKdLsJ82NxUq4CivBoO7ta0/edit#gid=874512033
                                       └──────────────── sheet id ────────────────┘         └── gid ──┘

• sheet id: everything between /d/ and the next /. in the url above, that's 1AbXWk9zR3mPQeYcVnFhTgKdLsJ82NxUq4CivBoO7ta0, a long random string google generates once when the spreadsheet is first created and never changes again
• gid: the number after #gid= at the very end of the url, once you have that specific tab open and active. in the url above, that's 874512033. if you're on the very first tab of the spreadsheet, you might not see a #gid= in the url at all, which just means the gid is 0

copy both of these out somewhere, a notes app, a comment in your code, wherever, we'll reuse them together in the next step.

step 3 — build the json url

google sheets has a built in endpoint that returns any published sheet as json, it was originally built for google's own charting tools, but nothing stops you from using it yourself, and it needs zero setup on your end. here's the general pattern, with the two ids you just found standing in as placeholders:

https://docs.google.com/spreadsheets/d/YOUR_SHEET_ID/gviz/tq?tqx=out:json&gid=YOUR_TAB_GID

and here's that exact same url with our example ids dropped in where the placeholders were, this is the actual, working link you'd paste into a browser tab or into fetch() to test it:

https://docs.google.com/spreadsheets/d/1AbXWk9zR3mPQeYcVnFhTgKdLsJ82NxUq4CivBoO7ta0/gviz/tq?tqx=out:json&gid=874512033

if you open that link directly in your browser to check it, you won't see clean, ready to use json, you'll see a wall of text like this instead:

/*O_o*/
google.visualization.Query.setResponse({"version":"0.6","status":"ok","table":{"cols":[
{"id":"A","label":"title","type":"string"},
{"id":"B","label":"author","type":"string"},
{"id":"C","label":"status","type":"string"}],
"rows":[{"c":[{"v":"the cruel prince"},{"v":"holly black"},{"v":"finished"}]},
{"c":[{"v":"the foxhole court"},{"v":"nora sakavic"},{"v":"finished"}]}]}});

that's completely normal, not an error, it's the "quirk" step 4 deals with. the real json is genuinely in there, it's just buried inside a call to a javascript function named setResponse, so you can't hand this text straight to JSON.parse() and expect it to work.

step 4 — unwrap and parse the response

here's the fetch code that requests that url, strips away the google.visualization.Query.setResponse(...) wrapper, and pulls the actual rows out of what's left underneath. this version uses our example url and ids directly, filled in and ready to run as is, so you can see exactly what a finished, working version looks like before adapting it to your own sheet:

fetch("https://docs.google.com/spreadsheets/d/1AbXWk9zR3mPQeYcVnFhTgKdLsJ82NxUq4CivBoO7ta0/gviz/tq?tqx=out:json&gid=874512033")
  .then(response => response.text())
  .then(text => {
    // the raw text looks like: /*O_o*/\ngoogle.visualization.Query.setResponse({ ...json... });
    // slicing off the first 47 characters and the last 2 leaves just the { ...json... } part
    const json = JSON.parse(text.substring(47).slice(0, -2));

    const rows = json.table.rows.map(row =>
      row.c.map(cell => (cell ? cell.v : ""))
    );

    rows.forEach(row => {
      console.log(row);
      // first time through: ["the cruel prince", "holly black", "finished"]
      // second time through: ["the foxhole court", "nora sakavic", "finished"]
    });
  })
  .catch(error => console.error("couldn't load the book log:", error));

walking through why each line is written the way it is:

• response.text(): we pull the raw text instead of using response.json() like the generic example at the top of this tutorial, since (as you saw above) this response isn't valid json on its own yet, it's wrapped in that setResponse(...) call. asking for it as json would just throw an error
• text.substring(47).slice(0, -2): chops the fixed /*O_o*/\ngoogle.visualization.Query.setResponse( text off the front, and the closing ); off the back. that opening text is always exactly 47 characters long and never changes between sheets, so this same line works for any published sheet, not just this example, without needing to be rewritten
• json.table.rows: once parsed, this is an array with one entry per spreadsheet row. each row's actual cell values live inside its own .c array, one entry per column, and each of those cell objects has a .v property holding the real value, which is why the code above maps twice, once over the rows, and once over each row's cells inside .c
• the cell ? cell.v : "" part matters more than it looks: google leaves a cell as null instead of an object when it's genuinely empty, so reading .v straight off it would crash on any blank cell. this quietly falls back to an empty string instead

turning each row into a labeled object instead of a plain array, so you can write book.title instead of the harder to read book[0], just takes one more small step, matching each cell up with its column name using the header row you already have access to:

const headers = json.table.cols.map(col => col.label); // ["title", "author", "status"]

const books = json.table.rows.map(row => {
  const book = {};
  row.c.forEach((cell, i) => {
    book[headers[i]] = cell ? cell.v : "";
  });
  return book;
});

console.log(books[0]);
// { title: "the cruel prince", author: "holly black", status: "finished" }

the headers line reads the column labels straight from the sheet's own metadata, so if you ever add or rename a column, this code doesn't need to change at all, it just picks up whatever labels are there. inside the .map(), row.c.forEach((cell, i) => ...) walks through each cell in a row alongside its index i, and uses that same index to look up the matching header name in headers[i], pairing them together into one object per book.

google apps script

use this when you need more control than a plain published sheet gives you, a custom json shape, combining multiple tabs into one response, filtering out draft or private rows before they ever leave the sheet, or eventually letting your page write data back, like a request form. apps script is google's built in scripting layer for its own apps, and here we're using it to turn your spreadsheet into your own tiny api that you fully control.

step 1 — open the script editor

in your spreadsheet, go to extensions → apps script. this opens a separate code editor tied specifically to that spreadsheet, running entirely on google's servers rather than in anyone's browser, which is exactly why it's able to safely do things a plain static page never could.

step 2 — write a doGet function

apps script recognizes a function named exactly doGet as the entry point for any web request that hits your deployed script, you don't call this function yourself, google calls it automatically whenever someone visits your script's url. inside it, we read the sheet and hand back its rows as json:

function doGet() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Books");
  const values = sheet.getDataRange().getValues();

  const headers = values[0];
  const rows = values.slice(1);

  const data = rows.map(row => {
    const entry = {};
    headers.forEach((header, i) => {
      entry[header] = row[i];
    });
    return entry;
  });

  return ContentService
    .createTextOutput(JSON.stringify(data))
    .setMimeType(ContentService.MimeType.JSON);
}

going through this line by line:

• SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Books"): grabs the spreadsheet this script is attached to, then the specific tab named "Books" inside it. if your tab has a different name, this is the one string in the whole function you'd need to change
• getDataRange().getValues(): reads every filled in cell as a plain 2d array, one inner array per row, exactly matching what you'd see if you selected the whole sheet and looked at the raw values, no formatting, no formulas, just the values
• headers and rows: since values[0] is always the very first row, we treat it as the column names, and everything from values[1] onward, grabbed with .slice(1), as the actual data. this gives you the same shape you'd expect from a real json api, a set of named fields, not just bare arrays
• .map(row => {...}): turns each plain array row into a labeled object, so instead of ["the cruel prince", "holly black", "finished"] you get { title: "the cruel prince", author: "holly black", status: "finished" }, which is far easier to work with once it reaches your page
• ContentService.createTextOutput(...): this is what actually makes the function respond like an api endpoint instead of just running silently in the background with no visible result. .setMimeType(ContentService.MimeType.JSON) tells whatever requests this url that the response body should be treated as json, not plain text or html

step 3 — deploy it as a web app

with the function written and saved, click deploy → new deployment. a panel opens asking what type of deployment to create, choose web app from the type dropdown. under "who has access," choose anyone, otherwise your own page won't be allowed to fetch from it either, then click deploy. google will show you a url that looks like this:

https://script.google.com/macros/s/AKfycbxJ3mZQ9vTnRkLpXeYw2CdGhUoI4bFa8sVrNcMz1QpWtEz/exec
                                   └────────────────── deployment id ─────────────────┘

the whole url is your api endpoint, you'll fetch this exact address from your site later. the long string sitting between /s/ and /exec is what we've been calling YOUR_DEPLOYMENT_ID in the placeholder pattern above, but you don't need to extract it separately, copy the entire url as one piece.

step 4 — fetch it like any other api

with our example deployment url, the fetch code looks like this, filled in and ready to run as is, the exact same shape as the generic pattern from the idea section, just pointed at your new endpoint:

fetch("https://script.google.com/macros/s/AKfycbxJ3mZQ9vTnRkLpXeYw2CdGhUoI4bFa8sVrNcMz1QpWtEz/exec")
  .then(response => response.json())
  .then(data => {
    data.forEach(entry => {
      console.log(entry.title, entry.author, entry.status);
    });
  })
  .catch(error => console.error("couldn't load the book log:", error));

and here's exactly what that data variable contains once it arrives, straight from the doGet function in step 2, no wrapper text to strip this time, since unlike the plain published sheet method, you control the entire response format yourself:

[
  { "title": "the cruel prince", "author": "holly black", "status": "finished" },
  { "title": "the foxhole court", "author": "nora sakavic", "status": "finished" },
  { "title": "the raven king", "author": "nora sakavic", "status": "reading" },
  { "title": "the king's men", "author": "nora sakavic", "status": "want to read" }
]

that's the version of your data source you get to design yourself, exactly the shape you asked for back in step 2's .map(), nothing extra tacked on by google, nothing to unwrap on your end.

notion

notion databases work a little differently from the two options above, and it's worth understanding why before diving into the code. say you're keeping a book log in notion instead of a spreadsheet, with columns for the book's name (the title), author, and reading status, the same three fields we've used throughout this tutorial, just living in a different tool.

the fix is to put something in between your site and notion, a small server that holds the secret token for you, and only ever hands your page back the finished, already public safe json. you don't need to rent or set up a whole separate server for this, the same apps script web app pattern from the last section can do exactly that job for free.

step 1 — create a notion integration

in notion, go to notion.so/my-integrations and create a new integration, this gives you a secret token tied specifically to it, copy that token somewhere safe. then open your book log database inside notion itself, click ••• → connections in the top right, and connect that same integration to it, otherwise the token will exist but won't actually be able to see or read that database at all.

step 2 — find your database id

before writing any code, you need one more piece: the id of the specific database you want to read from, notion doesn't show this anywhere in the interface as a labeled field, it's tucked inside the page's own url. open your book log database as a full page (not as an inline block inside another page, the address bar needs to show the database itself), and look at the url up there:

https://www.notion.so/myworkspace/1a2b3c4d5e6f7a8b9c0d1e2f3a4b5c6d?v=9f8e7d6c5b4a3f2e1d0c9b8a7f6e5d4c
                                  └───────── database id ─────────┘   └──── view id, not needed ────┘

• database id: the 32 character string that comes right after your workspace name and the last /, and right before the ?v=. notion writes it without dashes when it's sitting in a url like this, even though notion's own interface sometimes displays ids elsewhere with dashes inserted, like 1a2b3c4d-5e6f-7a8b-9c0d-1e2f3a4b5c6d. both forms point to the same database, the api accepts either, so you don't need to add or remove the dashes yourself
• view id: the string after ?v=, if there is one. that's just which saved view (table, board, calendar) you currently have open, it has nothing to do with the api and isn't needed for the request in the next step

if your database is nested inside another notion page instead of living on its own, open it by clicking to expand it into a full page first (the little arrow icon on hover, or "open as page"), the url won't show a proper database id while it's only sitting inline inside another page.

step 3 — query it from apps script

back in an apps script project (the same kind of project as the sheets section, either a new one or the same one), write a doGet that calls notion's api on your behalf, using the secret token safely on the server side where visitors can never see it:

function doGet() {
  const token = "YOUR_NOTION_SECRET_TOKEN";
  const databaseId = "YOUR_DATABASE_ID";

  const response = UrlFetchApp.fetch(
    `https://api.notion.com/v1/databases/${databaseId}/query`,
    {
      method: "post",
      headers: {
        "Authorization": `Bearer ${token}`,
        "Notion-Version": "2022-06-28",
        "Content-Type": "application/json"
      }
    }
  );

  const results = JSON.parse(response.getContentText()).results;

  const data = results.map(page => ({
    title: page.properties.Name.title[0]?.plain_text || "",
    author: page.properties.Author.rich_text[0]?.plain_text || "",
    status: page.properties.Status.select?.name || ""
  }));

  return ContentService
    .createTextOutput(JSON.stringify(data))
    .setMimeType(ContentService.MimeType.JSON);
}

the parts worth understanding here:

• token and databaseId: paste your integration's secret token from step 1, and the database id you just found in step 2, here
• UrlFetchApp.fetch(...): apps script's own server side version of fetch(). it runs entirely on google's servers, never in the visitor's browser, so the secret token sitting in the headers here never gets exposed to anyone viewing your site's source code, unlike if you tried to call notion straight from your page's own javascript
• Authorization: Bearer ${token} and Notion-Version: two headers notion's api requires on every request, the first proves who you are, the second tells notion which version of its api shape to respond with, so your code doesn't silently break if notion changes their api later
• page.properties.Name.title[0]?.plain_text: notion's api returns each column, called a "property," in a fairly deep, type specific shape depending on whether it's a title, plain text, select, checkbox, and so on. this particular line is reading a property named "Name" whose type is "title," which holds the book's title in this database, and the ?. quietly avoids a crash if that field happens to be empty
• page.properties.Author.rich_text[0]?.plain_text: the same idea as above, but for a plain text column named "Author," notion nests those under rich_text instead of title, since it's a different property type under the hood
• the .map(...) step here is doing double duty, it's both reshaping notion's deeply nested format into the same flat { title, author, status } shape we've used all tutorial, and deciding exactly which properties get exposed publicly. anything you don't explicitly pull out here, like private notes or a rating column, simply never leaves the proxy and never reaches your page

step 4 — deploy and fetch, same as before

deploy this project as a web app exactly like in the google apps script section above, same "who has access: anyone" setting, and fetch its /exec url from your page the same way you did there too:

fetch("https://script.google.com/macros/s/YOUR_DEPLOYMENT_ID/exec")
  .then(response => response.json())
  .then(data => {
    data.forEach(entry => {
      console.log(entry.title, entry.author, entry.status);
    });
  })
  .catch(error => console.error("couldn't load the book log:", error));

notice this fetch code is identical in shape to the sheets via apps script example from the previous section, that's the whole point of routing everything through your own endpoint. your page only ever needs to know one simple pattern, no matter which service is actually holding the data behind it, so swapping notion for something else later wouldn't touch this part of your code at all.

result

you now have three ways to pull external content into a static page, reading a published sheet directly, building your own json api on top of a sheet with apps script, and safely proxying a notion database through that same apps script pattern. all three end the same way, a fetch() call and a loop, which means once you're comfortable with one, adding another data source later is mostly just swapping out a url and adjusting the shape of the objects you map over.