[Python3] Outputting JMeter execution results to a Google Spreadsheet [gspread]

Hello.
This is Shimeji from the SS team.
I occasionally work on load testing projects, and since recording the JMeter execution results every time was a hassle, I created a script to output the results to a Google Spreadsheet.
Let's automate it!

Advance preparation

You will need to prepare the following three things in advance:
• Enable the Google Drive API / Google Sheets API
• Download the private key (JSON data)
• Set up spreadsheet sharing

For more information, please visit the link below!
https://console.developers.google.com/

Also, please make sure you have installed the necessary packages beforehand.
*For CentOS 7, the following command will work!

yum install python3 python-devel jq pip3 install gspread pip3 install oauth2client

Script 1 (Python)

This is a Python script that outputs a CSV file specified as a command-line argument to a spreadsheet.
Python has a convenient library for spreadsheet manipulation called "gspread," which we will gratefully use.

#!/usr/bin/python3 import gspread import json import csv import sys import itertools # Specify the secret key with an absolute path SECRETJSON = "/usr/local/jmeter/bin/sacred-drive.json" # Define the spreadsheet key SPREADSHEET_KEY = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' ############################################################################## ## Functions # Number and character converter def num2alpha(num): if num<=26: return chr(64+num) elif num%26==0: return num2alpha(num//26-1)+chr(90) else: return num2alpha(num//26)+chr(64+num%26) ####################################################################### ## Authentication # Promises from oauth2client.service_account import ServiceAccountCredentials scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive'] # Define the downloaded json file credentials = ServiceAccountCredentials.from_json_keyfile_name(SECRETJSON, scope) # Log in to Google API gc = gspread.authorize(credentials) # Open sheet 1 of the spreadsheet worksheet = gc.open_by_key(SPREADSHEET_KEY).sheet1 ################################################################################## ## Processing # Get command line arguments args = sys.argv csvfile = args[1] # Assign the contents of the CSV file to an array with open(csvfile) as fp: results_list_ex = list(csv.reader(fp)) # Convert a 2D array to a 1D array results_list = list(itertools.chain.from_iterable(results_list_ex)) # Initialize the count variable COUNT_NUM = 1 # Search for blank rows while str(len(worksheet.cell(COUNT_NUM, 1).value)) != "0": COUNT_NUM += 1 # Specify the range to edit cell_list = worksheet.range('A'+str(COUNT_NUM)+':'+num2alpha(len(results_list))+str(COUNT_NUM)) # Assign the results_list array to cell_list for i,cell in enumerate(cell_list): cell.value = results_list[i] # Save the results worksheet.update_cells(cell_list)

The following two points need to be adjusted to suit your environment:
• Specify the Google API secret key file in "SECRETJSON".
• Specify the key of the sheet where the results will be output in "SPREADSHEET_KEY".

This Python script will be used in the shell script described below

Script 2 (shell script)

#!/bin/sh DATE=$(date +"%Y%m%d") OPTIME=$(date +"%Y%m%d-%H%M%S") # Specify the directory to output the results LOGDIR=/var/www/html/${DATE} # Specify the JMX file FILE_JMX=/usr/local/jmeter/bin/templates/build-web-test-plan.jmx # Create the date directory mkdir -p ${LOGDIR} # Run JMeter /usr/local/jmeter/bin/jmeter -Dsun.net.inetaddr.ttl=0 -n -t ${FILE_JMX} -j ${LOGDIR}/${OPTIME}.log -l ${LOGDIR}/${OPTIME}.jtl -e -o ${LOGDIR}/${OPTIME}/ -r # Create a CSV file cat ${LOGDIR}/${OPTIME}/statistics.json | jq -r ". [] | [.transaction,.sampleCount,.errorCount,.errorPct,.meanResTime,.minResTime,.maxResTime,.pct1ResTime,.pct2ResTime,.pct3ResTime,.throughput,.receivedKBytesPerSec,.sentKBytesPerSec] | @csv" | grep "Total" > ${LOGDIR}/${OPTIME}/statistics.csv # Output results to a spreadsheet /usr/local/bin/main.py ${LOGDIR}/${OPTIME}/statistics.csv

process involves formatting the output from JSON to CSV and then passing it to a Python script.
The script's processing steps are:
① Run JMeter
② Convert the JSON output to CSV
to a spreadsheet
append it

Usage example

Let's try it out!

[root@test-server2-1 bin]# ./start-controller_cui.sh Creating summariser<summary> Created the tree successfully using /usr/local/jmeter/bin/templates/build-web-test-plan.jmx Configuring remote engine: 192.168.33.11 Starting remote engines the test @ Thu Jun 25 04:59:38 JST 2020 (1593028778488) Remote engines have been started Waiting for possible Shutdown/StopTestNow/HeapDump/ThreadDump message on port 4445 summary = 2 in 00:00:02 = 1.2/s Avg: 215 Min: 139 Max: 291 Err: 0 (0.00%) Tidying up remote @ Thu Jun 25 04:59:41 JST 2020 (1593028781684) ... end of run [root@test-server2-1 bin]# ls /var/www/html/20200625/20200625-033715_th/statistics.csv /var/www/html/20200625/20200625-033715_th/statistics.csv

Yes, it ran successfully! It looks like the CSV file has been created as well.
Now let's check the spreadsheet itself.

It worked!
Let's run it one more time!

It's outputted correctly on the next row. OK!

Conclusion

So, what did you think?
With Python's extensive libraries, you can implement this quite easily and quickly.

In the future, I would like to improve the functionality so that the following items can also be output at the same time

1. Execution time (YYYY/MM/DD hh:mm:ss)
2. JMeter execution parameters
: number of controllers, number of slaves
, number of
threads,
number of loops
, ramp-up time
3. Link to the HTML file output by JMeter

Well then