Lessons by Dr. Wesley Fryer (wesfryer.com)

These are instructions for creating a "Web Searchable Spreadsheet" with Google Sheets and Google Sites. This is an advanced lesson for my middle school web design course and students. *

If you use this lesson please let me know! **

Create a Dynamic Searchable Website with Google Sheets

This guide will help you build a dynamic, searchable website using Google Sheets and Google Sites.

Example files:

Check out the example I've created for Resources on PodCampCLT. (Podcasting tools)
That Google Site page uses an embed of this link, created with a script on this Google Sheet.
The ChatGPT conversation used to create this searchable interface is available. (More AI tips are available on wfryer.me/tips)

Step 1: Create and Populate Your Google Sheet

Go to Google Sheets and create a new spreadsheet.

Add these column headers in the first row:

Name
Categories
Short Description
URL

Add your data in the rows below, filling in:

Name: The name of the item.
Categories: A single category (e.g., "Tools," "Books").
Short Description: A brief summary (1–2 sentences).
URL: A direct link to the item.

Share the sheet:

Click Share in the top right.
Set sharing permissions to Anyone with the link can view.

Step 2: Create a Google Apps Script Web App

Open Apps Script:
- In your Google Sheet, go to Extensions > Apps Script.
Add the Code:
- Replace any existing code with the following:

3. Deploy the App:

Click Deploy > New Deployment.
Choose Web App.
Set:
- Description: “Dynamic Search Web App”
- Execute as: Me
- Who has access: Anyone.
Click Deploy, and copy the web app URL.

Step 3: Create the HTML Interface

Open Apps Script.
Add a new HTML file:
- Click the + icon and choose HTML.
- Name it Index and paste this code:

Replace YOUR_WEB_APP_URL with the URL you deployed in Step 2.

Step 4: Embed in Google Sites

Deploy the script as a Web App again, ensuring permissions are set to Anyone.
Use the web app URL in Google Sites:
- Click Embed.
- Paste the URL.
- Adjust the height to 600px or more.

* Depending on how your school Google Domain is configured for SHARING outside your organization, these steps may not work. I recommend first, as a teacher, replicating these steps with your own personal Google account, then replicating on a school account. Also keep in mind sharing settings may be configured differently for student education domain accounts compared to teacher accounts. Testing for your specific domain / context is therefore important before using this in a formal lesson.