Integrating Google Sheets API With Katalon Studio Using a Service Account

Sep 25, 2023 | by Ralph Van Der Horst

Introduction

I revamped a response I made in one of the katalon forum back in those days :-). For one of my customers I made a keyword api with a service account which could read and write to a google sheets.

In automated testing, Katalon Studio is being recognized as a comprehensive and robust platform, enabling the integration of various services for enhancing testing workflows. In this tutorial, we are demonstrating how you can seamlessly integrate Google Sheets API with Katalon Studio using a service account. This integration is very beneficial for testers who want to use spreadsheet data in their test cases.

Prerequisites

Google Cloud Account: You are needing a Google Cloud account for accessing the Google Sheets API and for creating a service account.
Katalon Studio: Ensure that Katalon Studio is installed and set up on your system.

Step 1: Enable Google Sheets API

Go to the Google Cloud Console.
Select or create a new project.
Go to ‘API & Services’ -> ‘Dashboard’.
Enable the Google Sheets API for your project.
Create credentials for a service account and download the JSON key file. note: you need to be admin to setup service account, so align with your admin

Share your Google Sheet with the email address associated with the service account and give the necessary permissions.

Step 3: Set up Katalon Studio

Open Katalon Studio and make a new project.
Go to Project -> Settings -> External Libraries, and add the Google Sheets API client library JAR files to your project. The library can be downloaded from the Google Sheets API Java Client page. (via graddle or maven)

Step 4: Create a Custom Keyword in Katalon Studio

Make a new package within Keywords, then inside the package, create a new keyword. Here is an example implementation of the keyword for reading data from Google Sheets:



package com.katalon.keyword;



// Import needed libraries



public class GoogleSheetReader {



   private static final String APPLICATION_NAME = "Google Sheets API Java Quickstart";

   private static final JsonFactory JSON_FACTORY = JacksonFactory.getDefaultInstance();



   @Keyword

   def readSheetData(String spreadsheetId, String range) {

       // Implementation to read data from Google Sheets

   }

}