Tuesday, June 23, 2015

A Stable and Maintainable automation framework (use Protractor for non Angular-JS web application)

A good automation framework should be:

  • Easy to write
  • Easy to maintain
  • Fast 
  •  Stable

The current automation framework used for the system I am working on fails all those criteria, it is not easy to read and maintain – one UI element change requires full text search to fix all impacted code; it is so slow that it is scheduled to be run only once; it is not stable, the usual reaction to its failure is, “let us wait for another run.”

I set out to rewrite the automation framework using Protractor. Protractor was designed for testing AngularJS web applications, my application was not written in AngularJS, so it took me a lot of trouble to make it fast and stable. 

The resulting architecture is:


Test cases

The top layer is test cases, the focus of the test cases is readability: it is readable to an even non-technical person.  The reason to stress readability  is that the framework can be leveraged to write feature acceptance tests, a non-technical person (a product manager or a user) can read the test case and verify if it complies with the requirements.

My system is a ticket handling system, one test case reads like:


Domain objects

The middle layer is domain objects. Domain objects focus on troubleshooting.

Every domain method is about one business action. Some business actions are repeatable, for example, one can save a ticket multiple times; but some business actions are not repeatable, for example, after a ticket is assigned, it can’t be assigned to another person – it needs to be first unassigned.

The test case can fail at any business action, when it fails at one action, to troubleshoot what happens, you do not want to start all over again, you’d want to start from the failed business action. Because every business action is encapsulated into one method, you can comment out the previous successful methods, and start from the failed method.

Every business action works on a business object. Every method has two parameters; the first parameter is a map for defining properties of the business objects, the second parameter is a map for defining how to search for the business objects.

Domain objects work on the UI element, it locates UI elements through ID, name, xpath or text, and apply actions on them. The top layer of test cases does not know UI elements. 

To start from the failed business action, comment out the previous successful method, and for the fail method, add the second parameter to search for the business object:

Writing test cases is like assembling domain objects, over the time, the domain objects will be enriched to cover most functions, and writing test cases will be like assembling domain objects, easy and efficient.


Element Handler

The bottom layer is the messiest one: it handles UI elements.  For AngularJS application, Protrator has a mechanism to wait for the page to be loaded and settled down (so I heard, I’ve never tried Protractor on AngularJS applications),  but my application was not written in AngularJS, so I have to work out on my own way to test if a UI element is visible, is enabled or is clickable.

This layer has many methods to make handling UI element easy:


You might wonder why I have methods for different locators (byID, byName, byXPath etc), why I didn’t make the automation framework more tolerant, and try to locate an element using different locators. Since UI element changes frequently, on one release, one UI element’s name is “ticketNum”, on the next release, its name changed and now its ID become “ticketNum”. If the framework first tries to locate by name, and then by id, then the code doesn’t have to be changed.
Apparently, this kind of change happens quite often,  so it tries to be tolerant. But the problem is that it slows down the test case. Take clicking a button for example, to make clicking stable, it has to go through these steps:

  1.   First locate element: by.Name(“”) (or by other locators)
  2.  Keep polling if the element is visible until it is visible or timeout
  3.  Keep polling if the element is enabled until it is visible or timeout
  4.   Click it
  5.   Catch errors throwing out from 4), and repeat clicking

If the framework first tries by name, it needs to go through the second step before it determines the element doesn't exist; then it will try by id. The time on the second step will be wasted. You might think such waste is ignorable in the name of making the framework more tolerant of UI changes, but little time here and there soon adds up, and soon no longer the time wasted is ignorable. Also my application is really clunky, it can fail for all kinds of reasons, so if it fails, I’d like it to fail fast.

The second reason is that all UI locating happens inside the domain objects, if UI locators change, it is easy to figure out what is impacted.


Test Case Gluer

There is a Global object used as the test case gluer. Some test cases are inter-dependent: the objects created by the previous test case is used in the following test cases. The Global object is used to store such objects.

The Global objects is also used to record test cases’ starting time. Every case’s beforeEach method will invoke util.initCase to record start time:


    var caseName=replaceSpaceWithDash(jasmine.getEnv().currentSpec.description);

    global[caseName]=new moment();




And very test case’s afterEach method will invoke util.finsihCase  to calculate and print out each case's run time as well as the total run time:


    var caseName=replaceSpaceWithDash(jasmine.getEnv().currentSpec.description);

    var endTime=new moment();

    console.log("################The run time for "+caseName +" is "+endTime.diff(global[caseName], "minutes")+" minutes");

    console.log("################The total run time is "+endTime.diff(global.starTime, "minutes")+" minutes");

Error Handling

Because the test cases are inter-dependent, if one test case fails, the rest should not be started. Protrator has an open request to track this issue: https://github.com/jasmine/jasmine/issues/414.
Before this is fixed by Protractor, I solved it in util.initCase:


ignoreSynchronization = true;

prototype.bailFast = function() {
var env = this;
env.afterEach(function() {
if (!this.results().passed()) {
env.specFilter = function(spec) {
return false;



Upon failure, the framework will take a picture of the moment and store the picture, this is done in util.finsihCase:

var caseName=replaceSpaceWithDash(jasmine.getEnv().currentSpec.description);
    var passed = jasmine.getEnv().currentSpec.results().passed();
    var screenshotsDir = path.resolve(reportDir + global.starTime.format('MMDD_HHmm'));

    var self=this;
    if (!passed) {

        co(function *(){
            if (!fs.existsSync(screenshotsDir)) {

            var file = path.resolve(screenshotsDir + '/' + caseName + .png');
            browser.takeScreenshot().then(function (png) {
                console.log('Writing screenshot to file ' + file);
                fs.writeFileSync(file, png, {encoding: 'base64'}, console.log);
            }, console.log);

Tuesday, April 7, 2015

Create a pivot table from another pivot table

Excel doesn’t support creating a pivot table from another pivot table directly, but it is very easy to do so using named range. 

Please refer to my previous blog about how to use named ranges. The difference is that you might want to change the last parameter – the range width – to include more columns in the named range:

Now, from the menu tab, “Pivot Table”, input the named range:

That is all!

Create a scatter chart from a pivot table

Excel doesn’t allow you to create scatter chart from a pivot table:

To overcome this, you can use the following approach:

  1.  Create a scatter chart using two columns of the pivot table as x, y series
  2.  Create two named ranges referring to the two columns
  3.  Replace the x, y series of the scatter chart with the named range

Create a scatter chart using two columns of the pivot table as x, y series

  1. Menu tab: Insert, choose “scatter” chart
  2.  Right click on the chart, select menu “select data”
  3.  Edit series

You do not need to be precise about X, Y series, just choose a few values from the two columns of the pivot table. later, you will replace these two series with named ranges.

Create two named ranges referring to the two columns

1.       Menu tab: formula, choose “named range”
2.       Create a new named range referring to the first column of the pivot table
Name: javaPivotName
Formula: =OFFSET(javaChart!$A$4,0,0,COUNTA(javaChart!$A$4:$A$10000)-1,1)

3.       Create a second named range use the first range as the reference

Replace the x, y series of the scatter chart with the named range

Click on a dot in the scatter chart, and replace the x, y series with the two named ranges:

  1. Replace “$A$4:$A$12” with the first named range
  2. Replace “$B$4:$B$16” with the second named range

Automate the process in a macro

If you try to automate this in a macro, you will encounter something tricky. To create such a macro, you use record to capture creating a named range, which will generate code such as this:

  ActiveWorkbook.Names.Add Name:="javaPivotName", RefersToR1C1:= _

Notice, it uses RC style to reference cells.

Being a good developer, you of course do not want to use the absolute row, column number, you change the code to:

ActiveWorkbook.Names.Add Name:=xRangeName, RefersToR1C1:= _
        "=OFFSET($" + xColumn + "$" + xRow + ",0,0,COUNTA($" + xColumn + "$" + xRow + ":$" + xColumn + "$10000)-1,1)"

Then you try to replace x, y series in the scatter chart with named range, here is when you encounter the tricky part: Excel thinks your name range is invalid, if you check the named range, its definition is:

Notice somehow Excel adds single quotes around cell addresses.

Apparently it is because two ways of referencing cell addresses are mixed together, you should stick to one way. So the solution is to change

ActiveWorkbook.Names.Add Name:=xRangeName, RefersToR1C1:= _
        "=OFFSET($" + xColumn + "$" + xRow + ",0,0,COUNTA($" + xColumn + "$" + xRow + ":$" + xColumn + "$10000)-1,1)"


ActiveWorkbook.Names.Add Name:=xRangeName, RefersTo:= _
        "=OFFSET($" + xColumn + "$" + xRow + ",0,0,COUNTA($" + xColumn + "$" + xRow + ":$" + xColumn + "$10000)-1,1)"