[Python3] Output JMeter execution results to google spreadsheet [gspread]

Hello.
This is Shimeji from the SS team.
I occasionally work on load testing projects, but I was lazy to record the JMeter execution results each time, so I created a script that outputs the execution results to Google Spreadsheet.
Let's automate! !

Advance preparation

You need to prepare the following three items in advance.
・Enable Google Drive API / Google Sheets API
・Download private key (JSON data)
・Spreadsheet sharing settings

Check out everything below!
https://console.developers.google.com/

Also, be sure to install the necessary packages in advance.
*If you are using CentOS7, the following command is OK!

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

Script 1 (Python)

This is a Python script that outputs the csv specified as a command line argument to a spreadsheet.
Python has a useful library for manipulating spreadsheets called ``gspread,'' so I'm grateful to be able to use it.

#!/usr/bin/python3 import gspread import json import csv import sys import itertools # Specify secret key with absolute path SECRETJSON = "/usr/local/jmeter/bin/sacred-drive.json" # Define spreadsheet key SPREADSHEET_KEY = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' ############################################ ############################## ## Function # 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 # Promise 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 the command line arguments args = sys.argv csvfile = args[1] # Assign the contents of the CSV file to the array with open(csvfile) as fp: results_list_ex = list(csv.reader(fp)) # 2-dimensional array Convert to a one-dimensional array results_list = list(itertools.chain.from_iterable(results_list_ex)) # Initialize count variable COUNT_NUM = 1 # Search for blank line 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)) # results_list in cell_list Assign array for i,cell in enumerate(cell_list): cell.value = results_list[i] # Save results worksheet.update_cells(cell_list)

The following two points need to be adjusted appropriately to the environment.
- Specify the Google API private key file in "SECRETJSON".
- Please specify the key of the sheet to output the results 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 result output destination directory LOGDIR=/var/www/html/${DATE} # Specify JMX file FILE_JMX=/usr/local/jmeter/bin/templates/build-web-test-plan.jmx # Create 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 CSV file cat ${LOGDIR}/${OPTIME}/statistics.json | jq -r ". grep " Total" > ${LOGDIR}/${OPTIME}/statistics.csv # Output results to spreadsheet /usr/local/bin/main.py ${LOGDIR}/${OPTIME}/statistics.csv

It's like formatting the results output in json to csv and passing it to the Python script.
The processing contents of the script are:
1) Run JMeter
2) Convert the json format results to csv

append it to the spreadsheet

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 Starting 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 worked! It looks like a CSV file has also been created.
Let's check the important spreadsheet.

I made it.
Let's try it again!

It is correctly output on the next line. OK!

At the end

How was it?
If you use Python's rich library, you can implement it quite easily.

Also, in the future, I would like to improve the function so that the following items can 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
, lamp UP time
3. To HTML file output by JMeter link of

Well then.