How to Integrate Google Sheets with Selenium

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

  
  

Configure the google drive, OAuth account, Service account, Shared google sheet.

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.

• Download google-api-java-client jars from – http://search.maven.org/remotecontent?filepath=com/google/api-client/google-api-client-assembly/1.20.0/google-api-client-assembly-1.20.0-1.20.0.zip

• Extract the downloaded folder and add the following jars in your project:

  • google-api-client-1.20.0.jar

  • google-api-client-jackson2-1.20.0.jar

  • google-http-client-1.20.0.jar

  • google-oauth-client-1.20.0.jar

  • google-oauth-client-java6-1.20.0.jar

  • google-oauth-client-jetty-1.20.0.jar

  • jetty-6.1.26.jar

  • jetty-util-6.1.26.jar

Code Snippet for reading the data from Google Sheet:

Code Snippet for passing the data to a web application: