Write to google sheet using Maximo

Maximo like any other Java can read / write to Google sheets like any other typical java / python app

Setting up Google Console

Go to console.cloud.google.com

Create a new project. Give it any name. Select the newly created project from.

Go to API and services → Enabled API and services

In the search type Sheet API. Select Sheet API and click on enable

In the next page. Click on Create credentials button

In the next page, under Credential Type select Application data. Click Next.

This will take you to service account creation page.

Give it any name, note the email created and click Create and continue button.
Select role as owner. Click Continue and then click Done

On the left menu, click on Credentials. Then select your created service account.

In service account page, click on Keys

Click on Add Key → Create new key
Select JSON and click Create
This will download a json file

Note the following values from key

project_id
private_key_id
private_key
client_email
token_uri

Setting up Google Sheets

Go to Google Sheets

Create a new sheet or open an existing sheet. Note its id. Its located on the url between the /d/ and /edit

Share the sheet with the email address of service account created in step 7a. Give it the role of editor

Setting up WebSphere

The following jars are required


apiguardian-api-1.1.0.jar
checker-qual-3.12.0.jar
commons-codec-1.11.jar
commons-logging-1.2.jar
error_prone_annotations-2.11.0.jar
failureaccess-1.0.1.jar
google-api-client-1.35.2.jar
google-api-services-sheets-v4-rev612-1.25.0.jar
google-http-client-1.42.0.jar
google-http-client-apache-v2-1.42.0.jar
google-http-client-gson-1.42.0.jar
google-oauth-client-1.34.1.jar
grpc-context-1.27.2.jar
gson-2.9.0.jar
guava-31.1-jre.jar
httpclient-4.5.13.jar
httpcore-4.4.15.jar
j2objc-annotations-1.3.jar
jsr305-3.0.2.jar
listenablefuture-9999.0-empty-to-avoid-conflict-with-guava.jar
opencensus-api-0.31.1.jar
opencensus-contrib-http-util-0.31.1.jar
opentest4j-1.2.0.jar

You can download the dependencies from https://mvnrepository.com/ which is time consuming

or faster way is to add in your pom.xml add these two dependencies.


		<dependency>
      <groupId>com.google.api-client</groupId>
      <artifactId>google-api-client</artifactId>
      <version>1.35.2</version>
    </dependency>
    <dependency>
      <groupId>com.google.apis</groupId>
      <artifactId>google-api-services-sheets</artifactId>
      <version>v4-rev612-1.25.0</version>
    </dependency>

Then run this command mvn dependency:copy-dependencies to generate all dependencies to target\dependency folder. Copy the generated dependencies in your Maximo server and then configure Websphere in next step

Configure the downloaded jar files and add them to Websphere shared libraries using the below link

How to create a shared library and associate it with the application server or enterprise application on WebSphere Application Server

https://www.ibm.com/support/pages/how-create-shared-library-and-associate-it-application-server-or-enterprise-application-websphere-application-server

Maximo code

Create a new autoscript and paste the following code in it


from com.google.api.client.googleapis.auth.oauth2 import GoogleCredential
from com.google.api.client.googleapis.javanet import GoogleNetHttpTransport
from com.google.api.client.googleapis.util import Utils
from com.google.api.client.http import HttpTransport
from com.google.api.client.json import JsonFactory
from com.google.api.client.json.gson import GsonFactory
from com.google.api.client.util import PemReader, SecurityUtils, Base64
from com.google.api.client.util.PemReader import Section
from com.google.api.services.sheets.v4 import SheetsScopes, Sheets
from com.google.api.services.sheets.v4.model import ValueRange, BatchGetValuesResponse
from java.io import StringReader
from java.security import PrivateKey, KeyFactory
from java.security.spec import PKCS8EncodedKeySpec
from java.util import Collections, ArrayList, Arrays


APPLICATION_NAME = "Maximomize"
SPREADSHEET_ID = "1YX9wA_M8V4Fj1StyTH3-U2guU7YYaWPfmyBKGJZDgCI"

# Populate these values from the json file downloaded 
project_id = ""
private_key_id = ""
private_key = ""
client_email = ""
token_uri = ""

httpTransport = GoogleNetHttpTransport.newTrustedTransport()
jsonFactory = GsonFactory.getDefaultInstance()

def getPrivateKeyFromString(serviceKeyPem):
  privKeyPEM = serviceKeyPem.replace("-----BEGIN PRIVATE KEY-----", "")\
    .replace("-----END PRIVATE KEY-----", "")\
    .replace("\r", "")\
    .replace("\n", "")

  encoded = Base64.decodeBase64(privKeyPEM)
  keySpec = PKCS8EncodedKeySpec(encoded)
  privateKey = KeyFactory.getInstance("RSA").generatePrivate(keySpec)
  return privateKey


privateKey = getPrivateKeyFromString(private_key)
credential = GoogleCredential.Builder().setTransport(
  Utils.getDefaultTransport()).setJsonFactory(jsonFactory).setServiceAccountId(
  client_email).setServiceAccountScopes(
  Collections.singleton(SheetsScopes.SPREADSHEETS)).setServiceAccountPrivateKey(
  privateKey).setServiceAccountPrivateKeyId(
  private_key_id).setTokenServerEncodedUrl(
  token_uri).setServiceAccountProjectId(project_id).build()

sheetsService = Sheets.Builder(httpTransport, jsonFactory,credential)\
  .setApplicationName(APPLICATION_NAME).build()

writeText = ValueRange().setValues(Arrays.asList(
  Arrays.asList("Maximomize Technologies", "http://www.maximomize.com"),
  Arrays.asList("Hello", "Maximo")
))
writeResult = sheetsService.spreadsheets().values()\
  .update(SPREADSHEET_ID, "A1", writeText).setValueInputOption("RAW")\
  .execute()

readRange = Arrays.asList("A1", "B2")
readResult = sheetsService.spreadsheets().values().batchGet(
  SPREADSHEET_ID).setRanges(readRange).execute()

returnedText = readResult.getValueRanges().get(0).getValues().get(0).get(0)

print returnedText

Click on LaunchScript button

ℹ️

If Launch Script button is missing. You can enable it using the following link Missing “Launch Script” function in Autoscript Application in Maximo / ICD 7.6 – Maximo & ICD Script Development (maximoscripting.com)

ℹ️

You might need to add googleapis.com to Webspere trust store. See this

It should first write the sheet and then read the first values from sheet

Java Code

If you need Java code it is also very similar. Use the following imports


import java.io.FileNotFoundException;
import java.io.IOException;
import java.security.GeneralSecurityException;
import java.security.KeyFactory;
import java.security.PrivateKey;
import java.security.spec.PKCS8EncodedKeySpec;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;

import com.google.api.client.googleapis.auth.oauth2.GoogleCredential;
import com.google.api.client.googleapis.javanet.GoogleNetHttpTransport;
import com.google.api.client.googleapis.util.Utils;
import com.google.api.client.http.HttpTransport;
import com.google.api.client.json.JsonFactory;
import com.google.api.client.json.gson.GsonFactory;
import com.google.api.client.util.Base64;
import com.google.api.services.sheets.v4.Sheets;
import com.google.api.services.sheets.v4.SheetsScopes;
import com.google.api.services.sheets.v4.model.BatchGetValuesResponse;
import com.google.api.services.sheets.v4.model.UpdateValuesResponse;
import com.google.api.services.sheets.v4.model.ValueRange;

The function code is below


HttpTransport httpTransport = GoogleNetHttpTransport.newTrustedTransport();
        JsonFactory jsonFactory = GsonFactory.getDefaultInstance();
        final String APPLICATION_NAME = "";
        final String SPREADSHEET_ID = "";

        final String project_id = "";
        final String private_key_id = "";
        final String private_key = "";
        final String client_email = "";
        final String token_uri = "";

        PrivateKey privateKey = getPrivateKeyFromString(private_key);

        GoogleCredential credential = new GoogleCredential.Builder()
                .setTransport(Utils.getDefaultTransport())
                .setJsonFactory(jsonFactory)
                .setServiceAccountId(client_email)
                .setServiceAccountScopes(Collections.singleton(SheetsScopes.SPREADSHEETS))
                .setServiceAccountPrivateKey(privateKey)
                .setServiceAccountPrivateKeyId(private_key_id)
                .setTokenServerEncodedUrl(token_uri)
                .setServiceAccountProjectId(project_id).build();

        Sheets sheetsService = new Sheets.Builder(httpTransport, jsonFactory, credential)
                .setApplicationName(APPLICATION_NAME)
                .build();

        ValueRange body = new ValueRange().setValues(Arrays.asList(Arrays.asList("Maximomize"),
                Arrays.asList("Hello", "Dev")));
        UpdateValuesResponse result = sheetsService.spreadsheets().values().update(SPREADSHEET_ID, "A1", body)
                .setValueInputOption("RAW").execute();

        List<String> ranges = Arrays.asList("A1", "B2");
        BatchGetValuesResponse readResult = sheetsService.spreadsheets().values().batchGet(SPREADSHEET_ID)
                .setRanges(ranges).execute();
        Object returnedText = readResult.getValueRanges().get(0).getValues().get(0).get(0);

Reference:

Getting Started with Google Sheets API in Node.js | Engineering Education (EngEd) Program | Section