Read write on a google sheet

Hi Community,

I followed this tutorial to read/write in a Google Sheet :slight_smile: 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

1 Like

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 Goto Project > 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.

1 Like

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 ?
:smiling_face_with_tear: :sob: :sob: :sob:

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

Have 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 ?

:sob: :sob: :sob: :smiling_face_with_tear: :sob:

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