Webinar listing using an Excel Sheet

In this tutorial we will create a Webinars listing page using Excel Online 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.

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

Here's our Excel Online spreadsheet we're going to use to populate our webpage: https://1drv.ms/x/s!AnONTscVSMXNgUE6CaoXHvm7Iibj?e=1qkeKd

	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

Now Create your Spreadsheet API using the link to that spreadsheet.

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:

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".

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!

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

Useful Links

• Live demo webpage of this Webinars listing web page
• Github source code for the demo webpage