It’s just about GDPR time!

(Image credit: Flickr)

I still remember back when the Internet was an escape from the real world. Today, it seems that the real world is an escape from the Internet. Over the last 30 years, a substantial amount of daily life has moved online for billions of people: News, communication, sharing, commerce, banking, and entertainment.

Wherever human activity goes, regulation inevitably follows. This year it seems like the internet is truly going mainstream, with new regulations from 2 of the 3 largest economies in the world: the US’ FOSTA, and Europe’s GDPR.

I’m actually quite a fan of GDPR – or at least, what it’s trying to do. In GDPR, there’s an attempt to extend protection of EU citizens rights across the Internet, following their personal data wherever it may end up. And the regulations make a lot of sense from a consumer perspective: If companies want your personal data, they have to prove they can handle it responsibly, and you retain basically all the rights.

GDPR goes “live” on the 25th of May (just under 3 weeks!), and I’m hoping it heralds a new era of more responsible data practices. I think everything they’re trying to do is achievable, and should be baked into organizational and system design for new businesses (“privacy-first architecture”).

For existing businesses though, it’s going to be hell of a slog. I’ve worked on data audits and compliance for some of the world’s biggest brands, and it’s impossible to overstate how tricky full compliance is going to be. When your database software vendors have gone defunct, when your processes have spiraled out into spreadsheet nightmares, when your primary method of data exchange is email attachments, you’ve got a problem.

Over the last few days I’ve put some stuff live to help with that. For one, there’s already a wave of companies that are blocking the EU from accessing their services outright. It’s a sensible short-term move if you need time to assess the impact, and update your processes to comply. The rights of Erasure and Portability could require non-trivial work if you’ve been historically lax with how you manage data internally.

For that use-case, I pulled together a repo (gdpr-blackhole) of the IP ranges (IPv4 and IPv6) of all 28 EU member states, UK included. Brexit or not, early indications are that the UK will adopt GDPR in its entirety.

Then, since I work mainly in Laravel, I wrapped that up in a simple Middleware class that makes blocking EU IPs straightforward (laravel-gdpr-blocker).

Finally, I’ve just put a short blog post live on Amberstone: “What small businesses need to know about GDPR“. Large businesses already have armies of lawyers, auditors, and officers to assess their liability. Small businesses are not exempt, and if you want to participate in the second-largest economy on the planet, you’re going to need to know a bit about what GDPR asks of you.

Overall, it’s an exciting change. The ICO has already indicated that they’d sooner work with non-compliant organizations to improve their stewardship of personal data, making the eye-watering fines a last resort. And if all goes well, this is exactly the sort of bar-raising work we’ve needed on the world stage.

And with any luck, it means less Excel spreadsheets loaded with personal data. Time will tell ūüôā

Integrating Laravel and Flarum

One of the features I wanted to add to Write500 was a community forum. I’ve worked with Flarum before, and while it’s still ostensibly beta software, it has some really nifty features and works quite well across desktop and mobile. I decided early on that I’d plan to integrate a standalone instance of Flarum into Write500.

Flarum has a JSON API, which as it turns out is really easy to use (despite being insufficiently documented at the moment).

I wanted to set up an integration such that Write500 handled the authentication (you could only log in from the site, not the forum itself), and that changes to a user’s Write500 profile would be synced across.

Laravel Flarum - Page 1 (4).png

This integration broke down into several parts. I’ll go into detail on each one here – hopefully it’s useful!

Contents

  1. Laravel Setup
  2. Flarum Setup
  3. Integration: Creating a new user
  4. Integration: Authenticate the user
  5. Integration: Avatars
  6. Conclusion and Additional Notes

Laravel Setup

At the outset, I implemented a “nickname” field on my User model, with the following constraints (in the validator() method in RegisterController).

'nickname' => 'required|max:32|unique:users'

Those are the same constraints Flarum uses. So now, when I integrate the two (using the nickname as the Flarum username), I can be at least 99% sure it’ll work every time – barring the inevitable weird-unicode-character issue, or some other edge case.

I also ended up adding the following to my User table:

$table->string('nickname');
$table->integer('forum_id')->nullable()->default(null);
$table->string('forum_password')->nullable()->default(null);

Those fields will be populated when the integration runs, and are critical for everything else.

Finally, I needed a few configurable fields. I added these to my config/app.php file:

'forum_url' => env('FORUM_URL', 'https://forum.write500.net'),
'forum_admin_username' => env('FORUM_USER', 'default_admin'),
'forum_admin_password' => env('FORUM_PASS', 'default_pass'),

Note that in the samples below, I don’t reference any of these fields directly.

Flarum Setup

There are a few tweaks made to Flarum to enable all of this. In summary:

  • Installed the latest version of Flarum
  • Set up HTTPS using LetsEncrypt
  • Added the Flarum Links extension
  • Set up the default admin user as a generic account (not named after myself)
  • Disabled new-user registration

About the Links extension

When a user navigates to the community, I want them to be able to navigate back to any other part of the site, without feeling like they’ve really left Write500. To do this, I used the Links extension to create a navbar with the same links as on the main site.

Screen Shot 2017-02-13 at 2.31.49 PM.png
Header on main site
Screen Shot 2017-02-13 at 2.31.58 PM.png
Header on Flarum

I could have used Flarum’s Custom HTML Header feature for that, and created my own menu bar to replace the forum’s one, but that would have lost me the search bar and notification indicator.

About the default admin user

I use the username and password for the admin user to drive the integration. I could technically create an API key on Flarum’s side and just use that instead (it would save me a CURL call), but that also means going directly into the Flarum database to do stuff, which I wasn’t keen on.

One day, when you can generate API keys in the Flarum interface itself, that would obviously be the way to go.

Note about the code samples

The code samples below (all the gist embeds) are not taken directly from my Write500 source – they’ve been adapted for standalone illustration.

Integration: Creating a new User

My first integration requirement:

Whenever a new user is created in Laravel, that same user must be created and activated in Flarum.

The following has to happen:

  1. Authenticate to the Flarum API with my admin account
  2. Create a new user
  3. Activate that user
  4. Link that user to our Laravel user

I ended up putting all of that in a Job, and dispatching it when a new User object was created (in my RegisterController):

dispatch(new \App\Jobs\SyncUserToForum($user));

And now for the actual code!

Step 1: Authenticate

Nothing complicated here – just POSTing to the API to get a token back for this session. This would be the step I could skip if I had a preconfigured API token.

This same method is used whenever I need to log into the Flarum API, so for brevity I’m excluding this code from the rest of the samples.

Step 2: Create a new user

I should, strictly, be checking the API first to see if that nickname or email exists – to prevent any attempts to create duplicate users. However, both Laravel and Flarum itself should fail a new registration if the nickname or email address fields are not unique – so I should be okay there.

Step 3: Activate the user

This could possibly be done in the registration call itself (sending isActivated=true as an attribute upfront), but I’m breaking this out for another reason.

I know that, at some point, I’m going to set up a registration workflow on Write500 that requires users to activate their account before they can log in, and I’m only going to trigger the Flarum activation at that point.

What this means, practically, is that a user’s nickname will be reserved across the system, but will only be @-mentionable once their Write500 account is activated.

Step 4: Link to our Laravel user

When I created this new user, I generated a random password like so, which was used to create the user on Flarum:

$generated_password = substr(md5(microtime()), 0, 12);

The user never has to know this password, since the actual authentication is handled by the site. So we just need to update a few things on our user model:

// $new_user is the result of the POST /api/users call
$user->forum_id = $new_user->data->id;
$user->forum_password = $generated_password;
$user->save();

The forum user ID is not strictly required, but it makes it easier to check whether the user has a forum account yet or not – we don’t want to present the sign-in link to the user before they have a forum account to sign in to:

if($user->forum_id != null) { /* Integrated successfully */ }

Summary

So we now have a job that registers a new user account on Flarum once we have a valid Laravel user – none of it requiring any modifications to Flarum’s internal database.

Integration: Authenticate the user

Next, we need users to be able to authenticate to the forum.

Create a one-click link on Laravel’s side that authenticates and redirects the user to the Flarum instance.

There are a couple of ways to handle this, and I’ll admit upfront that the solution I’ve come up with (as of right now) may not be the best, security-wise.

The following has to happen:

  1. Create a new session token
  2. Set that as a cookie, then redirect to the forum
  3. Update the forum to only allow authenticated users access

Flarum makes this quite easy – the Token you get from the /api/token endpoint can be used as a flarum_remember cookie.

Step 1: Create session token

Once a user has been provisioned in Flarum (they have a numeric forum_id value on their model), we can use their nickname and password to generate a new session token, in the same way we authenticated the admin user.

Step 2: Set token value as cookie

I butted heads with this step for a while, before coming to a solution. On the Flarum side, I had to create a new auth.php file in the forum web root (where index.php and api.php are), with the following:

All that file does is take whatever token value you give it, set it as a cookie, and then bounce you to the forum homepage.

In terms of security, this setup is making me a bit nervous. However, it’s worth noting:

  • The token is sent over HTTPS, and HTTPS URLs and parameters are encrypted
  • The only way to hijack someone else’s account using this, would be knowing their session ID – at which point you can manually add that as a browser cookie anyway
  • The only other exploit that’s possible here is someone trying a script injection attack, but again, that’s nothing you cannot set in your browser manually
  • If a user tries hitting this route manually with a bad token value, they’ll be bounced to the forum, invalidated (see below) and then sent back to the login page.

With that script set up, you can now redirect the user to it from Laravel (as in Step 1), and they’ll be bounced to the forum, fully authenticated.

return redirect()->to('https://my.flarum.url/auth.php?token=' . $session->token);

Step 3: Only permit authenticated users to view the forum

This was another ugly script hack. Flarum itself lets you set permissions so that guests cannot read content – they’ll just see a “no discussions” message when opening the forum.

However, since I’m also planning on removing the Login button on the forum, the only way an unauthenticated user will be able to log into the forum, is by going via my login page.

So I need to add a redirect before Flarum itself fires, to check whether the user is authenticated. I ended up with this:

When a user authenticates with Flarum (which all runs via the api route eventually), a new access token is generated and stored in that table. If the value matches, they’re logged in – if not, they’re bounced to my login page. After logging into Write500, hitting that Community login route will authenticate and take them through.

That could be improved by reading the database connection details from config.php instead, but those values are not likely to change for this setup. I’d also have preferred it if Flarum used some sort of file storage for sessions, in the same way basic Laravel does – but we can’t always get what we want ūüėČ

Integration: Flarum Modification

So we now have the basics down – new users are registered, users can log in via my site, and users cannot reach the forum without being authenticated.

Modify Flarum to integrate the navigation with the main site, and prevent users from logging out via Flarum.

On the Flarum side, I want to change what some of the buttons in the navigation do – for instance, they shouldn’t be able to log out via Flarum (since that still leaves them logged into Write500), and I don’t want them to be able to manage their forum settings directly in Flarum.

After some trial and error, and almost giving up and hacking on the core scripts, I eventually landed on this neat hack.

In the Flarum administration section, under Appearance, you can add your own custom HTML. I used that to deploy a script block that looks like this:

That script is loaded in the page header, and executes immediately. At the end of the page, Flarum loads its app script and creates an app object. Before that happens, there’s nothing to modify, and I cannot force my javascript to run at the end of the page. So instead, my script keeps checking to see if the app variable is available, and when it is, it makes some changes.

With that script in place, the forum is now about as integrated as it can get – there’s just one more¬†thing to cover – Avatars.

Integration: Avatars

Users can upload and crop avatars to Write500.

Automatically synchronize the user’s avatar from Laravel, to Flarum.

I wanted those avatars to sync across to the forum, whenever they were created or updated on Write500’s side. I also have a “Reset to Default” which deletes the current avatar, and that deletion should be synced across as well.

Delete Avatars

This one was pretty easy. On the route that resets my avatar, I dispatch a job that basically does this:

It authenticates to the Write500 API, and issues a delete request against the avatar resource. Simple.

Upload avatars

This one’s a bit more complicated. The API endpoint is expecting a form-data upload, which takes a bit of fiddling to get right on PHP’s end.

After a user uploads, crops and saves an avatar, it’s stored in the public/ directory with a GUID, which is saved against the User model. All I really need to do is read that file from disk and upload it to the Flarum API. Which I achieve like so:

That bit of code will blindly upload a new avatar. Flarum doesn’t require you to delete an existing avatar before uploading a new one, so I can trigger this all I want. Right now, it’s just dispatching as a job after a new avatar is saved in Write500.

I’m not sure whether or not the User-Agent is strictly required for this, but I haven’t had time to go back and test it. I do know that the Flarum API does behave differently depending on whether it thinks it’s being hit from a browser.

Conclusion

So that’s an exhaustive look at how I’ve enabled the following integrations:

Laravel Flarum - Page 1 (5).png

I’m sure that some elements of this could be improved, and if I make any substantial updates to this, I’ll likely write a follow-up post here.

Additional Considerations

This is all still a work in progress, and I’m sure I’ll find more bugs as I go along.

Right now, for instance, I think it might make more sense to keep the user token cached in the User model, and only issue a new one when the current one expires. Right now, if you upload an avatar (for instance), it generates a new token that might invalidate the old one. So you’d have to log into the community from scratch to get access again.

There’s also notification integration. There’s an API endpoint that returns information about new notifications (@-mentions, etc), which I would want to display in the Write500 navbar.

I’d also want to take over the profile view eventually. I’m aiming to build a user profile page in Write500 to show some high-level stats, and I’d rather integrate forum activity in there, as opposed to having Flarum display any user profile page at all. Finally, I need to consider what will happen when users cancel their Write500 subscriptions.

Those are all questions for another day though!

Laravel 5.3 + Cloud9

I use Cloud9 as my primary IDE – it handles all my PHP projects, my Domo app projects, and when I start seriously developing Ionic-based mobile apps, I’ll find a way to use Cloud9 for that too.

I’ve found it particularly handy for Laravel development: A lot of the prerequisite software provided by Homestead comes pre-installed on the Ubuntu VM that Cloud9 provides, and with a few small config tweaks, it’s possible to get up and running very quickly.

1. VM Setup

One of my cardinal rules: All your¬†code should exist in a repository.¬†Especially with web development, there’s basically zero excuse to keeping substantial amounts of code locally.

My first step is to always create a blank repository, and use that as the clone URL for a new Cloud9 VM.

2017-01-20 05_48_50-Create a New Workspace.png

The Blank Ubuntu VM is good enough for our purposes – there’s no benefit to selecting the PHP VM, since we’ll be installing the latest PHP in any case.

2. Services Setup

Chances are, whatever you’re building in Laravel will need the MySQL service to be running, and in most cases, you’ll probably want Redis too. Cloud9 has made it a little bit more difficult to automate service startup (presumably to minimize unnecessary resource utilization), but there’s a way around that.

If you’re using a premium workspace, Cloud9 will keep it “hot” between sessions – everything you start will continue to run even after closing the IDE window itself. But that will expire after a while, and it can be a hassle to keep restarting your services.

So, the first change we’ll make to our new VM is editing the /.profile file, and adding two new lines to the bottom:

$ cd ~/
$ echo 'sudo service mysql start > /dev/null 2>&1' >> .profile
$ echo 'sudo service redis-server start > /dev/null 2>&1' >> .profile

That will start the services (and skip startup if they’re already running), won’t show any console output, and will run whenever a new terminal window is created. So now, every time you open up a new terminal window, you’re guaranteed to have those services running:

2017-01-20 06_11_31-cloud9-demo - Cloud9.png

It’s not as ideal as having the server start it automatically, but in my opinion, this is a small trade-off to make.

3. PHP 7 Setup

If you’re deploying code to a new server on DO, or AWS, or Rackspace, or most other places, chances are it’s running PHP 7. By default, Cloud9 does not include PHP 7 (for reasons which escape me), so we need to take a quick detour into apt-get land.

There are two ways to set up PHP here – apt, or phpbrew. For a while I was using phpbrew, which builds PHP from source. It’s a decent option, but using ondrej/php is faster.

$ sudo add-apt-repository ppa:ondrej/php

Then you’ll need to sudo apt-get update. When that’s done, you should have access to the php7 packages:

2017-01-20 06_18_23-Groove Music.png

I’m using php 7.1 here, because that’s what my DigitalOcean VM is running. Laravel will need that, plus a few other modules:

$ sudo apt-get install php7.1 php7.1-mysql php7.1-curl 
  php7.1-xml php7.1-mbstring

That shouldn’t take more than a minute. And now we’re good to go:

2017-01-20 06_21_26-cloud9-demo - Cloud9.png

4. Laravel Project Setup

There are quite a few ways to get Laravel installed. It has a CLI-type thing that lets you do laravel new, you can clone it directly from github, or you can use Composer.

I tend to favor composer since it’s a once-off command for this workspace. There’s no benefit to installing any more tools here, since we’re only going to create 1 Laravel project in this VM. And since we’ve already got our workspace folder linked to our project repository, git can get messy.

So my favorite – use composer to set up Laravel in a temporary directory, then move it all across:

$ cd ~/
$ composer create-project laravel/laravel tmp --prefer-dist
$ mv tmp/* workspace/
$ mv tmp/.* workspace/
$ rmdir tmp

Why two move commands? The first catches all the files, but Laravel does include a few dotfiles that aren’t caught by the first move command.¬†Sure, you could configure dotglob to modify mv’s behavior, but we’re only doing this move once for the workspace.

Just one more setup step – the database. Start by adjusting the .env file to set the username to root, and a blank password:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=homestead
DB_USERNAME=root
DB_PASSWORD=

Then we’ll create that database in one go:

$ mysql -uroot -e "CREATE DATABASE homestead;"

You can now run the initial migrations if you want.

5. Node Setup

If you’re planning on using Elixir to compile frontend assets, you’ll probably want to upgrade to the latest version of Node. Cloud9 makes this easy:

$ nvm install 7.2
$ nvm alias default 7.2

All node commands will now use the 7.2 binary. You can now install the necessary components in your workspace folder:

$ npm install

6. Running Laravel

Cloud9 features a reverse proxy – anything that binds to port 8080 in the VM will be accessible via a URL unique to your workspace. It also has a dedicated “runner” feature, which we’ll¬†configure like so:

2017-01-20 06_33_15-cloud9-demo - Cloud9.png

The name can be anything, but the run command will be:

php artisan serve --host=0.0.0.0 --port=8080

Cloud9 does make mention of $HOST and $PORT variables, but they’re always the same values anyway. Fill that in and click the Run button on the left:

2017-01-20 06_33_53-cloud9-demo - Cloud9.png

Cloud9 should open up an in-IDE browser window that points to your site. You can click the little pop-out button on the right of the “address bar” to bring it up in a new browser tab.

2017-01-20 06_36_01-cloud9-demo - Cloud9.png

That domain (c9users.io) will check for cloud9 authentication before proxying to your VM – in short, that URL will only work for you, and anyone else on cloud9 that you’ve shared the workspace with. Those settings are managed via the Share button on the top right:

2017-01-20 06_37_40-cloud9-demo - Cloud9.png

From that popup, you can invite new users to the workspace (cloud9 does issue free accounts), or you can just make the application URL public:

2017-01-20 06_38_34-cloud9-demo - Cloud9.png

You can now share that URL to anyone.

7. Get coding!

You’ve now got just about everything you need for proper Laravel development! It’s usually at this point I then take the extra step of configuring Amazon S3 as a cloud disk, push the blank Laravel project to bitbucket, and deploy my repo as a site on Forge.

Use Amazon S3 with Laravel 5

Laravel’s Filesystem component makes it very easy to work with cloud storage drivers, and the documentation does an excellent job of covering how the Storage facade works – so I won’t repeat that here.

Instead, here’s the specifics on getting Laravel configured to use S3 as a cloud disk. These instructions are valid as of 4 January 2017.

The AWS Setup

On the AWS side, you need a few things:

  • An S3 bucket
  • An IAM user
  • An IAM policy attached to that user to let it use the bucket
  • The AWS Key and Secret belonging to the IAM user

Step 1: The S3 Bucket

Assuming you don’t already have one, of course.

This is the easiest part – log into AWS, navigate to S3, and create a bucket with any given name. For this example, I’m using write500-backups (mainly because I just migrated the automated backups for write500.net to S3):

2017-01-04 00_33_39-S3 Management Console.png
1. Easy button to find

Then:

2017-01-04 01_41_14-S3 Management Console.png
2. Select your region – with care

US Standard is otherwise known as North Virginia, and us-east-1. You can choose any region, but then you’ll need to use the corresponding region ID in the config file. Amazon keeps a list of region names here.

If you’re using this as a cloud disk for your app, it would make sense to locate the bucket as close as physically possible to your main servers – there are transfer time and latency benefits. In this case, I’m selecting Ireland because I like potatoes.

Step 2: The IAM User

Navigate to IAM and create a new user. AWS has made some updates to this process recently, so it has a lot more of a wizard look and feel.

2017-01-04 00_37_17-IAM Management Console.png
1. Add a new user from the Users tab
2017-01-04 00_37_33-IAM Management Console.png
2. Make sure the Programmatic Access is ticked, so the system generates a Key and Secret

Step 3: The IAM Policy

The wizard will now show the Permissions page.¬†AWS offers a few template policies we’ll completely ignore, since they grant far too much access. We need our user to only be able to access the specific bucket we created.

Instead, we’ll opt to attach existing policies:

2017-01-04 01_45_53-IAM Management Console.png
3. This one

And then create a new policy:

2017-01-04 01_46_01-IAM Management Console.png
4. And then this one

This will pop out a new tab. On that screen, Select “Create Your Own Policy”.

  • Policy name: Something unique
  • Policy description: Something descriptive
  • Policy document: Click here for the sample

Paste that gist into the Policy Document section, taking care that there are no blank spaces preceding the {. Replace “bucket-name” with your actual bucket name, then save:

2017-01-04 01_50_40-IAM Management Console.png
If only insurance were this easy

Go back to the IAM wizard screen and click Refresh – you should see your brand new policy appear at the top of the list.

2017-01-04 00_40_36-IAM Management Console.png
Perfect!

Tick the box, then click¬†Next: Review, and then¬†Create user. It’ll give you the Access key ID and Secret like so:

2017-01-04 00_40_50-IAM Management Console.png
3. When you complete the wizard, you’ll get these.

The Access Key ID (key) and Secret access key (secret) will be plugged into the config file.

Step 4: Configure Laravel

You’ll want to edit the filesystem details at:config/filesystem.php

Near the bottom you should see the s3 block. It gets filled in like so:

fixed.png
Remember to set the correct region for your bucket

And done! The filesystem is now configured. If you’re making your app portable, it would be smart to use env() calls with defaults instead, but I’ll leave you figure that one out ūüôā

Step 5: Test

The simplest way to test this is to drop into a tinker session and try working with the s3 disk.

2017-01-04 01_57_16-forge@write500_ ~_write500.net.png

And you should see the corresponding file in the S3 interface itself:

2017-01-04 01_57_43-S3 Management Console.png

Step 6: parrot.gif

Now that you have cloud storage configured, you should use it!.

First (and this will take minimal effort), you should set up the excellent spatie/laravel-backup package. It can do file and db backups, health monitoring, alerts, and can be easily scheduled. Total win.

You can also have Laravel do all its storage there. Just change the default disk:

2017-01-04 02_01_34-write500 - Cloud9.png
config/filesystems.php

This has the benefit of ensuring that even if your server crashes/dies horribly, nothing gets lost. You can also have multiple server instances all talking to the same s3 disk.

In my case, I’m using S3 as the storage for regular backups from write500. I’ll also use the same connection and attempt to publish my internal statistics¬†dumps as CSV directly to S3 – meaning I can¬†pull the data in via Domo’s Amazon S3 connector. I can then undo the SFTP setup I created previously, further securing my server.

Excel Bootstrap

So this is a real thing, that I actually built.

screenshot3

It’s an Excel workbook that can generate Bootstrap HTML sites. And what’s more, if you have a hosting key, it can publish that site to the web.

For real: https://github.com/woganmay/excel-bootstrap

Why, for the love of God

2016-10-14-20_00_26-slack-za-tech

Mainly: Why not. I know that VBA can do some basic string manipulation and conditional logic, and I know that it can work with HTTP, so I figured I’d put the two together.

Also, I felt like I needed some more experience with VBA. Despite all the love-to-hate that people throw at it, Excel is a remarkably capable platform and I was interested in what it might take to get it to do something weird.

Truly, though, I had been feeling a little bit irritated by the phrases “programming elite” and “bad language”. There’s this somewhat-arrogant notion out there (bordering on a holy war in some cases) that some languages are better at things than others, and there are some things you¬†dare not consider using.

Well how about if I want to build websites using Excel? Who’s going to stop me?

How it works

The workbook is “open source”, for lack of a better term – you can download it and pop open the VBA modules in Excel to see how it works.

It’s pretty simple – the main worksheet lets you fill in a few cells, and those are substituted into some basic HTML by a macro. There’s even some “component logic” – you can turn parts of the site on or off.

The results can then be written out to an HTML file. Since all the CSS and JS files are hosted on CDNs, all you really need is that one file. And there you have a basic website.

2016-10-14-19_58_37-my-first-excel-bootstrap-website

It’s even themeable! I reference the library of themes via Bootswatch, so there’s a pre-defined list of themes to choose from.

Then there’s the hosting component. I have a server lying around, and you can get TLDs for free if you know where to look, so I quickly grabbed excel-bootstrap.cf, pointed it at my server, hooked up Apache and LetsEncrypt, and built a super-basic publishing endpoint with bare-minimum security.

I mean, literally, the entire thing looks like this:

2016-10-14-20_02_53-index-php-bs-visual-studio-code
Not pictured: the 4-line htaccess to enable url rewriting

Assuming you have an HTTP-capable client, that’s pretty much all the code you need to get a dumb-as-bricks “publishing” endpoint up and running. All it does is blindly dump whatever you send into an HTML file, that it then serves out on request, with a “pretty URL”.

Plus, it has rudimentary security – you can only publish to a named site if you have the correct GUID. I mean obviously the content’s not filtered at all, and a skilled hacker could probably exploit this to dump a reverse shell on my server, but you’re looking at maybe 10 minutes worth of work. Max.

Release and iterate!

This whole project took around 3 hours. Let me walk you through the timeline, starting with my stunning realization.

The principles behind this (rapid iteration) are perfectly valid when developing new applications of any kind. I think it’s a great idea to start with a minimum viable product, and then add on new things.

The app itself? Total bullshit. Do not use this as a reference for anything.

2016-10-14-20_00_26-slack-za-tech

It took all of 34 minutes to go from that, to a basic MVP. I would not have suspected Excel to be an ideal candidate for rapid prototyping, but you can’t argue with results!

(Yes, you can, you really really can. And for the love of all that is sacred, you really should when presented with an idea as dumb as this.)

2016-10-14-20_10_16-slack-za-tech

Getting up and running on Github took no time at all – I published my work under the Unlicense, and wrote up a basic readme.

2016-10-14-20_11_37-slack-za-tech

And with my MVP out the door, it was time to iterate!

All my MVP could do was create a basic 2-line site as seen in that screenshot. I wanted to add some of the basic Bootstrap components – a Navbar, Jumbotron – and offer some sort of control over theming.

I even added Font Awesome, because every site nowadays seems to have an icon font library.

So I grabbed a more complex block of sample HTML, and grabbed a few links from Bootswatch.com. Some copying-and-pasting later (and the good old Data-Validation-for-dropdowns workaround), I had a much more robust version of Excel Bootstrap, including an updated UI!

2016-10-14-20_15_01-slack-za-tech

At this point, I’d sunk 1h45m into this project. About the length of the average project management meeting. But it was time to go further!

Excel Bootstrap could now generate decent-looking websites, but what good is a website if you can’t host it? A quick trip to Freenom.com and I’d secured myself the excel-bootstrap.cf domain. I pointed it at my Afrihost server, hacked together a totally inadequate publishing API (pictured above), and set up my workbook with a macro that could upload data.

2016-10-14-20_18_45-microsoft-visual-basic-for-applications-excel-bootstrap-xlsm-module1-code
Which was actually not that difficult, all things considered.

Wired that up to a button, gave it a few test runs, and voila!

2016-10-14-20_19_30-slack-za-tech

Total time spent = 2 hours 15 minutes

Total IQ points lost from considering such a tortured path from content to published page = unknown

So the point of this was…?

Partially to prove that I could, partially to do something weird and stupid, but mainly to prove that when it comes to delivering value, there’s no objective “right” or “wrong” way to do things.

Would I ever use an Excel workbook to generate and publish websites? Not in a million years.

Could there be any number of business users out there that would save an enormous amount of time being able to publish content from Excel to a web platform? Possibly.

Who’s to judge what the “right” tool is for the job? Basically nobody.

When it comes to choosing your tech stack: use what you have, what you’re best at, what you can support, and what can get the job done.