Using Subject7 API to create a custom report in Google Sheets

Abstract

In this article, I will show how to use Subject7 REST API to create a custom report in a Google Sheet.

Input: Subject7 Credentials, Execution Id(s), Google Drive API Key, Google Sheet ID

Output: A Google Sheet report of a given execution with custom columns. 

Requirement

You will need subject7 credentials and URL, You need the execution Id(s) and you need google drive client id and secret. To get your Google API keys, follow these steps: 

Google Drive API key/secret (At the end you should have a JSON file with credentials in it)

1. Browse to https://developers.google.com/sheets/api/quickstart/java. Click the blue-button as seen here:  

2. Once you click and follow Google instructions to activate API,  you will see a popup with the Client ID, Client Secret and button to download credentials.json file. Here is the screenshot:

Usage With Binaries

You need to download this jar file. 

java -jar subject7_sample_report.jar 
 -s7_username subject7User -s7_password secret 
 -s7_url https://platform.subject-7.com 
 -execution 12345678 -google_credentials_file_path c:/my_credentials.json
 -google_new_sheet_path /test/my/path
 -google_new_sheet_title my_sheet  

In the above, anything that is bold & italic is specific to you everything else is as-is. 

To use an existing Google Sheet, you need to use the google sheet id of an existing document as use parameters as follows. 

java -jar subject7_sample_report.jar 
 -s7_username subject7User -s7_password secret 
 -s7_url https://platform.subject-7.com 
 -execution 12345678 -google_credentials_file_path c:/my_credentials.json
 -google_new_sheet_path /test/my/path
 -google_sheet_id my_google_sheet_id   

Java Project Source

This project is a standard Maven project and the full source can be found in GitHub.  You can see the source and use it however you wish. But just a quick explanation. 

Here is the main class: 

public static void main(String[] args) {
    Options options = new Options();

    //S7 info
    Option optionS7Username = Option.builder(S7_USERNAME_KEY).argName(S7_USERNAME_KEY).hasArg().desc("Subject 7 Username").required().build();
    options.addOption(optionS7Username);
    Option optionS7Password = Option.builder(S7_PASSWORD_KEY).argName(S7_PASSWORD_KEY).hasArg().desc("Subject 7 Password").required().build();
    options.addOption(optionS7Password);
    Option optionS7Url = Option.builder(S7_SERVER_URL_KEY).argName(S7_SERVER_URL_KEY).hasArg().desc("Subject 7 URL With Protocol and Port").required().build();
    options.addOption(optionS7Url);

    //Execution info
    Option optionS7Executions = Option.builder(S7_EXECUTIONS_KEY).argName(S7_EXECUTIONS_KEY).hasArg().desc("Subject 7 Executions IDs").required().build();
    optionS7Executions.setArgs(Option.UNLIMITED_VALUES);
    options.addOption(optionS7Executions);

    //Google info
    Option optionGoogleCredentialsPath = Option.builder(GOOGLE_CREDENTIALS_FILE_PATH_KEY).argName(GOOGLE_CREDENTIALS_FILE_PATH_KEY).
            hasArg().desc("Path to file with Google API credentials").required().build();
    options.addOption(optionGoogleCredentialsPath);
    Option optionGoogleTokensDirPath = Option.builder(GOOGLE_TOKENS_DIR_PATH_KEY).argName(GOOGLE_TOKENS_DIR_PATH_KEY).
            hasArg().desc("Path to directory where application will store Google tokens").optionalArg(true).build();
    options.addOption(optionGoogleTokensDirPath);
    Option optionGoogleSheetId = Option.builder(GOOGLE_SHEET_ID_KEY).argName(GOOGLE_SHEET_ID_KEY).
            hasArg().desc("Google Sheet ID (if file already exists)").optionalArg(true).build();
    options.addOption(optionGoogleSheetId);
    Option optionGoogleNewSheetPath = Option.builder(GOOGLE_NEW_SHEET_PATH_KEY).argName(GOOGLE_NEW_SHEET_PATH_KEY).
            hasArg().desc("Path for new Google Sheet (if file doesn't exist)").optionalArg(true).build();
    options.addOption(optionGoogleNewSheetPath);
    Option optionGoogleNewSheetTitle = Option.builder(GOOGLE_NEW_SHEET_TITLE_KEY).argName(GOOGLE_NEW_SHEET_TITLE_KEY).
            hasArg().desc("Title for new Google Sheet file (if file doesn't exist)").optionalArg(true).build();
    options.addOption(optionGoogleNewSheetTitle);


    //validate options
    CommandLine cmd;
    CommandLineParser parser = new DefaultParser();
    try {
        cmd = parser.parse(options, args);
    } catch (Exception e) {
        System.out.println(e.getMessage());
        return;
    }

    String[] executionIds = cmd.getOptionValues(S7_EXECUTIONS_KEY);
    try {
        List<List<Object>> values = new ArrayList<>();
        for (String executionId : executionIds) {
            String response = RestUtils.executeHttpRequest("GET", cmd.getOptionValue(S7_SERVER_URL_KEY) + "/api/v2/executions/" + executionId, null, null,
                    new Authentication(cmd.getOptionValue(S7_USERNAME_KEY), cmd.getOptionValue(S7_PASSWORD_KEY), AuthenticationType.BASIC));
            ExecutionResponse execution = Converter.convertToObjectT(ExecutionResponse.class, response);
            values.add(convertExecutionToRowValues(execution));
        }


        GoogleWorker googleWorker = new GoogleWorker(cmd.getOptionValue(GOOGLE_CREDENTIALS_FILE_PATH_KEY),
                cmd.getOptionValue(GOOGLE_TOKENS_DIR_PATH_KEY),
                cmd.getOptionValue(GOOGLE_SHEET_ID_KEY),
                cmd.getOptionValue(GOOGLE_NEW_SHEET_PATH_KEY),
                cmd.getOptionValue(GOOGLE_NEW_SHEET_TITLE_KEY));
        googleWorker.append(values);
    } catch (Exception e) {
        System.out.println(e.getMessage());
    }
}

private static List<Object> convertExecutionToRowValues(ExecutionResponse execution) {
    List<Object> executionValues = new ArrayList<>();
    executionValues.add(execution.getId().toString());
    executionValues.add(execution.getExecutionSetName());
    executionValues.add(execution.getExecutionStatus().toString());
    executionValues.add(execution.getExecutionState().toString());
    return executionValues;
}

1. Get user input: The first few lines were are just getting user inputs (subject7 credentials, google API keys, Execution Id as well as google sheet name). 
2. Make a REST call to Subject7 Platform to get the execution object as JSON and then we convert it to a POJO ExecutionResponse. 
3. Open/Create Google Sheet and write the values to it row by row.