---
title: Bootstrap 4 Tab Persistence
date: 10/18/2020
author: Justin Williamson
summary: I find it frustrating when tabs don't persist when reloading the page. Here's a little snippet I like to use for a simple, front-end solution to...
published: true
---

I find it frustrating when tabs don't persist when reloading the page. Here's a little snippet I like to use for a simple, front-end solution to this problem.

The only downside is the amount of back button clicks it takes to get to the previous page due to the hash manipulation. I usually try to add some sort of 'go back' button to circumvent this issue.

<iframe
    width="100%"
    height="500"
    src="https://jsfiddle.net/jjwilly16/3ve0zgrq/12/embedded/result,html,js/dark/"
    allowFullScreen
    frameBorder="0"
></iframe>

Nothing special; just quick and dirty. It's all about the id naming conventions.


---
title: How to Modify the 'Date Added' Metadata in your Plex Media Server Libraries
date: 5/2/2020
author: Justin Williamson
summary: The other day I decided to restructure my photo and home video libraries. I switched from flat directories to a nested directory approach (YEAR/MONTH). This...
published: true
---

The other day I decided to restructure my photo and home video libraries. I switched from flat directories to a nested directory approach (YEAR/MONTH). This allows me to trigger partial library scans for performance reasons.

So, I went about my merry way adding over one hundred or so directories to my Plex photo library and walked away to let the server do it's thing. The next morning I noticed something - my media was no longer in the correct order. My home page 'Recently Added' section was all over the map. Looks like dumping all of the directories at once wasn't a great idea...but I really didn't want to do it again.

After a little research, I discovered that I could just edit the Plex sqlite file (com.plexapp.plugins.library.db). So, here's what I did:

## 1) Stop the Plex server

You don't want anything writing to this file while we're modifying it. My server's on Ubuntu 18.04, so I ran this:

```bash
sudo systemctl stop plexmediaserver
```

## 2) Make a copy of the DB file

Don't mess around here, just copy the file somewhere.

```bash
sudo cp "/var/lib/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.db" /tmp/com.plexapp.plugins.library.db.bak
```

## 3) Export DB file to your local machine

I'm not feeling very confident running these commands in a terminal. I want to see and inspect the data before I put the file back, so I'm editing this in a GUI.

```bash
# Run this from your local machine (assuming you have ssh access to your plex server)
rsync --protect-args -P "plex:/var/lib/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.db" D:\Desktop\plex.db
```

## 4) Edit the SQLite file in editor of your choosing

I quickly found and downloaded [DB Browser for SQLite](https://sqlitebrowser.org/dl). Why? It showed up first in Google and it looked OK. It's fine, but I removed it afterwards.

I loaded the db file in my soon-to-be-deleted editor and poked around a bit.

```sql
select * from metadata_items
```

I decided I wanted to change the _added_at_ field to match the _originally_available_at_ field. This field seems to match the creation date as far as I can tell.

```sql
update
	metadata_items
set
	added_at = originally_available_at
where
	originally_available_at is not null
	and library_section_id = 4 -- my photo library id
```

After inspecting the results, this looked pretty good so I ran the same command for my home video library and wrote the changes to the DB file.

## 5) Put the SQLite file back

First I had to rsync it to the /tmp directory so I could enter the server and move it with the correct privileges.

```bash
# From my local machine
rsync --protect-args -P D:\Desktop\plex.db "plex:/tmp/com.plexapp.plugins.library.db"
```

```bash
# On the Plex server
sudo -u plex mv /tmp/com.plexapp.plugins.library.db "/var/lib/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.db"

# Just making sure the permissions are set (they probably already are)
sudo chmod 644 "/var/lib/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.db"

# Restart the Plex service
sudo systemctl start plexmediaserver
```

That's it. It actually worked. I didn't even have to re-scan the libraries.


---
title: Server-side Excel export with jQuery DataTables, express and ExcelJS
date: 6/5/2019
author: Justin Williamson
summary: For me, the best part about the jQuery DataTables library is how quick and easy it is to get a functioning table. Because of this I avoided using server-side...
published: true
---

> If you are just here for the code, [go here](#the-code)

For me, the best part about the [jQuery DataTables library](https://datatables.net) is how quick and easy it is to get a functioning table. Because of this I avoided using [server-side tables](https://datatables.net/examples/data_sources/server_side) for the longest time. I mean, why would I want to do so much heavy lifting just to display a table? My client-side tables are doing just fine...until they weren't.

One day I noticed a page timing out on one of the sites I'd been working on. Turns out the database was growing pretty fast and the row count was closing in on 10,000. From what I hear, that is the magic number for being obligated to convert your client-side table into a server-side one.

Shit.

At first it was a little tedious to set up (modifying the SQL procedure, adding a custom export function, defining columns), but the end result was fantastic. This led me to build a few more and eventually I came up with a pretty slick process for it.

First, just be aware that you need to rewrite whatever database retrieval function that you have in order to return the properties that DataTables is looking for. You should be paging your data as well.

Here is a sample of what the server-side DataTable instance will be sending to your endpoint:

```js
{
    "draw": "1",
    "columns": [
        {
            "data": "id",
            "name": "",
            "searchable": "true",
            "orderable": "true",
            "search": {
                "value": "",
                "regex": "false"
            }
        },
        {
            "data": "first_name",
            "name": "",
            "searchable": "true",
            "orderable": "true",
            "search": {
                "value": "",
                "regex": "false"
            }
        },
        {
            "data": "last_name",
            "name": "",
            "searchable": "true",
            "orderable": "true",
            "search": {
                "value": "",
                "regex": "false"
            }
        },
        {
            "data": "dob",
            "name": "",
            "searchable": "true",
            "orderable": "true",
            "search": {
                "value": "",
                "regex": "false"
            }
        },
        {
            "data": "age",
            "name": "",
            "searchable": "true",
            "orderable": "true",
            "search": {
                "value": "",
                "regex": "false"
            }
        },
        {
            "data": "id",
            "name": "",
            "searchable": "true",
            "orderable": "true",
            "search": {
                "value": "",
                "regex": "false"
            }
        }
    ],
    "order": [
        {
            "column": "0",
            "dir": "asc"
        }
    ],
    "start": "0",
    "length": "10",
    "search": {
    "value": "",
    "regex": "false"
}
```

Your first job is to take this table config and use it to retrieve your data. For the sake of this example, I'm just going to pull some mock data out of a JSON file (1000 records) that looks like this:

```js
[
    {
        id: 1,
        first_name: 'Anson',
        last_name: 'Keetley',
        dob: '07/02/1978',
        age: 41,
    },
];
```

Here is my _'database'_ pull:

```js
const mockData = require('../../data.json');

/**
 * Acts as our stored procedure - serves filtered, sorted mock data
 * @param {Number} page - Page to return
 * @param {Number} limit - How many items per page to return
 * @param {String} orderBy=id - Which column to order by
 * @param {String} orderDir=asc - Which direction to order by
 * @param {String} search - Search string
 * @returns {Object[]} - Array of filtered, sorted data
 */
function parseMockData({
    page,
    limit,
    orderBy = 'id',
    orderDir = 'asc',
    search,
}) {
    return (
        mockData
            // filter out the search term
            .filter(item => {
                if (!search) return item;
                let match = false;
                for (const prop in item) {
                    if (
                        item[prop]
                            .toString()
                            .toLowerCase()
                            .includes(search.toLowerCase())
                    )
                        match = true;
                }
                return match;
            })
            // sort results
            .sort((a, b) => {
                if (a[orderBy] > b[orderBy]) {
                    if (orderDir === 'asc') return 1;
                    return -1;
                }
                if (a[orderBy] < b[orderBy]) {
                    if (orderDir === 'asc') return -1;
                    return 1;
                }
                return 0;
            })
            // limit the results
            .slice(0, limit || mockData.length)
    );
}
```

I'll also need my main endpoint where most of the magic happens (I'm using express):

```js
router
    .route('/')

    // Send the index.html for our example
    .get((req, res, next) =>
        res.sendFile(path.join(__dirname, './dist/index.html'))
    )

    // Datatables server-side route
    .post((req, res, next) => {
        const data = parseMockData({
            page: +req.body.start / +req.body.length + 1,
            limit: +req.body.length || 10,
            orderBy: req.body.columns[+req.body.order[0].column].data,
            orderDir: req.body.order[0].dir,
            search: req.body.search.value || null,
        });

        // We need to tell DataTables a few things
        res.json({
            // The data
            data,
            // How many total records in table
            recordsTotal: mockData.length,
            // How many records are left after filtering with the search box
            recordsFiltered: data.length,
        });
    });
```

Now for the client-side, we're going to do something like this:

```js
import $ from 'jquery';
import 'datatables.net-bs4';
import 'datatables.net-buttons-bs4';

const $table = $('#mytable');

$table.DataTable({
    // Sorry for the confusing DOM stuff. I'm using bootstrap4 and I love flexbox
    dom: '<"btn-container d-flex align-items-center justify-content-center justify-content-sm-between flex-wrap flex-md-nowrap pb-3"B><"d-flex align-items-center justify-content-center justify-content-sm-between flex-wrap flex-md-nowrap pt-2"lf>r<"table-responsive pb-3"t><"d-flex align-items-center justify-content-center justify-content-sm-between flex-wrap flex-md-nowrap pt-3"ip>',
    serverSide: true,
    processing: true,
    deferRender: true,
    ajax: {
        url: './',
        type: 'POST',
    },
    lengthMenu: [
        [10, 25, 50, 100, 999999],
        [10, 25, 50, 100, 'All'],
    ],
    pageLength: 10,
    order: [[0, 'asc']],
    buttons: [
        {
            text: 'Download Excel',
            action: (e, dt) => {
                // Set the file and sheet names
                const fileName = 'people';
                const sheetName = 'People';

                // Organize table data
                const order = dt.order();
                const orderDir = order[0][1];
                const orderBy = dt.init().columnDefs[order[0][0]].data;
                const search = dt.search() || null;
                const columns = JSON.stringify(dt.init().columnDefs);

                // This is used to send to the backend, where a
                // cookie will be created. We will later check that
                // cookie against this. When it exists, that means
                // our download is complete (pretty hacky)
                const token = Date.now();

                // Dynamically create and submit a form
                function nonAjaxPost(path, params, method = 'POST') {
                    const tempForm = document.createElement('form');
                    tempForm.setAttribute('method', method);
                    tempForm.setAttribute('action', path);

                    for (const key in params) {
                        if (params.hasOwnProperty(key)) {
                            const hiddenField = document.createElement('input');
                            hiddenField.setAttribute('type', 'hidden');
                            hiddenField.setAttribute('name', key);
                            hiddenField.setAttribute('value', params[key]);
                            tempForm.appendChild(hiddenField);
                        }
                    }

                    document.body.appendChild(tempForm);
                    tempForm.submit();
                    tempForm.remove();
                }

                // Get cookie by name
                function getCookie(cname) {
                    const name = cname + '=';
                    const decodedCookie = decodeURIComponent(document.cookie);
                    const ca = decodedCookie.split(';');
                    for (let i = 0; i < ca.length; i++) {
                        let c = ca[i];
                        while (c.charAt(0) === ' ') {
                            c = c.substring(1);
                        }
                        if (c.indexOf(name) === 0) {
                            return c.substring(name.length, c.length);
                        }
                    }
                    return '';
                }

                // Delete cookie by name
                function deleteCookie(name) {
                    document.cookie = `${name}=; Max-Age=-99999999;`;
                }

                // Show a spinner when the download starts
                $('#spinner').show();

                // Initialize download
                nonAjaxPost('./excel-download', {
                    search,
                    orderBy,
                    orderDir,
                    columns,
                    fileName,
                    sheetName,
                    token,
                });

                // Now we need to check for the existence of a cookie that
                // signals the end of the download. Then we can hide
                // our spinner.
                const checkIfDownloadIsComplete = setInterval(() => {
                    if (getCookie('DownloadComplete') === token.toString()) {
                        clearInterval(checkIfDownloadIsComplete);
                        $('#spinner').hide();
                        deleteCookie('DownloadComplete');
                    }
                }, 500);
            },
        },
    ],
    // Define all columns
    // 'numberFormat' and 'exportable' are custom properties that we are adding
    // in order to communicate with the backend for Excel file creation
    columnDefs: [
        {
            targets: 0,
            data: 'id',
            title: 'ID',
        },
        {
            targets: 1,
            data: 'first_name',
            title: 'First Name',
        },
        {
            targets: 2,
            data: 'last_name',
            title: 'Last Name',
        },
        {
            targets: 3,
            data: 'dob',
            title: 'Date of Birth',
            // Set the number format in Excel
            numberFormat: 'mm/dd/yyyy',
        },
        {
            targets: 4,
            data: 'age',
            title: 'Age',
            className: 'text-center',
            // Set the number format in Excel
            numberFormat: '0',
        },
        {
            targets: 5,
            data: 'id',
            title: 'Ignored Column',
            className: 'text-center',
            // This column will be ignored in the Excel download
            exportable: false,
            render() {
                return 'Ignored in Excel';
            },
        },
    ],
});
```

<h3 id="the-code">The Excel Download Code:</h3>

I wrote a piece of middleware for this because I end up using it all over the place.

```js
const Excel = require('exceljs');

/**
 * Convert a dataset to an Excel file
 * @param {Object} req
 * @param {Object} req.body
 * @param {Object} req.body.fileName - Name of file
 * @param {Object} req.body.sheetName - Name of sheet
 * @param {Object[]} req.body.columns - Array of column definitions
 * @param {(String|Number)} [req.body.token] - Optional token to set a cookie when complete
 * @param {Object} res
 * @param {Function} next
 */
function dataToExcel(req, res, next) {
    // First we need to get the data from the previous middleware
    const sheetData = res.locals.results;

    // Create the workbook
    const workbook = new Excel.Workbook();

    // Set some metadata
    workbook.creator = 'Justin Williamson';
    workbook.lastModifiedBy = 'Justin Williamson';
    workbook.created = new Date();
    workbook.modified = new Date();
    workbook.date1904 = true;

    // (optional) - Freeze the header
    workbook.views = [
        {
            state: 'frozen',
            ySplit: 1,
        },
    ];

    // Create the worksheet
    const worksheet = workbook.addWorksheet(req.body.sheetName || 'Sheet 1');

    // Set up the columns
    const columns = JSON.parse(req.body.columns)
        .filter(column => column.exportable === undefined || column.exportable)
        .map(column => ({
            header: column.title || '',
            key: column.data || '',
            formula: column.formula || '',
            width: 20,
            style: {
                numFmt: column.numberFormat || '',
                font: {
                    name: 'Arial',
                    size: 10,
                },
            },
        }));

    worksheet.columns = columns;

    // Add the row data
    worksheet.addRows(
        sheetData.map(row =>
            columns.reduce((array, column) => {
                array.push(row[column.key]);
                return array;
            }, [])
        )
    );

    // Format the header text
    worksheet.getRow(1).font = {
        name: 'Arial Black',
        size: 10,
    };

    // Set headers for download
    const fileName = `${req.body.fileName}.xlsx`;
    res.type('application/octet-stream');
    res.set('Content-Disposition', `attachment;filename="${fileName}"`);

    // Sometimes we need to notify the client when the download is complete.
    // We do that by setting a cookie
    if (req.body.token) {
        res.set({
            'Access-Control-Allow-Credentials': true,
            'Set-Cookie': `DownloadComplete=${req.body.token}; Path=/;`,
        });
    }

    // Response
    return workbook.xlsx
        .writeBuffer()
        .then(buffer => res.send(buffer))
        .catch(next);
}

module.exports = dataToExcel;
```

Now we just need to set up our Excel download endpoint:

```js
const dataToExcel = require('../middleware/data-to-excel');

router
    .route('/excel-download')

    .post((req, res, next) => {
        // Important to get the data the same way as in the previous endpoint
        const data = parseMockData({
            page: 1,
            limit: 999999,
            orderBy: req.body.orderBy,
            orderDir: req.body.orderDir,
            search: req.body.search !== 'null' ? req.body.search : null,
        });

        // MUST set the results on the res.locals object
        res.locals.results = data;
        next();
    }, dataToExcel); // Call our middleware here
```

Now everything should be working - our Excel download button will kick off a POST to our '/excel-download' route, where the Excel file is built. Once the download completes, a cookie will be set on the server that our front-end code will detect and hide the spinner.

If you want to test out the demo, [you can download the code on github](https://github.com/jjwilly16/jjwilly.com-demos/tree/master/demos/datatables-excel-download).


---
title: Let's Build a Website Uptime Bot with Google Apps Script
date: 7/9/2018
author: Justin Williamson
summary: Here's the thing - I know I should be monitoring all of my websites' uptime...but I'm real lazy. So, let's automate this. I mean, this shouldn't require a lot...
published: true
---

> If you are just here for the code, [go here](#the-code)

Here's the thing - I know I should be monitoring all of my websites' uptime...but I'm real lazy. So, let's automate this. I mean, this shouldn't require a lot of effort, right?

I present to you today a solution for monitoring the uptime of ALL of your sites in a very custom manner that won't cost you a penny and doesn't require a server to run it. What is this magic, you ask?

It's [Google Apps Script](https://developers.google.com/apps-script/). More specifically, [Google Sheets scripting](https://developers.google.com/apps-script/reference/spreadsheet/) - a very powerful set of tools that is limited only by your imagination.

I've seen a few other scripts out there that do the same thing as this, but I haven't found one that behaves quite how I want it to, so let's hurry up and build this thing before Google gets smart and charges us for it.

## Building the Script

The sheet is going to be pretty simple - enter your website URLs and a corresponding notification email (to be notified when the site is down) and set up your timed trigger.

Something like this:

<Image
  alt="Uptime robot google sheets page 1"
  src="/images/posts/build-website-uptime-bot/Uptime-Robot-Google-Sheets-page1.png"
  caption="The sheet is going to be pretty simple - enter your website URLs and a corresponding notification email (to be notified when the site is down) and set up your timed trigger."
  height={378}
  width={941}
/>

Let's name this sheet **Websites**.

I'll also want to keep a log of all of the requests, so the script will create a new sheet tab for each website entered into sheet 1.

Something like this:

<Image
  alt="Uptime robot google sheets log page"
  src="/images/posts/build-website-uptime-bot/Uptime-Robot-Google-Sheets-log-page.png"
  caption="I'll also want to keep a log of all of the requests, so the script will create a new sheet tab for each website entered into sheet 1"
  height={344}
  width={658}
/>

Now, to create a script that is attached to our sheet, we'll click `Tools => Script Editor` and a new tab will open up. Let's rename the file `checkWebsiteStatus.gs`.

Let's start by setting a few variables and creating the function.

```js
// The timezone your logs will be using
var TIMEZONE = 'CST';
// The date format for the logs
var DATE_FORMAT = 'MM-dd-yyyy HH:mm:ss';
// The max amount of rows you want to keep in each log
var MAX_ROWS = 10000;

function checkWebsiteStatus() { }
```

Next, we need to set a few more variables to get the list of websites we need to ping.

```js
function checkWebsiteStatus() {

  // Get the spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // Our website list sheet
  var sheet = ss.getSheetByName("Websites");

  // Get the last row
  var lrow = sheet.getLastRow();

  // Select the range that contains the site data.
  var sheetData = sheet.getRange(2, 1, lrow - 1, 2);

  // Format the site data into an array of objects
  var siteData = sheetData.getValues().map(function (member) {
    return {
      url: member[0],
      email: member[1],
    };
  });

  // Set our logging breakdown header that displays the total uptime
  var sheetBreakdownHeaders = [['Uptime']];

  // Set our logging page headers
  var sheetHeaders = [['Website', 'Timestamp', 'Status']];
}
```

Now the `siteData` variable contains a nice array of objects that will be easy to loop through and ping our sites. It will look like this:

```js
[
  {
    url: 'https://jjwilly.com',
    email: 'justin@jjwilly.com'
  }
]
```

Let's write the function that will create a new logging sheet for each site entry (if it needs to).

```js
function createSheet(site) {

  // Assign a sheet name
  site.sheetName = site.url.split('//').pop();

  // Only create this sheet if it doesn't exist yet
  if (!ss.getSheetByName(site.sheetName)) {

    ss.insertSheet(site.sheetName);
    var currentSheet = ss.getSheetByName(site.sheetName);

    // Set the breakdown headers, format them and set the values
    currentSheet
      .getRange(1, sheetHeaders[0].length + 2, sheetBreakdownHeaders.length, 1)
      .setFontWeight('bold')
      .setBackground('lightgray')
      .setValues(sheetBreakdownHeaders);

    // Do the same for the regular headers
    currentSheet
      .getRange(1, 1, 1, sheetHeaders[0].length)
      .setFontWeight('bold')
      .setBackground('lightgray')
      .setValues(sheetHeaders);

    // Freeze the headers for easier readability
    currentSheet.setFrozenRows(sheetBreakdownHeaders.length);
  }
  return site;
}
```

Next we need a function to ping the sites.

```js
function checkIfSitesAreUp() {

  // loop through all site data
  for (var i = 0; i < siteData.length; i++) {

    // here's where we will create our log sheet
    var currentSite = createSheet(siteData[i]);

    // attempt to ping the site
    try {
      currentSite.response = UrlFetchApp.fetch(currentSite.url);
      currentSite.responseCode = currentSite.response.getResponseCode();
      if (currentSite.responseCode && currentSite.responseCode != 200) {
        websiteDown(currentSite, 'Response Code: ' + currentSite.responseCode);
      }
    } catch (err) {
      currentSite.error = err;
      currentSite.responseCode = -1;
      websiteDown(currentSite, currentSite.error);
    }

    insertLogEntry(currentSite, currentSite.responseCode || currentSite.error);

  }

  cleanUp();
}
```

You may have noticed a few new function calls in there. Here's our `websiteDown` function that will be called in case of a failed ping attempt:

```js
// Sends an email with error details
function websiteDown(siteData, error) {
  MailApp.sendEmail(siteData.email, 'One of your websites is down.', siteData.url + ' is currently down. \n\nHere is the error: \n\n' + error);
}
```

And here's our logging function:

```js
function insertLogEntry(site, status) {
  var timestamp =  Utilities.formatDate(new Date(), TIMEZONE, DATE_FORMAT);
  var currentSheet = ss.getSheetByName(site.sheetName);
  currentSheet
    .getRange(currentSheet.getLastRow() + 1, 1, 1, 3)
    .setValues([[site.url, timestamp, status]]);
}
```

Now let's create a function that will clean up the sheets a bit.

```js
function cleanUp() {

  // Loop through each sheet
  ss.getSheets().forEach(function (sheet) {

    // Don't need to modify the 'Websites' sheet
    if (sheet.getName() != 'Websites') {

      // Rotate logs
      rotateLogs(sheet);

      // Get total uptime percentage
      printTotalUptimePercentage(sheet);
    }

    // Auto-resize all columns in all sheets
    sheet.autoResizeColumns(1, sheet.getLastColumn());

  });
}
```

Of course, the logs will need to be rotated. This is not 100% necessary, but I don't want these logs getting out of control.

```js
function rotateLogs(sheet) {
  var startAt = sheetBreakdownHeaders.length;
  var numRows = sheet.getLastRow() - startAt;
  if (numRows > MAX_ROWS) {
    sheet.deleteRows(startAt + 1, numRows - MAX_ROWS);
  }
}
```

Lastly, we need to print the total uptime percentage on each log sheet. We could just create a formula to calculate this for us, but I prefer to do it this way:

```js
function printTotalUptimePercentage(sheet) {

  // Identifies the row where the data begins
  var startAt = sheetBreakdownHeaders.length;

  // Count the number of rows that have data
  var numRows = sheet.getLastRow() - startAt;

  // Get all of the status codes in an array
  var allData = sheet
    .getRange(startAt + 1, sheetHeaders[0].indexOf('Status') + 1, Math.min(numRows, MAX_ROWS), 1)
    .setNumberFormat('0')
    .getValues()
    .map(function (member) { return member[0]; });

  // Calculate the total uptime percentage
  var totalUptime = ((allData.filter(function (member) { return member == 200; }).length / allData.length) * 100).toFixed(2) + '%';

  // Insert the results onto each sheet
  sheet.getRange(1, sheetHeaders[0].length + 3, 1, 1).setValue(totalUptime);
}
```

<h2 id="the-code">The Final Code:</h2>

```js:checkWebsiteStatus.gs
/**
 * Change these variables to your liking
 */
var TIMEZONE = 'CST';
var DATE_FORMAT = 'MM-dd-yyyy HH:mm:ss';
var MAX_ROWS = 10000;

function checkWebsiteStatus() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Websites");
  var lrow = sheet.getLastRow();
  var sheetData = sheet.getRange(2, 1, lrow - 1, 2);
  var siteData = sheetData.getValues().map(function (member) {
    return {
      url: member[0],
      email: member[1],
    };
  });
  var sheetBreakdownHeaders = [['Uptime']];
  var sheetHeaders = [['Website', 'Timestamp', 'Status']];

  checkIfSitesAreUp();

  function createSheet(site) {
    site.sheetName = site.url.split('//').pop();
    if (!ss.getSheetByName(site.sheetName)) {
      ss.insertSheet(site.sheetName);
      var currentSheet = ss.getSheetByName(site.sheetName);
      currentSheet
        .getRange(1, sheetHeaders[0].length + 2, sheetBreakdownHeaders.length, 1)
        .setFontWeight('bold')
        .setBackground('lightgray')
        .setValues(sheetBreakdownHeaders);

      currentSheet
        .getRange(1, 1, 1, sheetHeaders[0].length)
        .setFontWeight('bold')
        .setBackground('lightgray')
        .setValues(sheetHeaders);

      currentSheet.setFrozenRows(sheetBreakdownHeaders.length);
    }
    return site;
  }

  function checkIfSitesAreUp() {
    for (var i = 0; i < siteData.length; i++) {

      var currentSite = createSheet(siteData[i]);

      try {
        currentSite.response = UrlFetchApp.fetch(currentSite.url);
        currentSite.responseCode = currentSite.response.getResponseCode();
        if (currentSite.responseCode && currentSite.responseCode != 200) {
          websiteDown(currentSite, 'Response Code: ' + currentSite.responseCode);
        }
      } catch (err) {
        currentSite.error = err;
        currentSite.responseCode = -1;
        websiteDown(currentSite, currentSite.error);
      }

      insertLogEntry(currentSite, currentSite.responseCode || currentSite.error);

    }

    cleanUp();
  }

  function websiteDown(siteData, error) {
    MailApp.sendEmail(siteData.email, 'One of your websites is down.', siteData.url + ' is currently down. \n\nHere is the error: \n\n' + error);
  }

  function insertLogEntry(site, status) {
    var timestamp =  Utilities.formatDate(new Date(), TIMEZONE, DATE_FORMAT);
    var currentSheet = ss.getSheetByName(site.sheetName);
    currentSheet
      .getRange(currentSheet.getLastRow() + 1, 1, 1, 3)
      .setValues([[site.url, timestamp, status]]);
  }

  function cleanUp() {
    ss.getSheets().forEach(function (sheet) {

      if (sheet.getName() != 'Websites') {

        // Rotate logs
        rotateLogs(sheet);

        // Get total uptime percentage
        printTotalUptimePercentage(sheet);
      }

      // Auto-resize all columns in all sheets
      sheet.autoResizeColumns(1, sheet.getLastColumn());

    });
  }

  function rotateLogs(sheet) {
    var startAt = sheetBreakdownHeaders.length;
    var numRows = sheet.getLastRow() - startAt;
    if (numRows > MAX_ROWS) {
      sheet.deleteRows(startAt + 1, numRows - MAX_ROWS);
    }
  }

  function printTotalUptimePercentage(sheet) {
    var startAt = sheetBreakdownHeaders.length;
    var numRows = sheet.getLastRow() - startAt;
    var allData = sheet
      .getRange(startAt + 1, sheetHeaders[0].indexOf('Status') + 1, Math.min(numRows, MAX_ROWS), 1)
      .setNumberFormat('0')
      .getValues()
      .map(function (member) { return member[0]; });
    var totalUptime = ((allData.filter(function (member) { return member == 200; }).length / allData.length) * 100).toFixed(2) + '%';
    sheet.getRange(1, sheetHeaders[0].length + 3, 1, 1).setValue(totalUptime);
  }

}
```

It's not perfect, but I'm not going to complain. It's free.


---
title: jQuery DataTables Column Totals that Update with Filtering
date: 6/24/2018
author: Justin Williamson
summary: I love jQuery DataTables. If you're not familiar with this library, it's basically tables on steroids. I use it often just for it's built-in sorting, filtering...
published: true
---

I love [jQuery DataTables](https://datatables.net/). If you're not familiar with this library, it's basically tables on steroids. I use it often just for it's built-in sorting, filtering, download buttons and nice api. It's always a real quick way to get fully-functional tables on the page.

Recently, I had a client request to add footer totals to the bottom of each table. *Whoa*, I thought, *Wouldn't it be nice if they updated when you searched?*.

Yeah, I mean, wouldn't it suck if they didn't?

<iframe
    width="100%"
    height="850"
    src="https://jsfiddle.net/jjwilly16/31dwzL50/embedded/result,js,html/dark/"
    allowFullScreen
    frameBorder="0"
>
</iframe>


---
title: Fabric.js 2.0 breakWords Implementation
date: 6/11/2018
author: Justin Williamson
summary: One of my favorite fabric.js hacks was the breakWords function. This would allow Textbox objects to hold a maximum width by forcing a line break when a long word...
published: true
---

> EDIT 8/26/2021 - The splitByGrapheme option seems to be working great in v4 and renders all of this shit unnecessary. Woohoo!

> EDIT 6/3/2019 - It looks like the [splitByGrapheme option has been fixed](https://github.com/fabricjs/fabric.js/commit/ce23118378fb0ebefcd9d25f3402909329691787) and is working in fabric 3.0. It is an alternate approach than the one shown here.

One of my favorite [fabric.js](https://github.com/fabricjs/fabric.js) hacks was the [breakWords function](https://stackoverflow.com/a/37883753/5432480). This would allow Textbox objects to hold a maximum width by forcing a line break when a long word could not be contained.

Unfortunately this functionality was broken in fabric 2.0. I busted my ass a bit trying to figure this out - here's what I came up with:

<iframe
    width="100%"
    height="500"
    src="https://jsfiddle.net/jjwilly16/sd03g4t4/embedded/result,js,html/dark/"
    allowFullScreen
    frameBorder="0"
></iframe>

Note that there are still some problems [as pointed out here](https://github.com/fabricjs/fabric.js/issues/2376#issuecomment-396934943). It's a start, though.


---
title: Scale and Center Images with Imagemagick
date: 6/2/2018
author: Justin Williamson
summary: Handling user-uploaded images can be a pain in the ass - especially when you have image requirements. My standard procedure used to involve loading these...
published: true
---

Handling user-uploaded images can be a pain in the ass - especially when you have image requirements. My standard procedure used to involve loading these massive images into a cropping plugin with a set aspect ratio, but I like to avoid this when I can.

Recently, I started to experiment with a new way to handle these uploads without any user intervention. It isn't perfect and isn't always the right solution, but it's a hell of a lot cleaner.

The idea is to scale the image down just enough to fit inside the available space while preserving the aspect ratio, center it, and make any excess remaining space transparent (or a color to match the background of the document).

Let's put that into action. Let's say we have a 300x300px space available to fit our image...

<Image
    alt="300x300 Placeholder"
    src="/images/posts/scale-and-center-images-imagemagick/300x300.png"
    height={300}
    width={300}
/>

...and an image with a troublesome aspect ratio...

<Image
    alt="My beautiful daughter"
    src="/images/posts/scale-and-center-images-imagemagick/monroe-playground.png"
    height={366}
    width={650}
/>

Here is the [ImageMagick](https://imagemagick.org/) command in all it's glory...

```bash
convert inputImage.png -scale 300x300 png:- | convert - -gravity Center -background black -auto-orient -extent 300x300 done.png
```

Let's walk through this real quick:

1. First we run the [scale command](https://www.imagemagick.org/script/convert.php), passing our dimensions. This will scale our image to fit inside the area without losing our aspect ratio. The png:- format will pipe the image to stdout as a png.
2. For the second command, we again run convert, using - to pass the image to stdin.
3. The [gravity option](https://www.imagemagick.org/script/command-line-options.php#gravity) will center the image for us. You can also align the image to the left or right with West or East.
4. The [background option](https://www.imagemagick.org/script/command-line-options.php#background) obviously sets our background color and [auto-orient](https://www.imagemagick.org/script/command-line-options.php#auto-orient) makes sure the image is oriented correctly.
5. The [extent option](https://www.imagemagick.org/script/command-line-options.php#extent) will size the image to the given area, while allowing the background to change color.

Here is the final image (I made the background black so you can see the output).

<Image
    alt="Processed image"
    src="/images/posts/scale-and-center-images-imagemagick/done.png"
    height={300}
    width={300}
/>

Of course, in a web app we need to translate this to something we can use on the server. Here is how it looks in [Node.js](https://nodejs.org), using the excellent [gm library](https://github.com/aheckmann/gm):

```js
const gm = require('gm').subClass({
    imageMagick: true,
});
const height = 300;
const width = 300;

new Promise((resolve, reject) =>
    gm(req.file.buffer)
        .scale(width, height)
        .toBuffer('PNG', (err, buffer) => {
            if (err) reject(err);
            resolve(buffer);
        })
)
    .then(bufferedImage =>
        new Promise((resolve, reject) =>
            gm(bufferedImage)
                .gravity('Center')
                .background('black') // Probably 'transparent' most of the time
                .autoOrient()
                .extent(width, height)
                .toBuffer('PNG', (err, buffer) => {
                    if (err) reject(err);
                    resolve(buffer);
                })
        );
    )
    .then(finishedImage => {
        // DO STUFF WITH FINISHED IMAGE HERE
    })
    .catch(err => {
        // Handle errors
    });
```

Also, you should probably check that the user's image isn't smaller than the predefined dimensions. If that's the case, I would probably warn the user or maybe just skip the scale command.
