Hi Community,
I followed this tutorial to read/write in a Google Sheet 
Integrating Google sheet with Katalon Studio | by Prashant Bhatasana | AppGambit | Medium
But, I have many issue with import (in step 3) and i have this message :
Groovy:unable to resolve class …
I try Ctrl+Maj+O but always the same error message.
1 - how to get rid of these import errors ?
2 - Has anyone ever managed to read and write in a Google sheet?
Thank you
hello… in short: import class…
longer … good habit is to show whole error message …
otherwise you will end-up like in that joke:
… flight log record: something is wrong in cabin …
… maintenance crew record: we fixed something …
It seems that you don’t understand how to resolve external dependencies for you test scripts.
The following document describes how to add the jars into your katalon project.
You FAILED to follow the tutorial. The tutorial writes:
Or else you need to add the following External Libraries in your Katalon Studio project.
For External Libraries GotoProject > Settings > External Libraries > Add new libraries.
google-api-client-1.22.0.jar
… (a lot more jars)
The how you can get the google-api-client-1.22.0.jar?
Answer: search for it in Google with keyword “Maven Central google-api-client”.
The Google will suggest you that you should visit: https://mvnrepository.com/artifact/com.google.api-client/google-api-client
In the page you will see a list of the versions of the google-api-client. You want to choose any one, and click the link. Say, you chose 2.0.0, then you will see https://mvnrepository.com/artifact/com.google.api-client/google-api-client/2.0.0
In the page you will find a link to the jar.
By clicking the link, you will get the jar file downloaded to your machine. Then you want to move the jar file into the Drivers folder of your project.
Repeat this processing for all other required jar files.
… tiresome? Yes, very much. However you have to do it. Otherwise, you would never be able to use google sheet in Katalon Studio.
Hi Andrej_Podhajsky,
I don’t understand any thing what you wrote.
This error message is the same for all the line in the Problems Pane :
Groovy:unable to resolve class com.google.api.client.extensions.java6.auth.oauth2
Groovy:unable to resolve class com.google.api.client.extensions.jetty.auth.oauth2.LocalServerReceiver
Groovy:unable to resolve class com.google.api.client.googleapis.auth.oauth2.GoogleAuthorizationCodeFlow
Groovy:unable to resolve class com.google.api.client.googleapis.auth.oauth2.GoogleClientSecrets
Groovy:unable to resolve class com.google.api.client.googleapis.javanet.GoogleNetHttpTransport
Groovy:unable to resolve class com.google.api.client.json.jackson2.JacksonFactory
Groovy:unable to resolve class com.google.api.services.sheets.v4.Sheets
Groovy:unable to resolve class com.google.api.services.sheets.v4.SheetsScopes
Groovy:unable to resolve class com.google.api.services.sheets.v4.model.ValueRange
This is the code :
package com.java.utilily
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;
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 = "< Application Name of Google API >";
private static final JsonFactory JSON_FACTORY = JacksonFactory.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 = "< Your client_secret.json file path >";
private static Credential getCredentials(final NetHttpTransport HTTP_TRANSPORT) throws IOException {
// Load client secrets.
InputStream in1 = ReadSpreadsheet.class.getResourceAsStream(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");
}
@Keyword
public static List<List<Object>> getSpreadSheetRecords(String range) throws IOException, GeneralSecurityException {
// Build a new authorized API client service.
final NetHttpTransport HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();
final String spreadsheetId = "< Your google sheet ID >";
Sheets service = new Sheets.Builder(HTTP_TRANSPORT, JSON_FACTORY, getCredentials(HTTP_TRANSPORT))
.setApplicationName(APPLICATION_NAME).build();
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 {
return values;
}
}
}
@kazurayam already done, see attachment.
Maybe I did my jar imports wrongly in the wrong folder or what?
Possibly you made some mistake. Only you can find it.
@kazurayam I don’t understand. some one can help me please ?
I have attached my screenshot.
I would like to read/write in a google sheet. Some one knows how ti can be possible to do this ?
Integrating Google sheet with Katalon Studio | by Prashant Bhatasana | AppGambit | Medium wrote that you need the
google-api-services-sheets-v4-rev483–1.22.0.jarHave you downloaded the jar and locate it into the project’s Drivers folder? I guess, not.
Please show us which jars your Drivers folder now contains.
Thak you @kazurayam but 1 have one error now.
I re-imported all the libraries and one is a problem because it is highlighted in red:
import com.google.api.client.json.jackson2.JacksonFactory;
it concerns this line:
private static final JsonFactory JSON_FACTORY = JacksonFactory.getDefaultInstance();
I removed it but it remains mandatory.
I am stuck again can someone help me please ?
I found solution.
i replace this import
import com.google.api.client.json.jackson2.JacksonFactory; // old
import com.google.api.client.json.GsonFactory; // old
by this import
com.google.api.client.json.gson.GsonFactory
and no red underline
Thank you very much for your answer