Extractng Google sites tables to Excel

In past few months I had to use tables in Google sites to gather issues and other data, replacing local spread sheets already in place. Though this is far from ideal issue tracking systems, it is absolutely great at what it is promising; fast set-up; easy to share and collaborate; good and clean UI . They worked great.

When I had to extract data, there was no easy way - at least I didn't find any. This little VBScript routine saved the day. I had to take short cuts to save time (to avoid all the authentication issues, I first open the site in IE and then run this. It does not use the same instance but new instance already have access to the site. And gave a 4 second sleep to complete the page load instead of waiting for an event).

Note that ~ is used as the delimiter to allow commas in data and non-breakable spaces in the table are replaced by regular spaces.

set FSO = CreateObject("Scripting.FileSystemObject")
Set IE = WScript.CreateObject("InternetExplorer.Application", "IE_")
IE.Visible = True

  recIndex=1
  set outFile = FSO.CreateTextFile(".\outfile.csv")
  IE.Navigate "https://sites.google.com/site/<mysite/mypage>"
  wscript.sleep 4000
  tmpSTR = IE.Document.DocumentElement.innerhtml

  set objTable = IE.Document.GetElementByID("goog-ws-list-table")
  set colRow = objTable.GetElementsByTagName("tr")
  For each objRow in colRow
    set colFields = objRow.GetElementsByTagName("TD")
    If Not isnull(colFields) then recIndex = recIndex + 1
    outfile.Write recIndex
    For each objField in colFields
       outfile.Write "~ " & trim(replace(objField.InnerHTML,"&nbsp;",chr(32),1,-1)) 
    Next
    outFile.Writeline
  Next
outfile.Close
MsgBox "Done"

SoapUI assertions using XQurey/Xpath for numeric comparisons

For new test steps, SoapUI adds a single assertion by default. This verifies that the response is actually a SOAP response. Other than this, SoapUI has a rich collection of assertion types to verify the response. One of them is XQurey assertions, work closely with Xpath. Using XQuery assertions not only make verification automatic but also improves the accuracy. Here are a few examples on how to use especially in numeric verifications.

Say your response contains the following XML structure.

     
<items>
  <item>
    <price>1000</price>
    <date>12-12-12</date>
  </item>
  <item>
    <price>110</price>
    <date>10-10-10</date>
  </item>
  <item>
    <price>125</price>
    <date>Sat Aug 11 12:49:05 NZST 2012</date>
  </item>
</items>

• Counting number of item nodes using XPath assertion is straight forward.

               count(//item)

       which is going to be 3.

• Compare each price value.

• Calculate the total of price values.

Setting current date and other dynamic values in SoapUI properties

One less known but very powerful and interesting features of SoapUI is the ability to use scripting inside property values. During property substitutions where ever they are used, in request or in side assertions, script is executed and the value is substituted. They take the general form of

${=(script)}

For example, if you want to generate a random number in a property, create a property in the TestCase, say Random and set the value to

${=(int)Math.random()*1000}
(Since Math.random gives a random value between 0 and 1, the value of this expression is going to be a random number between 1 and 1000)

In the request, if you specify

${#TestCase#Random}
It is going to be a random number between 1 and 1000.

Taking a more complex (and probably useful) example, following is going to generate the current date in form of DD-MM-YY.

${=Calendar.instance.get(Calendar.DATE)+"-"+(int)(Calendar.instance.get(Calendar.MONTH)+1)+"-"+Calendar.instance.get(Calendar.YEAR)}

Note that months are starting from 0, so you have to add one to the month.

Or, if the date time string is desirable,

${=Calendar.instance.getTime()}

Running mavenized JUnit Tests from command line

It has been a while again - I was doing lot of work but habit of blogging faded away. Fortunately I started writing things down, specially simple commands that are essential as a tester... Hopefully I will rapidly publish them.

I wasn't a fan of mavenizing java - at least at the start- but it has changed. The thing with these tools is the relative complexity for smaller scale projects. As a tester, I feel this more since we need small and quick JUnit or Java test programs often.

 If the project is mavenized, it is impossible to build the java path manually. You can export the classpath using dependency plugin, and set the classpath. Go to the project folder and give the command,

mvn dependency:build-classpath -Dmdep.outputFile=cp.txt

This will create the file cp.txt with all the dependencies. Now add the output folder of your project, this is usually,

%ProjectDir%\output\classes in a mavenized project. Don’t forget to add the semicolon!

Now open the text file from notepad, press Ctrl+A and then Ctrl+C to copy everything. Go to command prompt and type

Set CLASSPATH=

Without pressing Enter, right click the mouse and select paste. That will copy all the contents of the file in to the command. Now press enter and the classpath is set.

To verify, type

echo %CLASSPATH%

This should print all the contents you had in cp.txt. Now run the junit tests with command,

java org.junit.runner.JUnitCore MyTestClass


Alternatively, you can use the command line switch -classpath too but the command line is going to be very long!

java -classpath (content of cp.txt) org.junit.runner.JUnitCore MyTestClass

Why not make SoapUI step names more than descriptive?

I was continuing my journey with SoapUI. One of the challenges was to develop a test infrastructure with a low maintenance overhead. I am doing contracting work once it is over, in-house staff should be able to maintain it.

I was using Groovy script only when it is absolutely necessary, and setting up the custom content type was such task. Content type was read from a property, but I thought setting the property manually for every content type is not going to be reliable. It is easy to lose or change it without ever noticing it.

SoapUI suggest to use descriptive names for test steps. This is a very practical and useful suggestion. But can we go one step further? This is a technique I started using even in manual testing environments. You can include various data about the entity in different fields. The name can contain some useful data. If it is a database table, use values from a numeric column when testing a text column, so that you can verify them easily.

So, in this case I thought of including the content type in the name itself, and reading it from step name. There was an additional advantage of not having to modify the script in anyway when copying it to a new test. Just change the name. The following script read the name and use the string preceded by "-" to set the content type.

Re-usable Groovy scripts for SoapUI

For couple of days I could not login to blog - but with kind of frequency I was blogging in last year, it should not make any difference.

Though I stared to use SoapUI with some reluctance (mainly due to the fact that the project I am working is a REST system and I thought SoapUI's support for REST cannot be that great) I made good progress and became more and more comfortable with it.

One test I had in mind was verifying response headers. Using the Groovy script was the most straight forward method to do that. However, in all the examples I found, Groovy script was referring to the step by name to access the response data. This meant editing the script every time I used it in a different place. SoapUI pro has a scripting library but I was using the free version.

Then I thought the best way is to write the script to get data from the previous step, regardless where it is. In this way, I don't need to change the script every time I use, but just to place it write below the step where I need to test the headers. This was the script I came up with.

SoapUI: Verifying a url in the content of a page

We had a little discussion on SoapUI test cases and one question was could we extract an url from the response and use load the content from the page it is pointing to. This was important as we were testing a RESTful framework. Task is trivial when using a programming/scripting language (I was planning to develop the test framework using Perl and it would have been just couple of lines) but could not think of a reliable way of doing it in soapUI. The biggest problem was when a test step is defined as a REST request, you can't arbitrarily change the endpoint (You can, but found sometimes it does not actually use the updated link).

The trick is to have a test step as a HTTP request instead of REST. Then the endpoint is kind of free form. In a property transfer step, XQuery nicely extracted the url and set as the endpoint of HTTP request. Bingo! it's done!

This is how I did it. Say you have the following XML content in a response and wanted to create a request for the url in tag <myweb>

<Everybody>
<me>
  <myweb>http://www.myweb.local</myweb>
  <...>... </...>
  <...>... </...>
</me>
<...>
<...>
</Everybody>

I used a property transfer with following settings. Note that you have to declare the namespaces if exist in the response.

Press run, there you have the endpoint of 'goto myweb' request is set!

It has been a while ..

Did not have a chance to look at this for a while. After starting to work at Auckland transistion authority as a test manager in last July, life got kind of busy, till I finished the contract in December 2010.

Started working at AUT on their new student portal. It is an interesting job. Inintially the plan is to use Perl to automate it, but changed to SoapUI. I kind of started liking soapUI more than I expected. User freindliness is limited in the free version :-) but I'm impressed with the flexibility. I could implement all the tests planned under Perl infrastructure using soapUI and it is quite an achievement. I will post some of the techiques used in this blog.

Selenium and Visual Studio 2010 C# (CSharp) Express edition

Again, that question - what tool to use for this automation script? It is not a big script so the risk is low even if the selection is not optimal....

Looked at Watir and Selenium. Watir is more command line oriented than Selenium. I was impressed with both as I could execute a single line in interpretive mode and get something to happen in the browser. Amazing!

Selenium had the advantage of having a recorder. It is really handy to get most of the controls without painstakingly going through the source. In couple of sites there was a reference to a recorder for Watir, but never found the tool. This was the main reason to go to Selenium. It didn't take long to realize that the builtin scripting capabilities are not flexible enough to my work. Then, the next stage -went for Selenium RC. It supports many different languages to drive automation. I went for VS C# 2010 Express, which is free from MS.

I was pleasantly surprised of the compatibility of Selenium generated C# script and the VS C#. All I had to do was changing the Target .net framework version in C# project and then copy the class.

1. After recording, select the 'C# - Selenium RC' from Options->Format. This will generate the C# class for your test.
2. Note down the class name from 'public class ' statement. This is the name of the test case.
3. Go to the VS project properties and change the target framework to .Net framework 2.0. This gave me hours of trouble till found!
4. Remove any references marked with a yellow icon in Solution Explorer, and then any 'using 'statements marked with red underline in program.cs.
5. From the same dialog, brows and add all the dlls in 'Selenium-dotnet-clientdriver-<ver>' package. This is in Selenium-remote-control <ver>.zip file. I was using version 1.0.3.
6. Create a new class. Use the name noted in 2. Cut and past the output from Selenium IDE to this class file.
7. Add the name space of class to program.cs
8. Simply create the object, and start calling the SetupTest(). Then write your program, you can use/add/edit functions created by Selenium. Call TearDownTest() at the end, which would close everything.

It was a rare situation where two independent software programs agreed with each other so well :-)