Building a TODO App with Google Apps Script, gas-db, and HTML

I previously introduced the library I created here, but thinking that it might be hard to understand its use cases, I have created a sample TODO app.

This sample is an example of building a simple TODO list app by combining the following technologies:

• gas-db (A library to treat Google Sheets as a database)
• Google Apps Script Web App functionality
• HTML returned from Apps Script (Client-side JS)

External hosting like GitHub Pages is not required, as everything is provided on GAS.

Also, the code introduced here is included in the repository, so if you are interested in the complete code, please take a look here.

↓↓↓ Sample Code
https://github.com/shunta-furukawa/gas-db/tree/master/sample/todos

↓↓↓ Working App
(It might stop working because the URL changes when redeployed)
https://script.google.com/macros/s/AKfycbzkIlQjRkUMcIqULqbOdTqwgy0j7MxM_-P9nITMb7CH/dev

↓↓ Spreadsheet (Anyone can edit)
https://docs.google.com/spreadsheets/d/1-QxJN6sEoIEwrhhgLZS_eEAzJSCft3lCqjdgL_cuBRw/edit?gid=0#gid=0

---

Overview

1. Google Spreadsheet – Acts as the "database"
2. code.js – Server-side code (GAS)
   • Performs CRUD operations on the spreadsheet using gas-db
   • Returns index.html via the entry point (doGet(e))
   • Defines functions for fetching, creating, updating, and deleting TODO list items
3. index.html – Client-side (UI part)
   • Returned by HtmlService
   • Calls server-side functions using google.script.run

Steps

1. Preparing the Spreadsheet

1. Create a new Google Spreadsheet.
2. Set the sheet name to Todos (to match the code).
3. Add header rows like id, title, completed, etc.
4. Keep track of the Spreadsheet ID (the string between /d/ and /edit in the URL).

2. Creating a GAS Project

1. Access script.google.com and create a New Project.
   • Alternatively, you can go to "Extensions" > "Apps Script" from the spreadsheet screen.
2. The project name and other settings are optional.

3. Adding the Code

1. Import gas-db as a library. See here for details.
2. Copy the content of code.js and paste it into your GAS project (e.g., set the filename to Code.gs).

/**
 * Example of a simple API server for TODO management
 * Performs CRUD operations on a spreadsheet using gas-db
 */

function debug() {
    console.log(getDb())
}

/**
 * 1. Get the spreadsheet and sheet
 *   Retrieve according to gas-db syntax
 */
function getDb() {
    // Please replace with your actual Spreadsheet ID and sheet name
    return new gasdb.Spreadsheet()
        .from("YOUR_SPREADSHEET_ID")
        .at("Todos");
}

/**
 * Main entry point
 * Returns an HTML file (index.html) to create a Web App
 */
function doGet(e) {
    return HtmlService.createTemplateFromFile('index') // Load template
        .evaluate()
        // (Optional) Settings like expanding the maximum width
        .setTitle("TODO App")
}

/**
 * Fetches all TODOs and returns them as an array
 */
function getTodos() {
    const db = getDb();
    // Get all [{id, title, completed}]
    const rows = db.findAll();
    // Example of converting types like boolean for proper handling
    return rows.map(r => ({
        id: r.id,
        title: r.title,
        completed: (r.completed === true || r.completed === "true")
    }));
}

/**
 * Create a new TODO
 */
function createTodo(title) {
    const db = getDb();
    // Use timestamp for a simple ID
    const newTodo = {
        id: new Date().getTime(),
        title: title,
        completed: false
    };
    db.insert(newTodo);
}

/**
 * Toggle the completion flag of a TODO
 */
function toggleTodo(id) {
    const db = getDb();
    // Search for one existing item
    const existing = db.pick({ id });
    if (!existing) return;
    // Update with the toggled value
    db.update({ completed: !existing.completed }, { id: existing.id });
}

/**
 * Delete a TODO
 */
function deleteTodo(id) {
    const db = getDb();
    db.delete({ id: Number(id) });
}

2. Add an index.html file to the same project and copy the content of the file with the same name from the repository.

<!DOCTYPE html>
<html lang="ja">
<head>
  <meta charset="UTF-8" />
  <title>My TODO App</title>
  <!-- Load Onsen UI CSS and FontAwesome from CDN -->
  <link rel="stylesheet" href="https://unpkg.com/onsenui/css/onsenui.css">
  <link rel="stylesheet" href="https://unpkg.com/onsenui/css/onsen-css-components.min.css">
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
  <style>
    .completed {
      text-decoration: line-through;
      color: #999;
    }
  </style>
</head>
<body>
  <ons-page>
    <ons-toolbar>
      <div class="center">TODO List (GAS Web App)</div>
    </ons-toolbar>
    <div style="padding: 10px;">
      <ons-card>
        <div class="title">New Task</div>
        <div class="content">
          <form id="todoForm">
            <ons-input id="todoInput" placeholder="New task..." float required></ons-input>
            <div style="text-align: right; margin-top: 10px;">
              <ons-button type="submit" onclick="createTodo()">Add</ons-button>
            </div>
          </form>
        </div>
      </ons-card>
      <ons-list id="todoList"></ons-list>
    </div>
  </ons-page>

  <!-- Load Onsen UI JavaScript and Vue from CDN -->
  <script src="https://unpkg.com/vue@2.6.14/dist/vue.min.js"></script>
  <script src="https://unpkg.com/onsenui/js/onsenui.min.js"></script>
  <script>
    document.addEventListener('init', function() {
      fetchTodos();
    });

    function fetchTodos() {
      google.script.run.withSuccessHandler(renderTodos).getTodos();
    }

    function renderTodos(todos) {
      const listEl = document.getElementById('todoList');
      listEl.innerHTML = '';

      todos.forEach(todo => {
        const listItem = document.createElement('ons-list-item');
        listItem.setAttribute('modifier', 'longdivider');
        listItem.innerHTML = `
          <div class="center ${todo.completed ? 'completed' : ''}">${todo.title}</div>
          <div class="right">
            <ons-button modifier="quiet" onclick="toggleTodo(${todo.id})">
              <ons-icon icon="fa-${todo.completed ? 'undo' : 'check'}"></ons-icon>
            </ons-button>
            <ons-button modifier="quiet" onclick="removeTodo(${todo.id})">
              <ons-icon icon="fa-trash"></ons-icon>
            </ons-button>
          </div>
        `;
        listEl.appendChild(listItem);
      });
    }

    function createTodo() {
      const input = document.getElementById('todoInput');
      const title = input.value.trim();
      if (!title) return;

      google.script.run.withSuccessHandler(() => {
        fetchTodos();
      }).createTodo(title);
      input.value = '';
    };

    function toggleTodo(id) {
      google.script.run.withSuccessHandler(() => {
        fetchTodos();
      }).toggleTodo(id);
    }

    function removeTodo(id) {
      google.script.run.withSuccessHandler(() => {
        fetchTodos();
      }).deleteTodo(id);
    }
  </script>
</body>
</html>

3. Replace "YOUR_SPREADSHEET_ID" in code.js with the spreadsheet ID you noted earlier.

4. Deploy as a Web App

1. Click "Deploy" > "New deployment" in the GAS editor menu.
2. Select "Web app" as the type.
3. Set "Execute as" to "Me" and "Who has access" to "Anyone" or choose according to your needs.
   • If you want to make it accessible without authentication, select "Anyone".
4. Copy the Web App URL displayed after clicking "Deploy".

5. Verification

1. Open the Web App URL in your browser.
2. GAS will return index.html and the TODO app screen will be displayed.
3. When you add a new task, a row is automatically added to the spreadsheet.
4. Check if the task completion and delete buttons work correctly.

---

Mechanisms

• doGet(e)
  Returns index.html using HtmlService. This is the entry point of the Web App.
• index.html
  Calls server-side getTodos() via google.script.run when the page loads to display TODO data fetched from the spreadsheet.
  Calls createTodo(), toggleTodo(), and deleteTodo() as server-side functions via button operations, then re-fetches after processing.
• Server-side (code.js)
  • getDb() returns the gas-db Sheet instance to operate on the Todos sheet.
  • Manages TODO data through various CRUD operations (findAll / insert / update / delete).
    You can flexibly adjust access rights to the spreadsheet, either executing with the permissions of the user running the script or using "Execute as Me" + "Anyone" if anonymous access is permitted.

---

FAQ

Q. Why not host with GitHub Pages?

• If the frontend and backend are on different domains, CORS settings are required due to the browser's Same-Origin Policy, and standard Apps Script does not easily support setting Access-Control-Allow-Origin.
• By serving HTML as a GAS Web App, everything is completed on the same domain, allowing implementation without worrying about CORS.

Q. How can I use other features of gas-db?

• This sample uses only findAll, find, insert, update, and delete.
• It is possible to add spreadsheet columns and handle data other than title or completed.

Q. How can I debug?

• You can output logs on the server side (GAS) using Logger.log(). You can check them from the editor's "View" > "Logs" or in the "Executions" > "Recent executions" tab.
• For JavaScript on the client (index.html) side, please use console.log in your browser's developer tools.

Related Links

• gas-db Repository (GitHub)
• Google Apps Script Official Documentation
• HtmlService / Web Apps (Apps Script)

---

You can create apps with a spreadsheet as the backend with just a small amount of code!
Please try it out!!