Automating processes in a small company using free utilities

Introduction

I want to share some of the automation that I've written for my company to help with some of the back office processing. The reason that I wrote this automation myself is that I looked at numerous offerings on the market, and none fit exactly what we needed. Once I made that realization, I came up with automation using tools we already own or that are free to use, specifically:

Google Sheets, including Google Script
Windows Batch scripting
Perl scripting
Bookmarklets in JavaScript
Quickbooks IIF files

It looks like a lot (and it may very well be a lot), but for my background, this path required no more work than learning an off-the-shelf product.

The Payroll Process

Not everything is automated. We do have automated time sheets, but there are several pieces of data that need to be manually combined and calculated. Where we started with the automation was the spreadsheet in which this data was being captured. The data was already in a straightforward format, so it just needed to be cleaned up a little. Specifically, some of the entries just needed to have some specific keywords, and we put the spreadsheet into Google Sheets. We made the decision to move from Excel so that we could always access the spreadsheet and also because I personally enjoy programming in JavaScript (Google Scripting) than in Visual Basic (MS Office Scripting).

The process starts with my CFO filling out the Google Sheet using data from the few different sources that we have. She then presses the "Create IIF" menu item (created with a Google Script implementation of the onOpen() function). That creates both an IIF file for QuickBooks (that actually needs a little more processing - more on that later) and the a file containing the JavaScript for a bookmarklet that is used later in the process. It also sends an email out to each

The IIF file has to then be downloaded into a specific directory which contains a windows .BAT file, a shell script and a Perl script. The .BAT file is simply used to call the shell script, which calls the Perl script to actually parse the file. That Perl script mainly just adds TAB characters in the correct places to match the correct IIF format. I wasn't able to make the TAB characters actually stay in the downloaded file via Google Script, so this is the workaround I came up with.

The contents of the JavaScript file are the implementation of the "SetACH" bookmarklet that has already been defined in her browser. That bookmarklet is used to fill in the appropriate fields on our bank's ACH web page. To do this, my CFO logs into our bank and navigates to the appropriate page, then clicks the SetACH bookmarklet, which fills in all of the appropriate values.

Better Than a Packaged Product?

In a word, Yes. Every COTS (commercial off-the shelf) product requires some amount of configuration, which, in my experience, would have taken more time than creating the custom solution I have. Additionally, all of the COTS products I investigated included tons of capabilities that we simply don't need or that don't specifically work for us. As you can see, we only needed a tiny bit of functionality, and I didn't want to pay for a bevy of additional features that we wouldn't use. Specifically, I used the same evaluation process that I help customers with every day. I identified the specific functions I needed to perform and reports that I needed to generate, and evaluated the choices based on those. I also evaluated the existing tools and my development expertise with each to determine that I was capable of producing a working solution. We've now had this automation in place for three years, and it has required minimal upkeep.

Using jQuery in Cognos 10.x and DASH

For most of what you need, IBM has a great article on how to incorporate jQuery into your Cognos reports here:

https://www.ibm.com/developerworks/library/ba-pp-reporting-advanced-report-design-page647/index.html

However, there are some painful issues which vary based on whether you access reports through DASH/JazzSM or go directly to Cognos Reporting. Specifically, it appears that DASH adds some additional capabilities to the environment, possibly through the use of Dojo. Or it's possible that these capabilities are stripped off when logging directly into Cognos. Whatever the case, here are the caveats:

DASH/JazzSM adds some additional container elements to the HTML report such that you cannot use the browser "Print" function to print a multi-page report. All you get is the first page of the report, and this happens on all browsers. To get around this, I opened a new window and set the contents to be only the container enclosing the report. Then THAT window can be printed via the browser.

Logging directly into Cognos "removes" several methods that should exist in IE 11. For example, it undefines the Object.keys() method. So if any JavaScript you're using references that method, you'll need to use a workaround like this one:

https://stackoverflow.com/questions/18912932/object-keys-not-working-in-internet-explorer

Additionally when logging directly into Cognos, the HTMLCanvasElement.getContext() method is not defined. Personally, I got around this by commenting out any references to that method and it worked in my case. Unfortunately, I couldn't find any "good" solution to this problem, so this is all I've got.

Happy coding!