...

How to Integrate Google Sheets with Selenium

Published by Venkatesh Ganji

On October 5, 2020
Google Sheet with Selenium Title card

Microsoft Excel is easily one of the most versatile and essential software in Test Automation framework to manage the test data and track the status back. But because Excel is mainly used on individual computers, it is challenging for users to access their documents from anywhere or to collaborate with their team in real time. Emailing files back and forth can get confusing and no one is sure which version is the right one.

Google Sheets is cloud-based spreadsheet software is the best alternatives available if you are working on the Data-driven framework.

In this article, we going to use Google spreadsheets as the data source to read the data from Selenium and pass on to a website.

Some of the key advantages of using Google Sheets:

  • Easy to use
  • Built for collaboration
  • Built-in Version Control
  • Better visibility
  • Access to your test data from any computer

Pre-Requisites:

  • Java 1.7 or greater
  • Access to the internet and a web browser
  • A Google account with Google Drive enabled

Google Drive Configuration:

  1. Login to your Gmail account and open the URL https://console.developers.google.com/
  2. Create a new project.

3. Then after you need to enable google sheets API library

  • Navigate to Library
  • Search for API “Google sheets API”
  • Enable “Google sheets API”

4. After successful Google Sheets API enabled, the screen looks like this.

5. Create OAuth Client ID, create Credentials → OAuth client ID

6. Fill the details and complete the OAuth client ID.

7. After creating OAuth key, it will display the Client ID and click the OK button. Then after the download the JSON file (Download JSON) button to the right of the client ID.

8. Rename the Jason file to json and copy to project directory.

9. Then after create Service account from create Credentials à Service account.

10. Create a Google sheet with login credentials to pass on to a website. Copy the document id from the URL: https://docs.google.com/spreadsheets/d/<documentID>/edit#gid=0

11. Share the file with service account (xxx-developer.gsserviceaccount.com) which you have created above and give editor access.

Google Sheets integration with Selenium:

So far, we have configured the google drive, OAuth account, Service account, Shared google sheet. It’s time for the integration.

Download the following libraries and add them to your java project.

Code Snippet for passing the data to a web application:

Published by Venkatesh Ganji

On October 5, 2020

Recent Posts

Lunch & Learn: The Power of Automated Serverless Data Pipelines

May 14th, 2024 11:30 am - 1:00 pm In-Person and VirtualLunch & Learn: The Power of Automated Serverless Data Pipelines * **Note: This event will be held in-person. There will also be a virtual option for those that can not make it in-person. Please choose your...

5 Challenges Companies Face Implementing Digital Solutions in 2024

Digital transformation has been revolutionizing industries, from the way we connect with customers to how we optimize internal processes. Despite the many advantages of going digital, companies around the world encounter a host of challenges when implementing these...

Software Security and Ethical Hacking for Developers

February 21st, 2024 11:30 am - 1:00 pm In-Person and VirtualSoftware Security and Ethical Hacking for Developers * **Note: This event will be held in-person. There will also be a virtual option for those that can not make it in-person. Please choose your ticket...

Make your next software development project a Smart One.

Venkatesh Ganji