Creating an Editable Webpage With Google Spreadsheets and Tabletop.js

Say goodbye to endless customer content modification requests! This article will demonstrate how to create editable web pages using Google Sheets and Tabletop.js, allowing customers to modify content by themselves, saving you valuable time.

If you are building your website using static files (HTML, CSS, and JavaScript) instead of CMS (such as WordPress), you need other solutions to edit the content without directly modifying the file.

Tabletop.js allows us to use Google Sheets as a data store to easily access spreadsheet data through JavaScript. It provides Google Sheets data in JSON format and can be used in applications just like extracting data from any other API. This article will show you how to add data to a spreadsheet and set up Tabletop so that it can extract data from a data source to HTML. Let's go straight into the code!

This tutorial is based on a real project that was originally used to learn Tabletop.js. I suggest that developers should practice it by themselves when learning any new technology, even if they have read the tutorial.

We will use the demo project I created, including its source code and spreadsheet. First, you need a Google account to access the spreadsheet.

Create a new spreadsheet and enter your own value in the column, just like my example. The first cell of each column is the reference name that will be used later in JavaScript, and the second cell is the actual content of the website. For example, "header" is the reference name, and "Please edit me!" is the actual content of the first column.

Next, click "File" → "Publish to Web Page" in the menu bar to publish the data to the network.

The system will provide a link, but it is useless to us and can be ignored. Importantly, spreadsheets (and their data) are now publicly accessible so that we can get it for our application.

However, we need a link. Click the green "Share" button in the upper right corner of the page, and a modal window will pop up, providing a shareable link to the spreadsheet and allowing us to set permissions. Let's get the link and set permissions so that anyone with the link can view the spreadsheet. This way, others don't accidentally edit the data.

Now, initialize Tabletop in the project. Let's link to the compressed file they host. Similarly, we can copy the original compressed code, put it into our own script file and host it ourselves.

Here is the document file linked to Tabletop CDN, and the code taken from the document:

 var publicSpreadsheetUrl = 'https://docs.google.com/spreadsheets/d/1sbyMINQHPsJctjAtMW0lCfLrcpMqoGMOJj6AN-sNQrc/pubhtml';

  function init() {
    Tabletop.init( {
      key: publicSpreadsheetUrl,
      callback: showInfo,
      simpleSheet: true 
    } )
  }

  function showInfo(data, tabletop) {
    console.log(data); // Use console.log to view the data structure data.forEach(function(row) {
      // Dynamically set innerHTML according to the data structure
      document.getElementById('header').innerHTML = row.header || '';
      document.getElementById('header2').innerHTML = row.header2 || '';
      document.getElementById('body').innerHTML = row.body || '';
      // ... other elements});
  }

  window.addEventListener('DOMContentLoaded', init)

Replace the publicSpreadsheetUrl variable in the code with a shareable link to the spreadsheet.

Now enter the fun part. Let's assign a unique ID to the HTML element and leave it blank. Then, in the showInfo function, we will use the forEach() method to iterate through each spreadsheet column and associate it with the corresponding ID.innerHTML method, which loads the spreadsheet's data into the HTML tag via the ID.

Here is a piece of HTML code from my demo project that shows an empty tag:

<div>
    <h3 id="header"></h3>
    <p id="body"></p>
  </div>
  <div>
    <div><img src="/static/imghw/default1.png" data-src="https://img.php.cn/upload/article/000/000/000/174433687496119.png" class="lazy" alt="Creating an Editable Webpage With Google Spreadsheets and Tabletop.js"></div>
    <div>
      <div>
        <h4 id="header2"></h4>
        <p id="body2"></p>
      </div>
      <div>
        <h4 id="header3"></h4>
        <p id="body3"></p>
      </div>
      <div>
        <h4 id="header4"></h4>
        <p id="body4"></p>
      </div>
    </div>
  </div>

Note that I added id attribute to each HTML element. This allows us to precisely locate and update them through JavaScript's document.getElementById() method. The original code directly uses variable names, which assumes that the variable names are exactly the same as the spreadsheet column names and that all column names have been declared as JavaScript variables, which is not flexible and robust enough.

Now we can change the content on the website in real time by editing the content in the spreadsheet.

The above is the detailed content of Creating an Editable Webpage With Google Spreadsheets and Tabletop.js. For more information, please follow other related articles on the PHP Chinese website!