Read Google sheet API : ExceptionInInitializerError

[moustik]

Hi community,

I have an ExceptionInInitializerError :

2022-12-27 12:06:07.460 INFO  c.k.katalon.core.main.TestCaseExecutor   - --------------------
2022-12-27 12:06:07.468 INFO  c.k.katalon.core.main.TestCaseExecutor   - START Test Cases/Draft/LireGoogleSheet
2022-12-27 12:06:08.840 INFO  c.k.k.c.keyword.builtin.CommentKeyword   - beforeTestCase: Test Cases/Draft/LireGoogleSheet
Test Cases/Draft/LireGoogleSheet
[:]
iCi
déc. 27, 2022 12:06:10 PM com.google.api.client.util.store.FileDataStoreFactory setPermissionsToOwnerOnly
AVERTISSEMENT: unable to change permissions for everybody: C:\Users\soukna\nefertiti_auto\tokens
déc. 27, 2022 12:06:10 PM com.google.api.client.util.store.FileDataStoreFactory setPermissionsToOwnerOnly
AVERTISSEMENT: unable to change permissions for owner: C:\Users\soukna\nefertiti_auto\tokens
2022-12-27 12:06:11.007 ERROR k.k.c.m.CustomKeywordDelegatingMetaClass - ❌ java.lang.ExceptionInInitializerError
2022-12-27 12:06:11.013 ERROR c.k.katalon.core.main.TestCaseExecutor   - ❌ Test Cases/Draft/LireGoogleSheet FAILED.
Reason:
org.codehaus.groovy.runtime.InvokerInvocationException: java.lang.ExceptionInInitializerError
	at technicalFunctions.ReadSpreadsheet.invokeMethod(ReadSpreadsheet.groovy)
	at com.kms.katalon.core.main.CustomKeywordDelegatingMetaClass.invokeStaticMethod(CustomKeywordDelegatingMetaClass.java:55)
	at LireGoogleSheet.run(LireGoogleSheet:26)
	at com.kms.katalon.core.main.ScriptEngine.run(ScriptEngine.java:194)
	at com.kms.katalon.core.main.ScriptEngine.runScriptAsRawText(ScriptEngine.java:119)
	at com.kms.katalon.core.main.TestCaseExecutor.runScript(TestCaseExecutor.java:448)
	at com.kms.katalon.core.main.TestCaseExecutor.doExecute(TestCaseExecutor.java:439)
	at com.kms.katalon.core.main.TestCaseExecutor.processExecutionPhase(TestCaseExecutor.java:418)
	at com.kms.katalon.core.main.TestCaseExecutor.accessMainPhase(TestCaseExecutor.java:410)
	at com.kms.katalon.core.main.TestCaseExecutor.execute(TestCaseExecutor.java:285)
	at com.kms.katalon.core.main.TestCaseMain.runTestCase(TestCaseMain.java:142)
	at com.kms.katalon.core.main.TestCaseMain.runTestCase(TestCaseMain.java:133)
	at com.kms.katalon.core.main.TestCaseMain$runTestCase$0.call(Unknown Source)
	at TempTestCase1672139158040.run(TempTestCase1672139158040.groovy:25)
Caused by: java.lang.ExceptionInInitializerError
	at com.google.api.services.sheets.v4.Sheets.<clinit>(Sheets.java:48)
	at com.google.api.services.sheets.v4.Sheets$Builder.build(Sheets.java:2958)
	at com.google.api.services.sheets.v4.Sheets$Builder$build$0.call(Unknown Source)
	at technicalFunctions.ReadSpreadsheet.getSpreadSheetRecords(ReadSpreadsheet.groovy:97)
	at technicalFunctions.ReadSpreadsheet.invokeMethod(ReadSpreadsheet.groovy)
	at com.kms.katalon.core.main.CustomKeywordDelegatingMetaClass.invokeStaticMethod(CustomKeywordDelegatingMetaClass.java:55)
	at Script1672065789436.run(Script1672065789436.groovy:26)
	... 11 more
Caused by: java.lang.IllegalStateException: No match found
	at com.google.api.client.googleapis.GoogleUtils.<clinit>(GoogleUtils.java:66)
	at com.google.api.services.sheets.v4.Sheets.<clinit>(Sheets.java:48)
	at com.google.api.services.sheets.v4.Sheets$Builder.build(Sheets.java:2958)
	at com.google.api.services.sheets.v4.Sheets$Builder$build$0.call(Unknown Source)
	at technicalFunctions.ReadSpreadsheet.getSpreadSheetRecords(ReadSpreadsheet.groovy:97)
	at technicalFunctions.ReadSpreadsheet.invokeMethod(ReadSpreadsheet.groovy)
	at com.kms.katalon.core.main.CustomKeywordDelegatingMetaClass.invokeStaticMethod(CustomKeywordDelegatingMetaClass.java:55)
	at LireGoogleSheet.run(LireGoogleSheet:26)
	... 11 more

2022-12-27 12:06:11.043 INFO  c.k.katalon.core.main.TestCaseExecutor   - END Test Cases/Draft/LireGoogleSheet

This is my code :

package technicalFunctions
import static com.kms.katalon.core.checkpoint.CheckpointFactory.findCheckpoint
import static com.kms.katalon.core.testcase.TestCaseFactory.findTestCase
import static com.kms.katalon.core.testdata.TestDataFactory.findTestData
import static com.kms.katalon.core.testobject.ObjectRepository.findTestObject

import com.google.api.client.util.store.FileDataStoreFactory
import com.kms.katalon.core.annotation.Keyword
import com.kms.katalon.core.checkpoint.Checkpoint
import com.kms.katalon.core.checkpoint.CheckpointFactory
import com.kms.katalon.core.mobile.keyword.MobileBuiltInKeywords
import com.kms.katalon.core.model.FailureHandling
import com.kms.katalon.core.testcase.TestCase
import com.kms.katalon.core.testcase.TestCaseFactory
import com.kms.katalon.core.testdata.TestData
import com.kms.katalon.core.testdata.TestDataFactory
import com.kms.katalon.core.testobject.ObjectRepository
import com.kms.katalon.core.testobject.TestObject
import com.kms.katalon.core.webservice.keyword.WSBuiltInKeywords
import com.kms.katalon.core.webui.keyword.WebUiBuiltInKeywords

//import MobileBuiltInKeywords as Mobile
//import WSBuiltInKeywords as WS
//import WebUiBuiltInKeywords as WebUI

import org.openqa.selenium.WebElement
import org.openqa.selenium.WebDriver
import org.openqa.selenium.By

import com.kms.katalon.core.testobject.RequestObject
import com.kms.katalon.core.testobject.ResponseObject
import com.kms.katalon.core.testobject.ConditionType
import com.kms.katalon.core.testobject.TestObjectProperty

import com.kms.katalon.core.mobile.helper.MobileElementCommonHelper
import com.kms.katalon.core.util.KeywordUtil

import com.kms.katalon.core.webui.exception.WebElementNotFoundException

import com.google.api.client.auth.oauth2.Credential;
import com.google.api.client.extensions.java6.auth.oauth2.AuthorizationCodeInstalledApp;
import com.google.api.client.extensions.jetty.auth.oauth2.LocalServerReceiver;
import com.google.api.client.googleapis.auth.oauth2.GoogleAuthorizationCodeFlow;
import com.google.api.client.googleapis.auth.oauth2.GoogleClientSecrets;
import com.google.api.client.googleapis.javanet.GoogleNetHttpTransport;
import com.google.api.client.http.HttpTransport
import com.google.api.client.http.javanet.NetHttpTransport;
import com.google.api.client.json.JsonFactory;
//import com.google.api.client.json.jackson2.JacksonFactory; // obsolète
//import com.google.api.client.json.GsonFactory;  // obsolète
import com.google.api.client.json.gson.GsonFactory; // c'est la solution depuis 2021 !!!
import com.google.api.services.sheets.v4.Sheets;
import com.google.api.services.sheets.v4.SheetsScopes;
import com.google.api.services.sheets.v4.model.ValueRange;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.security.GeneralSecurityException;
import java.util.Collections;
import java.util.List;

class ReadSpreadsheet {
	private static final String APPLICATION_NAME = "gcp-nerfertiti";
	private static final JsonFactory JSON_FACTORY = GsonFactory.getDefaultInstance();
	private static final String TOKENS_DIRECTORY_PATH = "tokens";

	private static final List<String> SCOPES = Collections.singletonList(SheetsScopes.SPREADSHEETS_READONLY);
	private static final String CREDENTIALS_FILE_PATH = "C:/Users/soukna/nefertiti_auto/client_secret.json";

	private static Credential getCredentials(final NetHttpTransport HTTP_TRANSPORT) throws IOException {
		// Load client secrets.
		InputStream in1 = new FileInputStream(CREDENTIALS_FILE_PATH);
		if (in1 == null) {
			throw new FileNotFoundException("Resource not found: " + CREDENTIALS_FILE_PATH);
		}
		GoogleClientSecrets clientSecrets = GoogleClientSecrets.load(JSON_FACTORY, new InputStreamReader(in1));

		// Build flow and trigger user authorization request.
		GoogleAuthorizationCodeFlow flow = new GoogleAuthorizationCodeFlow.Builder(HTTP_TRANSPORT, JSON_FACTORY,clientSecrets, SCOPES).setDataStoreFactory(new FileDataStoreFactory(new java.io.File(TOKENS_DIRECTORY_PATH))).setAccessType("offline").build();
		LocalServerReceiver receiver = new LocalServerReceiver.Builder().setPort(8889).build();
		return new AuthorizationCodeInstalledApp(flow, receiver).authorize("user");
	}

	/**
	 * Permet de lire La G-Sheet
	 */
	@Keyword
	public static List<List<Object>> getSpreadSheetRecords(String range) throws IOException, GeneralSecurityException {
		//System.out.println("iCi");
		// Build a new authorized API client service.
		//final NetHttpTransport HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport(); //obsolète d'après https://stackoverflow.com/questions/39248453/googlenethttptransport-newtrustedtransport-returning-null
		NetHttpTransport HTTP_TRANSPORT = new com.google.api.client.http.javanet.NetHttpTransport()
		final String spreadsheetId = "1LXrgBIZx5K0XXWXBGaJGId1i3r8q_P6dGKhVacCVclo";
		System.out.println("iCi");
		Sheets service = new Sheets.Builder(HTTP_TRANSPORT, JSON_FACTORY, 
			getCredentials(HTTP_TRANSPORT)).setApplicationName(APPLICATION_NAME).build();
		System.out.println("LA");
		ValueRange response = service.spreadsheets().values().get(spreadsheetId, range).execute();
		List<List<Object>> values = response.getValues();
		if (values == null || values.isEmpty()) {
			System.out.println("No data found.");
			return null;
		} else {
			System.out.println("found");
			return values;
		}
	}
}

The error line is :

Sheets service = new Sheets.Builder(HTTP_TRANSPORT, JSON_FACTORY, 
			getCredentials(HTTP_TRANSPORT)).setApplicationName(APPLICATION_NAME).build();

I don’t know what’s wrong.

Could someone please help me?
Thank you

[kazurayam]

Caused by: java.lang.IllegalStateException: No match found
at com.google.api.client.googleapis.GoogleUtils.(GoogleUtils.java:66)

You can read the source of the GroogleUtils.java:66 at

googleapis/google-api-java-client/blob/main/google-api-client/src/main/java/com/google/api/client/googleapis/GoogleUtils.java

[kazurayam]

How did you brought the jar of com.google.api-client into the Katalon project?
Did you build it locally? or download from the Maven Central?
To me, the jar you used seems to be corrupted. The com.google.api-client.properties file in the jar is corrupted somehow.

[moustik]

It’s possible i don’t know…
I downloaded it from the web, I can’t remember where exactly…I followed this tutorial : READ DATA FROM GOOGLE SHEET WITH SELENIUM | by Prashant Bhatasana | AppGambit | Medium

Could you tell me which jar to download and where please?

The error came from this line :

Sheets service = new Sheets.Builder(HTTP_TRANSPORT, JSON_FACTORY, 
			getCredentials(HTTP_TRANSPORT)).setApplicationName(APPLICATION_NAME).build();

I think that “new Sheets.Builder” is obsolete i don’t know.

it’s very simple what i would like to do :
READ GOOGLE SHEET

It’s a very strange error !

[kazurayam]

@moustik

Could you tell me which jar to download and where please?

No, I do not know.

[kazurayam]

@moustik

I followed this tutorial : READ DATA FROM GOOGLE SHEET WITH SELENIUM | by Prashant Bhatasana | AppGambit | Medium

This article is dated at Aug,2019. It’s too old.

Google updates their technology so frequently that you should not assume that their products are the same as they were 3 year ago. You should refer to the current Google’s official documentations.

[moustik]

ok so I’m in the middle of the desert and no one can help me right now except you who can tell me where to go to find my way.

Thank u @kazurayam !

So, the best: is it to use .jar or to use Maven or Gradle?

is there a video tutorial that shows how to integrate Maven or Gradle with Katalon?

I used Maven with Eclipse in the past and didn’t need to download any .jar.

[kazurayam]

@moustik

So, the best: is it to use .jar or to use Maven or Gradle?

This would not be the question for you to ask.

Both of Maven and Gradle are tools that help you aquire necessary jars from external repositories of jars into local environment. In that term, Maven and Gradle have no great difference.

You just want to find the Google’s up-to-date guidance and follow what it tells you to. If it tells you to download jars from some web page manually without Maven or Gradle, OK, that would be enough. You would not need either of Maven and Gradle.

[kazurayam]

@moustik

how to integrate Maven or Gradle with Katalon?

I think, there is no such video.

The only resouce published by Katalon team concerning that theme is the following doc:

Dependencies Management with Native Gradle Support (Poc) | Katalon Docs

[bionel]

@moustik

So, the best: is it to use .jar

Even on the old version of the jar you are looking for it is mentioned Maven or Gradle, if you look carefully at the Readme.
Downloading the jar from whatever web will only bring to you that, without any dependency and whitout checking for conflicts

[moustik]

@kazurayam

follow what it tells you to

I follow what Google tells to do. I will search again on the net…

Thank @bionel but I didn’t quite understand…

My original problem is the error: ExceptionInInitializerError.

I don’t think it comes from Google API, it must be something I don’t understand in programming error…

When Does Java Throw the ExceptionInInitializerError? | Baeldung

I still don’t know how to fix this error

I repeat, the problem is in this line :

Sheets service = new Sheets.Builder(HTTP_TRANSPORT, JSON_FACTORY,getCredentials(HTTP_TRANSPORT)).setApplicationName(APPLICATION_NAME).build();

[bionel]

@moustik

My original problem is the error: ExceptionInInitializerError

your original problem is, you don’t understand how various java libraries depend on each other, and why central repositories are important (aside of using up-to-date libraries)
read a bit on this, please

[moustik]

Thank you Bionel !
if you have documentation, I’m interested.

[kazurayam]

@moustik

I didn’t quite understand…

My original problem is the error: ExceptionInInitializerError.

I don’t think it comes from Google API,

I already showed enough information to you.

In the Stacktrace, you can find a message:

Caused by: java.lang.IllegalStateException: No match found
	at com.google.api.client.googleapis.GoogleUtils.<clinit>(GoogleUtils.java:66)

This is the immediate point where a ExceptionInInitializationError occured. So you should read the source of GoogleUtils.java. I showed you the URL already.

I am afraid you, @moustik, ignored this information that I provided. You did not read the source code of GoogleUtil.java at all. You should note that the truth about programs always lies in the source code; nowhere else.

At the line#66 of the GoogleUtils.java, you can find the following statement:

63: static {
64:     Matcher versionMatcher = VERSION_PATTERN.matcher(VERSION);
65:     versionMatcher.find();
66:     MAJOR_VERSION = Integer.parseInt(versionMatcher.group(1)); // HERE THE ERROR OCCURED

versionMatcher.group(1) failed and complaied "No match found". So you need to think why versionMatcher.group(1) failed. … It seems that the VERSION variable had an unexpected value.

Then how the VERSION variable is initialized? … By the following code snipet:

35:  /** Current release version. */
36:  public static final String VERSION = getVersion();

How is the getVersion() method implemented?

91: private static String getVersion() {
    // attempt to read the library's version from a properties file generated during the build
    // this value should be read and cached for later use
    String version = null;
    try (InputStream inputStream =
        GoogleUtils.class.getResourceAsStream("google-api-client.properties")) {
      if (inputStream != null) {
        Properties properties = new Properties();
        properties.load(inputStream);
        version = properties.getProperty("google-api-client.version");
      }
    } catch (IOException e) {
      // ignore
    }
    return version == null ? "unknown-version" : version;
  }

The getVersion() method tries to load a properties file named google-api-client.properties, which must be bundled in some jar file. Which jar file? I find no clue in the GoogleUtils.java source file.

I looked around the GitHub repository, and found a file named google-api-client.properties.

googleapis/google-api-java-client/blob/main/google-api-client/src/main/properties/com/google/api/client/googleapis/google-api-client.properties

which contains a line:

google-api-client.version=${project.version}

The getVersion() method could possibly return a string which is either of

1. a string like "2.1.2" which is generated during a build process
2. something else; like "unknown-version".

You should note that, if the statement GoogleUtils.class.getResourceAsStream("google-api-client.properties")) failed for some reason, then the getVersion() will return a string unknown-version. When unknown-version is returned, then a "No match found" error will be raised.

That’s all I can trace for sure what could happen.

[kazurayam]

@moustik

I guess, you used the jar files which are incomplete somehow. But I do not know how and why. You should be able to look into the jar file and try to see if the “google-api-client.properties” is really contained in the jar. If contained, you should check how the file is coded. That’s the first thing you should check. I guess, you would find something interesting in the jar. If not, I have no more idea.

[kazurayam]

@moustik

I don’t think it comes from Google API,

…

I repeat, the problem is in this line :

Sheets service = new Sheets.Builder(HTTP_TRANSPORT, JSON_FACTORY,getCredentials

No, this problem does not reside in this line. This line is not immediately causing the “No match found” error. This line is just an entrance into a dark cave. The problem resides somewhere in the darkness.

[moustik]

@kazurayam This is my jars:
maybe there are jars that need to be removed or added ?

next :

next :

other way, i investigate on the net and i found one post :

Communiquer avec l'API Google Sheet en Java • Billets • Zeste de Savoir

I read all the post and i make some verification but always the same error : ExceptionInInitializerError

[kazurayam]

I have no more input. I will quite.

[bionel]

Looks like you have google-api-client 1.32.x which is quite old.
On Maven central, latest is 2.1.1

https://mvnrepository.com/artifact/com.google.api-client/google-api-client

So, I suggest, remove all of them, grab the latest from maven (using either maven or gradle) alongside all dependencies.

@kazurayam already provided some hints on how to do it, and if you search the forum you may find more tips and tricks on this matter.

e.g you can try the Grape trick:

How to validate the JSON response with expected JSON schema

So, here’s my approach. First, I add some .jar’s into my project: [Image: https://s3.amazonaws.com/katalon-forum/editor/4b/vcidfae4odcq.png] To grab them … you can manually search them into Maven central … or do a small trick: - Create a temporary custom keyword and put only this code into it (I name it Grab): package utilimport groovy.grape.Grape@Grab(group='org.everit.json', module='org.everit.json.schema', version='1.5.1')public class Grab {} - Once you hit ‘save’ katalon may looks like…

[bionel]

@moustik

if you have documentation, I’m interested.

Well, a quick view here:

How does a maven repository work? | Packagecloud Blog

The most important is:

One of the core features of Maven is its ability to handle Transitive Dependencies. That is, to find and download the dependencies of your dependencies, and their dependencies also , recursively, until they are all satisfied.

So, by using a certain dependency management tool (Maven, Gradle, Grape whatever) you are sure that all dependencies are satisfied properly.

Downloading manually from web, chances are high that you are missing something, or you can grab a wrong version for a certain library needed. Such is almost imposible to debug, you have to look into all of them code.

Another important thing is, use up to date libraries (as much as possible)
It has been already pointed that, the article you used as a source for your project is very old, it was written for java 1.7.
Katalon is not using the very latest java, but still, it is using openjdk8 which is newer.

Since that article was written, lot of libraries were updated.

Another thing to consider, Katalon is not a Maven project.
Maven it is not aware of whatever is installed.
So, by using Maven to grab everything needed, you may grab also duplicated libs, possible having a different version, thus conflicting with the libraries already present.

With this in mind, using the Gradle POC or the Grape trick, you have better chances to get propper versions (libraries already present in Katalon should be skipped)