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.
** Please let me know if you use this lesson / these techniques by either:
Adding a comment on my blog post about this project (link coming)
Adding a comment on my Reddit post about this project (link coming)