sheet2api logo Sign in

Webinar listing using a Google Sheet

Google Sheet spreadsheet as a database.

In this tutorial we will create a Webinars listing page using Google Sheet spreadsheet as a CMS. When we add a new item to the spreadsheet this will display on the Webinars listing page.

For this example we're going to start with Bootstrap example template called "Album".

We'll make some minor changes to this to make it more suited to listing Webinars.

Here's our modified version of that template https://github.com/odwyersoftware/sheet2api-use-cases/blob/master/examples/webinars-listing/base-static-template.html

As you can see below it's a simple page with one placeholder webinar listed on it.

Inital static Webinars page template

Now, let's get started hooking this up to a spreadsheet to make it dynamic.

Here's our Google Sheet spreadsheet we're going to use to populate our webpage: https://docs.google.com/spreadsheets/d/1rtazbhcYh9PBFFnMGoIl-TJ2zuFZJ_tu5_niAuG8kBQ/edit#gid=0

A B C D E
1 title date time image
2 Soca Tworkout Fitness: Whine Online w/Bea 12 August 2020 7PM EST https://i.imgur.com/Hkv5EyW.png
3 Azure Fundamentals (AZ-900) Free Training 10 August 2020 5PM EST https://i.imgur.com/VKvz2vz.png
3 Typographics Conference 2020 Online 5 August 2020 10AM EST https://i.imgur.com/HQmkbPC.png
4
Sheet1

Now Create your Spreadsheet API using the above spreadsheet share link.

Create REST API from a Google or Excel Sheet

Then copy the link to your Spreadsheet API (it should look something like https://sheet2api.com/v1/FgI6zV8qT121/use-case-webinars).

Now we'll use this to make our page dynamic. Here's the area of the template we're now going to edit to make our page dynamic:

<div class="row">
    <div class="col-md-4">
        <!-- webinar item start -->
        <div class="card mb-4 box-shadow">
            <img class="card-img-top" src="data:image/svg+xml;charset=UTF-8,%3Csvg%20width%3D%223...">
            <div class="card-body">
                <p class="card-text">WEBINAR TITLE</p>
                <div class="d-flex justify-content-between align-items-center">
                    <div class="btn-group mr-2">
                        <button type="button" class="btn btn-sm btn-outline-secondary">Register</button>
                        <button type="button" class="btn btn-sm btn-outline-secondary">Share</button>
                    </div>
                    <small class="text-muted">DATE, TIME</small>
                </div>
            </div>
        </div>
        <!-- webinar item end -->
    </div>
</div>

The parts we need to fill out are the image src, the data-spreadsheet-api along with the text content WEBINAR TITLE, DATE, TIME.

We want to loop over all the rows in our spreadsheet and display one of the above boxes on the page for it. Here's the code to do that:

<div class="row" data-spreadsheet-api="https://sheet2api.com/v1/FgI6zV8qT121/use-case-webinars">
    <div class="col-md-4">
        <!-- webinar item start -->
        <div class="card mb-4 box-shadow">
            <img class="card-img-top" src="{{image}}">
            <div class="card-body">
                <p class="card-text">{{title}}</p>
                <div class="d-flex justify-content-between align-items-center">
                    <div class="btn-group mr-2">
                        <button type="button" class="btn btn-sm btn-outline-secondary">Register</button>
                        <button type="button" class="btn btn-sm btn-outline-secondary">Share</button>
                    </div>
                    <small class="text-muted">{{date}}, {{time}}</small>
                </div>
            </div>
        </div>
        <!-- webinar item end -->
    </div>
</div>

Next, import the sheet2api script into your template just before the end of the "</body>" tag, like so:

<script src="https://sheet2api.com/v1/template.js"></script>
</body>

Save your file and reload your web page to see the result:

Google Sheet powered Webinars listing page

Next optional steps you could take would be to add dynamic links to the buttons page. Try this yourself by adding new columns to the spreadsheet "register_link" and "share_link".

Securing our API

Now we have a dynamic page we're going to tweak some settings in our Spreadsheet API to secure things.

Go to your account page, located your Spreadsheet API from the list and click "Configure API".

Your Spreadsheets list in sheet2api account section

On the next page, under "API Permissions", untick everything except "Read", and click "Save".
This will ensure data may only be read from our Spreadsheet API and stops anyone from updating or deleting it!

Webinars Spreadsheet API Configuration page

The end. You've now built a dynamic, secure web page using a Google Sheet spreadsheet as a CMS.

Useful Links


It's as easy as that 🎉.

If you need help, please feel free to send a message via the contact page.

Start using sheet2api now

Be up & running in seconds.