Forget the Mac Mini, Bring on the Raspberry

This weekend I decided to try and tackle [turning a Mac Mini into a server to host my own photos]( Well, that turned into a real mess and I abandoned the idea after I had to disassemble the computer to retrieve a stuck recovery DVD. We went all kinds of places together, but this Mac couldn't go the distance with me this time.

So, I grabbed the semi-used Raspberry Pi 3 that was working as a wireless musicbox on our stereo (kind of) and gave it an overhaul. I removed the [PiMusicbox OS]( and went back to a fresh Raspbian image. (Actually, I only grabbed the Lite distribution because I won't need to go to the desktop. Ever.)

I wanted a basic LAMP (Linux - Apache - MySQL - PHP) stack to run the website, specifically because the end goal was to have [Lychee]( installed and running on a public space. I relied on two _really good_ tutorials to help me through the process.

The first, written by a guy named Richie, is a [full-blown, step-by-step guide]( on all the software setup. He even uses WordPress as his thing-to-install-at-the-end, so that's a bonus. It's written for non-technical people and isn't just a wall of command line code to type in. He had explanations (why does he always use the `-y` flag on his install commands?) and screenshots of what to expect. Really superb. If you're looking to try setting up a local server (available only on your wifi network) or have students who want to try, this is the place to start.

Once everything was going, I went to the GitHub project and used a quick command download the package into the Pi:


and then unzipped the project:


This put all of the source files into the `/var/www` directory on the Pi, which becomes the public space. For updates, I can just use `git pull` in the directory and I'll get those updates automatically. A cron job could even take care of that, so double bonus.

I was able to go to my internal IP address and see the setup prompt. I signed into my MySQL admin and I was off.

CC0 by Brian Bennett

Photos are organized by album and tags, so you can quickly search for items. I uploaded an old photo of my wife, just to see if it would accept files.

CC0 by Brian Bennett

There's another option I need to dig into that says "Upload from Link," but I'm not quite sure what that does yet. In the short term, I can start uploading photos here rather than Flickr.

The second article had some hints about how to get the server visible to the public. Your modem and router take a public IP address from your ISP and convert it into something you can use in the house. So, getting the Pi up with an IP address is easy to do and use, but only if you're at home. Making it publicly available requires two things:

- Some serious guts (this was the part I was most scared about)
- Some IP address and DNS work (potentially)

RaspberryPiGuy, who apparently works for RedHat, has a guide on [taking your server public]( I added a couple more packages to help with security, like fail2ban, which blocks an IP address after too many login attempts. I'm also going to split my network one more time so my home computers are sequestered from this little public slice. I found my public IP address on [this site]( and then edited my router to forward traffic to the **public** IP (my house) to the **Pi** (the internal network IP).

I was able to use my phone on 4G to go directly to the public IP address and see public photos in my library, so mission accomplished for tonight. The next steps are to do some DNS forwarding so you don't have to memorize an IP address to see pictures. Some other considerations are to get a static IP so those DNS records don't get messed up, but I have to work up to that call to Comcast.


Featured image is Looking Through the Lens flickr photo by my friend, Alan Levine, shared into the public domain using Creative Commons Public Domain Dedication (CC0)

Dynamic Data Validation with Apps Script

My coworker, Kat, needed to set up meetings with our 13 elementary schools. Appointment slots would have been a lot of clicking back and forth on calendar events plus her dates were variable and subject to change. She set up a spreadsheet with each school on a row. The challenge was to set up data validation rules that would automatically update after a school made a selection.

cracks knuckles

The first thing to do was to list out all the possible dates. Updating a spreadsheet is easier than changing a validation object in Apps Script. I can also loop the sheet and make structural adjustments on the fly.

Once that list was done, we wrote a function to set validation on the sheet using the date.

Now that validation is set, we needed to listen for changes to the spreadsheet. The onOpen function has an event object which holds information about where and how the sheet was edited. This let us limit the function to changes in columns two or three only. If a cell was edited (ie the validation dropdown was changed by the user) the function gathers event data and passes it along to the workhorse.

Now we can update the validation range. Loop through the entire range of dates backwards. Once a matching row is found, delete the row. Looping backwards prevents your index from changing. We also remove validation from the edited cell at this point so it doesn’t show an error for having a value outside the validation list.

Because validation is pulled from a range, deleting the row (and the value) removes it as an option in all of the other cells! Once the sheet is finished and everyone has a date, you could even add a short loop to push dates to your Google calendar as all day events.

Here’s the full script if you want to add it to your sheet.

Featuredimage is Array of Rivets flickr photo by jim.choate59 shared under a Creative Commons (BY-NC-ND) license