Write to google sheet using Maximo

Setting up Google Console

1. Go to console.cloud.google.com

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

3. Go to API and services → Enabled API and services

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

5. In the next page. Click on Create credentials button

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

7. This will take you to service account creation page.
1. Give it any name, note the email created and click Create and continue button.
2. Select role as owner. Click Continue and then click Done

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

9. In service account page, click on Keys
1. Click on Add Key → Create new key
2. Select JSON and click Create
3. This will download a json file

10. Note the following values from key
1. project_id
2. private_key_id
3. private_key
4. client_email
5. token_uri

Setting up Google Sheets

1. Go to Google Sheets

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

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

Setting up WebSphere

1. 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
 

2. 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
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

1. 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

2. 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

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

Java Code

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;

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);