Creative laziness: Python, Selenium and Timesheets
As a routine and very common practice many companies I have worked with make salaried employees to fill out timesheets. The reason for that is usually taxes. Having salaried workers filling out timesheet can be used for a R&D (Research and Development) tax credit, as well as company can use this data for more accurate separation of the capital and operational expenses.
Timesheets have been around for some time (since 19 century), and used to be called Time Books and later time cards.
The sole and original purpose of the worker’s timesheet is statistical. Timesheet is a record (usually in tabular format) of how many hours worker has spent on a given day performing his job.
As someone with a decent retail background, I have seen a large variations of the timesheets from rough cardboard-ish time cards that you need to stick into industrial analog clock machine, capable of snapping your finger off to more advanced, smaller electronic ones, and lastly digitalized spreadsheets accessible over the WEB that act as a small gears in the enterprise infrastructures. We are going to talk about the latter.
As every software developer should be, I’m extremely lazy. So I started thinking how I can do my timesheet without actually doing it (that is the ideal goal in general, to complete a lot of tasks without actually doing anything).
My timesheet was part of the Project Web App (previously known as Project Web Access, Microsoft PWA was renamed Project Web App in Microsoft Project 2010, which is part of the Microsoft Office suite of productivity software, I will refer to it as PWA from now on) which is, as you have guessed, a Web Application. So the obvious choice for the automation here was Selenium. As for the programming language, I decided to go with Python. Not because I’m a professional Python developer but because of the learning opportunity. I have been looking at Python for a while and right now here is the chance to finally start using it. So here is also a small disclaimer: If you find something obviously stupid (or maybe not so obviously) in the code snippets, that doesn’t sit right with Python conventions, or just not right, please feel free to pass your argumentative judgement. I will be grateful.
And so I did. Right now I have a script that launches browser on schedule and fills out my timesheet for me and submits it. Bam! :) If you have ever worked with Selenium, you know how extremely easy and intuitive it is. I will not go into the selenium too much, instead I will talk about potential caveats and difficulties that I had to overcome.
About the browser, I went with Firefox. Looks like it’s the default choice and Firefox driver is there with Python Selenium out of the box. (If i remember correctly I would have to download something to switch to chrome, i didn’t bother)
Authentication
The very first problem I have encountered was authentication. Our PWA was behind basic HTTP Authentication (you know.. auth headers, that annoying popup in the browser asking you to enter your credentials etc.)
As a good samaritan, of course, I was planing to share the result of this small project with my co-workers, so storing passwords in configuration files was out of the question. So after some googling I came across a a nice Firefox add-on called AutoAuth. This small add-on will remember your basic authentication credentials and if you open that page again it will take care of the pop up for you.
Good. Now what we will do about Firefox user profiles. Every time you run a Selenium script it creates new blank Firefox user profile, and adding add-ons on the fly is very tricky and looks nasty. Fortunately there is a simpler way: just tell selenium to use your default user profile, where you installed and tested AutoAuth add-on initially :)
Done! Now if you will launch that script is it will open your timesheet and AutoAuth will take care of the basic authentication and you are free to proceed.
Editing spreadsheets
PWA is using something called Microsoft Smart Tables and Microsoft Ajax Webforms, all these things put together look pretty much like an MS Excel spreadsheet with basic MS Excel capabilities.
Now how it works is when you select a cell and hit any keyboard key or double-click on the cell, floating input element appears on the top of the cell and keyboard keys are sent to the input. When you select another cell, the cell you just “edited” triggers “temporary” form submission and cell’s html displays formatted hours you entered. The input element is only one on the page and every time a cell triggers an input it just floats over there places itself on top if the cell and gathers all input information. Heavy JavaScript load handle the juggling with attributes, validation etc.
Selenium did not like all that… At all.
It took me hours to figure out the sequence of actions selenium needed to perform in order to activate a cell and trigger the appearance of the floating input element.
So the answer to this problem is Double-click+Click. In selenium you will need to use ActionChains for the double-click and click is “native” and simple element.click():
I think all this info should be enough for now for anyone to get started with Selenium and Python automations.
For this particular project the next step will be headless script run and text-message or email status update. This where I will have to summon all the creativity I have to come up with something very simple and easy configurable. And because of the complexity there will be a whole different post about running Selenium automation applications that work with rich web apps headlessly (by the way PhantomJS does not work with PWA. It just cant render the tables).
