Contents:
Over the last few years I've found it genuinely life-changing to listen to great sermons from various podcasts. When I took on a new role at church, I wanted to find an easy and persistent way to share sermons from around the web with other people - our congregation, a mentoree and friends. I couldn't find anything that suited my needs exactly, so I decided to create my own.
It allows you to fill your spreadsheet with details about MP3 files you've found on the web (titles, descriptions, links, topic tags, etc.) and turns them into a complete website (of static HTML files) and a set of podcast (XML) feeds which you can upload via FTP to your web host to create a public website like this one (noting that you also need to upload the 'components' folder - see The Source for more information). The website includes four different types of pages:
You can also:
Because I know Visual Basic and didn't have time to learn the finer points of Python to make a web application. The downside of using Excel is that only one person can maintain it, but I've realised that this is a useful constraint for two reasons:
Make no mistakes about it, this code is a dodgy hack. I'm a self-taught programmer who doesn't comment as much as he should. Worse, I haven't had the time to optimise the code to the extent I wanted to - I don't reuse code as much as I should, and for speed at runtime I probably should have sucked the entire worksheet into an array rather than keep calling worksheet references. But I didn't. It works, is reasonably quick and I don't have time to optimise it further.
This source is licensed under a GN licence - Go Nuts. It's yours - do with it what you will.
This is the Excel spreadsheet (which is the latest copy of my spreadsheet, containing all of the talks that appear on my podcast website.
You also need to download this zipped folder, unzip it and FTP it to the root directory of your podcasting system (i.e. put the 'Components' folder in the same directory as the files generated by the Excel spreadsheet). This zipped folder contains:
Other components that add additional functionality to the site are as follows:
I'm no fan of Google and their desire to track everyone on the web so I haven't used their search engine in my implementation (although you could easily modify the code to do so). I've used the Sphider search engine which is a free download and can be installed on your site.
Note, however, that Sphider doesn't reindex itself - you need to set a Cron job (via cPanel at your webhost) to schedule reindexing. The command that works on my host is as follows:
cd /home/[hosting account name]/public_html/resources/search/admin; php spider.php -all
Note, however, that I changed the Sphider directory name to 'search' - if you used the default Sphider parameters the correct command would be:
cd /home/[hosting account name]/public_html/resources/sphider/admin; php spider.php -all
Of course, the file path for your host will be different to mine and can be discovered using tools in your cPanel, such as File Manager.
If that Cron command doesn't work for you, there's a discussion on the Sphider forums which might be helpful to you.
To turn off the search functionality on web pages (and remove the search bar at the top of each page), all you need to do is go to the Excel spreadsheet and delete the contents of the cell below the cell which says:
Full or relative URI to PHP redirect file for search function (to remove search option, leave blank).
To help people share notes on sermons, you can instal a wiki system from MediaWiki on your webserver and, by setting the right path in the Excel spreadsheet (under the 'Wiki location' cell), automatically create a link to a wiki page for every talk on your website (like this page). A handy feature of the wiki is that even if a wiki article doesn't exist for a particular talk, it will allow users to create a new page at the link specified by the Notes link on the podcast website.
Note that evil spambots will quickly find your Wiki and fill it with thousands of fake wiki articles if you allow anyone to create a web page without a username and password. The simplest solution I could come up with was to lock down the wiki and make a username and password (not your administrator username and password!) visible to humans using the site in the site image (see this page, noting the image at top left). To lock down the wiki (to prevent people from creating an article without a username and password, and to prevent anyone from creating a new username and password, which the spambots do), add the following code (and comments) to the end of your LocalSettings.php file in the Wiki root directory:
# http://www.mediawiki.org/wiki/Manual:Preventing_access#Restrict_account_creation
# Prevent new user registrations except by sysops
$wgGroupPermissions['*']['createaccount'] = false;
# Disable anonymous editing
$wgGroupPermissions['*']['edit'] = false;
# Now that the wiki is locked down to spam, turn off the nofollow attribute so Sphider will follow links
$wgNoFollowLinks = false;
A course podcast (example) is much like a topic podcast (example) except that:
Courses are created on two sheets of the spreadsheet. On the 'Item' sheet, courses are managed on the right-hand side of the spreadsheet, the section marked in blue (starting at column 'N' at the time of writing this).
Then, on the 'Courses' sheet:
Are churches happy for you to do this with their talks?
Yes, it would seem so. Originally I thought I'd have to host talks myself - the idea of just pointing to the talks already hosted on their servers hadn't occurred to me - and I contacted a few churches for permission to rehost their talks on my server. All of them except one said no - they wanted me to point to the talk already on their server. It was basically their idea to do the podcast system this way (and it was a better idea than mine). Besides, the talks are on the public web so linking to them by any means is to be expected.
What's the difference between the 'Item Title' and the 'Item Subtitle' in the Excel spreadsheet?
In a bid to keep URIs short, the Item Title file is used to create the URI, page title and headers of the talk's web page, whereas the Item Subtitle appears only in the page title and text. Note that the spreadsheet does not check that the Item Title is unique, because it's reasonable for different authors to give talks the same name. Instead the macro adds the author's initials as a suffix in the URI to differentiate between homonymic titles. Also, the code truncates URIs (where the Item Title is long) except where the title contains a number, so that talks in a series (e.g. Praying in the Holy Spirit 1, Praying in the Holy Spirit 2, etc.) can be differentiated (where otherwise the distinguishing characteristic, the number, might be truncated). The bottom line for you as a user is:
Do I need to submit these feeds to the iTunes store?
No. Users can subscribe by clicking on the iTunes link or taking the podcast feed URI and pasting it into iTunes (in Windows version of iTunes, it's under [File] then [Subscribe to Podcast...].)]. Note that iPads, iPhones can't subscribe directly from the link, however, although there might be a way to make it work (does anyone know?).
What version is this? Where are the release notes? Where is the user forum?
Version? Release notes? User forum? Bah. As far as I know I'm the only person on the planet using this. But if the global user base increases beyond 2 - or maybe 3 - I can do something that resembles version control, release notes (of a kind) and maybe a forum. Contact me to put your order in.
Check your feed validity
At the bottom of the bytopic.htm page is an 'Admin' section that allows you to validate your podcast feed. This is a good habit to get into as it's easy to find dodgy characters in your description (that you've cut and paste from a website) or to put the wrong info in the wrong field (e.g. a URL into the duration column), any of which can invalidate your feed. Validating the 'All episodes' feed will catch most problems, but you can check others individually if you're having problems. Feel free to contact me if you have any persistent problems.
Channel image
The podcast generator correctly uses the channel image tag to specify a channel (podcast) image for iTunes and other feed readers. While Firefox picks it up (and your browser might as well), iTunes doesn't - it picks up images from episodes. I assume that iTunes is recognising the GUIDs (MP3 links) found other podcasts found in its database and using those episode / channel images instead.
Check the MP3 URI
Some sites track MP3 downloads from their site by using a URI which goes something like this: http://www.trackingsite.com/trackingcode.php?url=http://realpathtoMP3.com/filename.mp3&otherstuff. My software will pass it through to the feed but iTunes doesn't like it and won't display the podcast episode. To make it work you need to strip the tracking code away and list only the MP3. This isn't entirely polite, but given that I'm also pointing back to the author's website (and my audience is much smaller than the author's, meaning that their stats won't be skewed too badly) I don't think it's a serious breach of etiquette.
iPads, iPhones ...
iPads can't use the podcast subscription links - Apple seems to have locked them down so that you have to subscribe to podcasts through the iTunes store. Nor can they use the Flash audio player. They can, however, see the HTML5 audio player via Safari browser.
HTML5 audio player
Support for HTML5 audio in browsers is inconsistent at best. Fortunately v21 of Firefox now supports MP3 files in their HTML5 player, as do Chrome and Safari. Internet Explorer doesn't seem to work, but what are you using Internet Explorer for? Get thee Firefox.
Alphabetical order of topics and authors
I couldn't find some Visual Basic code that put arrays into alphabetical order so I created my own. For the sake of coding time it only sorts on the first two letters of the string, creating some misordering. It's on my to-do list.
How publishing dates are set
The macro sets the publishing date automatically, for any row in which the 'Date (Auto-Generate) cell value is not have a valid date. This means you can manually set dates or overwrite any auto-generated date of your choice and the macro will not overwrite your date (provided that you gave it a valid date). Note, however, that:
Feel free to contact me via my personal site.
Sermon browser, a plug-in for Wordpress.