In this tutorial , we will elaborately look how to integrate Google Sheets Selenium in Java Project ,In the world of selenium data-driven frameworks, there are multiple types of data sources available for the creators to make use of according to their needs, it can be either an excel sheet, CSV, XML, JSON, or even it can be a database connection. There are advantages as well as disadvantages in each one, let’s not go through those for now, in this dedicated post we are going to see how we can use a cloud-based excel sheet as a data source for selenium automation scripts. Let’s begin
Table of Contents
Prerequisite
Here I am mentioning only the discrete dependencies we have used in this tutorial, other dependencies such as Maven, Selenium, Java, Rest Assured are self implicated.
Google API Dependency JAR files
BestBuy API Playground setup
- Clone BestBuy API Playground from the below github URI.
- Install NodeJS in your system.
- Open Command prompt and change it to BestBuy project folder.
- Start the BestBuy api-playground project using below command.
npm install
npm start
- Now the BestBuy api-playground will be started to run at http://localhost:3030
Steps to Create Google Developer Service account
- Navigate to https://console.developers.google.com/
- Create a New Project.
- Navigate to Credentials.
- Create New Service Account.
Copy the service account email id and keep it for reference.
Make sure to keep safe the client secret json file, once it is lost or corrupted, we have to create new key and use it.
Steps to Create, Share Spreadsheet to the Service account to integrate Google Sheets Selenium
- Create a spreadsheet in our primary account.(from our own account).
- Share the spreadsheet with the service account we just created (service account mail id we have created in the previous step) and select the role as Editor, and click Send.
Copy the id of the spreadsheet from the URL.
docs.google.com/spreadsheets/d/XXXXXXXXXXXXXXXXX/edit
XXXXX value is the id of your spreadsheet
Mechanism behind Google Sheets Selenium API Authorization flow
- Getting Credential instance by passing client secret and client id
- Getting Spreadsheet instance by passing Credential object along with our exclusive Project Name
- Retrieve or Insert values by Range function using the Spreadsheet instance.
- Retrieving/modifying the workbook using BatchUpdate method based on the ownership access level
Changes needed before running your tests
Here is my GitHub project for your reference Google Sheets Selenium
https://github.com/vigneshram20/GoogleSheetsRestAssured.git
Kindly clone the project and make the necessary changes as cited below before starting executing the tests.
In GoogleSheets.java,
Line No: 5 – Change existingSpreadSheetID to your spreadsheet id
Line No: 6 – Change credentialPath to your Client Secret Path
In Google Drive SpreadSheet,
1. Create a Spread Sheet named TestData (case sensitive).
2. Add the following columns and the test data (case sensitive).
Live Action!
- Run testng.xml file as TestNG Suite
That’s it, folks, it’s a lengthy post I admit, but I hope it could serve the purpose, Comment me if you have any questions or facing any challenges, I am happy to help you 🙂
Also Read : How to Use Owasp Dependency Maven Plugin to detect vulnerabilities in Java
