Eating Our Own Dogfood - Using Rapise to Automate Spira

May 17th, 2018 by Adam Sandman

So, we had the task of loading a bunch of users into an instance of SpiraTest for our upcoming Social Testing competition this week. The good news was that we had a nice Excel sheet of all the users that had registered using Eventbrite and Meetup, but we now had to get them into SpiraTest. What we needed was a tool that can read a spreadsheet and make REST web service calls into SpiraTest to create the projects and load in the users. Wow, we have a tool that can do that, Rapise! Find out how we used Rapise to automate this laborious task.

Attaching the Excel Spreadsheet

The first thing we did is create a new Rapise test project. We then used the option in Rapise to Attach an Excel Spreadsheet:

Once we had attached the file containing the registered users, we could use the built-in Rapise spreadsheet editor to clean up the data prior to loading into SpiraTest:

Now that we had the spreadsheet with the data, we can use the global Spreadsheet object to attach to the data and loop through the users and projects:

	//Loop through the data
	Spreadsheet.DoAttach('%WORKDIR%/DataToLoad.xlsx');
	while (Spreadsheet.DoSequential())
	{
		//Project
		var projectName = Spreadsheet.GetCell('Project Name');
		var organization = Spreadsheet.GetCell('Organization');
		
		//User 1
		var firstName1 = Spreadsheet.GetCell('First Name 1');
		var lastName1 = Spreadsheet.GetCell('Last Name 1');
		var email1 = Spreadsheet.GetCell('Email Address 1');
		var login1 = Spreadsheet.GetCell('Login Name 1');

		//User 2
		var firstName2 = Spreadsheet.GetCell('First Name 2');
		var lastName2 = Spreadsheet.GetCell('Last Name 2');
		var email2 = Spreadsheet.GetCell('Email Address 2');
		var login2 = Spreadsheet.GetCell('Login Name 2');
	}

OK, now that we had a way to read the data from the spreadsheet, the next thing we needed to do, was to access the SpiraTest REST API to actually load in the projects and users.

Making the REST Calls

Using Rapise, we can manually add a REST web service and configure the various resources by hand:

However the SpiraTest has a handy feature where you can download each of the REST resources as a pre-configured .REST file that can be used by Rapise. So to save time and avoid manual errors typing up the REST URLs we downloaded the resources that we needed:

Once we had downloaded all of the REST files and added them to the Rapise project, we had the following:

We had to adjust the URL and the credentials in the REST definitions to match the actual instance of SpiraTest that we were using to load the data into.

Once that was done, we just needed to add the code to the test project that looped through the users and projects, adding them to SpiraTest:

		//Populate project
		var newProject = {
			Name: projectName,
			Description: '' + organization
		};
		
		//Create the project first using the template
		Tester.Message('Creating project \'' + projectName + '\' using template PR' + templateId);
		SeS('Project_Create').SetCredential(username, apiKey);
		SeS('Project_Create').SetRequestBodyObject(newProject);
		SeS('Project_Create').DoExecute({"existing_project_id": templateId});
		newProject = SeS('Project_Create').GetResponseBodyObject();
		var newProjectId = newProject.ProjectId;
		Tester.Message('Created project \'' + projectName + '\', new ID=' + newProjectId);
		
		//Now add the users to the system and the project
		if (login1 && login1 != '')
		{
			Tester.Message('Adding user \'' + login1 + '\' to project ' + projectName);
			
			//Add user to their project as project owner
			var params = {
				"password": "changeme",
				"password_question": "What is 1+1?",
				"password_answer": "2",
				"project_id": newProjectId,
				"project_role_id": PROJECT_ROLE_PROJECT_OWNER
			};
			var newUser = {
				"FirstName": firstName1,
				"LastName": lastName1,
				"UserName": login1,
				"EmailAddress": email1,
				"Admin": false,
				"Active": true,
				"Approved": true,
				"Locked": false
			};
			SeS('User_Create').SetRequestBodyObject(newUser);
			SeS('User_Create').SetCredential(username, apiKey);
			SeS('User_Create').DoExecute(params);
			newUser = SeS('User_Create').GetResponseBodyObject();
			var newUserId = newUser.UserId;
			
			//Add user to sample project as 'tester'
			var newAllocation = {
				"ProjectId": sampleProjectId,
				"UserId": newUserId,
				"ProjectRoleId": PROJECT_ROLE_TESTER
			};
			SeS('ProjectUser_AddUserMembership').SetCredential(username, apiKey);
			SeS('ProjectUser_AddUserMembership').SetRequestBodyObject(newAllocation);
			SeS('ProjectUser_AddUserMembership').DoExecute({ "project_id": sampleProjectId });
		}

Putting it All Together

If you would like to reuse the code and Rapise project to help you do bulk imports of users and projects into your SpiraTest instance, we have published the complete project onto our GitHub Account:

https://github.com/Inflectra/rapise-loading-data-into-spira

Enjoy!