Reading data from google spreadsheet

Until today, to perform data-driven testing, we have used libraries like Apache POI, JExcel etc to read/write Excel files. To work with these libraries, we have to create spreadsheet, save in a repository and we need to write much amount of code to access the spreadsheet and test all the data dependent scenarios.

This becomes problem in maintainability for automation test scripts when the test data is growing and keep adding files to the framework. Every time when we update the test data, we have to check-out / check-in from the framework to make sure our test works.

Instead of having them physically available, we can create an google spreadsheet to store the test data and access the data using Google Sheets API’s.

Why Google Spreadsheet?

1. We can maintain multiple spreadsheets which will be a central repository for automation Test Data.
2. We don't to check-out/Check-in to change / update test data.
3. Lesser amount of code is required to get data, Google API's made it easy for us.
4. If you want to share this test data, just share the URL.
5. No data loss.

Getting started with the Google Sheets API

Google Sheets is an online spreadsheet application that lets users create and format spreadsheets and simultaneously work with other team members.

We will try to access spreadsheet data using Google Sheets API v4. Using this API, our code can access and update spreadsheets just like any other user.

Prerequisites :

1. Jave 1.7 or greater.
2. Gradle 2.3 or greater / Maven
3. Google account

The important step before proceeding to an example, we should first turn on the Google Sheets API.

a. Use Google Developers Console wizard to create or select a project and automatically turn on the API. Click Continue, then Go to credentials.

b. On the Add credentials to your project page, click the Cancel button.

c. At the top of the page, select the OAuth consent screen tab. Select an Email address, enter a Product name if not already set, and click the Save button.

d. Select the Credentials tab, click the Create credentials button and select OAuth client ID.

e. Select the application type Other, enter the name "Google Sheets API for Selenium", and click the Create button.

Google Spreadsheet oAuth for selenium

f. Click OK to dismiss the resulting dialog.

g. Click the file_download (Download JSON) button to the right of the client ID.

Google Spreadsheet client id for selenium

h. Move this file to your working directory and rename it client_secret.json<.

Let's see how to use this API to read data from Spreadsheet, here we are going to use Maven to get dependencies for Google Spreadsheet API v4.

Create a simple maven project and add below dependencies to work with Google Sheets API.

<dependency>
    <groupId>com.google.api-client</groupId>
    <artifactId>google-api-client</artifactId>
    <version>1.22.0</version>
</dependency>
<dependency>
    <groupId>com.google.oauth-client</groupId>
    <artifactId>google-oauth-client-jetty</artifactId>
    <version>1.22.0</version>
</dependency>
<dependency>
    <groupId>com.google.apis</groupId>
    <artifactId>google-api-services-sheets</artifactId>
    <version>v4-rev483-1.22.0</version>
</dependency>

Below is a sample code in Java for accessing the Google Spreadsheeet. You can also add the following jars as maven dependencies to perform an tests using selenium and testng.

<dependency>
    <groupId>org.testng</groupId>
    <artifactId>testng</artifactId>
    <version>6.10</version>
</dependency>
    <dependency>
        <groupId>org.seleniumhq.selenium</groupId>
        <artifactId>selenium-java</artifactId>
        <version>3.4.0</version>
    </dependency> 

First create a class named GoogleSheetAPI.java and copy the below code:-

package spreadsheetAutomation;

import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.Arrays;
import java.util.List;

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.json.JsonFactory;
import com.google.api.client.json.jackson2.JacksonFactory;
import com.google.api.client.util.store.FileDataStoreFactory;
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;

public class GoogleSheetAPI {
    /** Application name. */
    private static final String APPLICATION_NAME = "Google Sheets API Java Quickstart";

    /** Directory to store user credentials for this application. */
    private static final java.io.File DATA_STORE_DIR = new java.io.File(
        System.getProperty("user.home"), ".credentials/sheets.googleapis.com-java-quickstart");

    /** Global instance of the {@link FileDataStoreFactory}. */
    private static FileDataStoreFactory DATA_STORE_FACTORY;

    /** Global instance of the JSON factory. */
    private static final JsonFactory JSON_FACTORY = JacksonFactory.getDefaultInstance();

    /** Global instance of the HTTP transport. */
    private static HttpTransport HTTP_TRANSPORT;

    /** Global instance of the scopes required by this quickstart.
     *
     * If modifying these scopes, delete your previously saved credentials
     * at ~/.credentials/sheets.googleapis.com-java-quickstart
     */
    private static final List<String> SCOPES = Arrays.asList(SheetsScopes.SPREADSHEETS_READONLY);

    static {
        try {
            HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();
            DATA_STORE_FACTORY = new FileDataStoreFactory(DATA_STORE_DIR);
        } catch (Throwable t) {
            t.printStackTrace();
            System.exit(1);
        }
    }

    /**
     * Creates an authorized Credential object.
     * @return an authorized Credential object.
     * @throws IOException
     */
    public static Credential authorize() throws IOException {
        // Load client secrets.
        InputStream in =
        GoogleSheetAPI.class.getResourceAsStream("/client_secret.json");
        GoogleClientSecrets clientSecrets = GoogleClientSecrets.load(JSON_FACTORY, new InputStreamReader(in));

        // Build flow and trigger user authorization request.
        GoogleAuthorizationCodeFlow flow = new GoogleAuthorizationCodeFlow.Builder(HTTP_TRANSPORT, JSON_FACTORY, clientSecrets, SCOPES)
                .setDataStoreFactory(DATA_STORE_FACTORY)
                .setAccessType("offline")
                .build();
        Credential credential = new AuthorizationCodeInstalledApp(flow, new LocalServerReceiver()).authorize("user");
        System.out.println("Credentials saved to " + DATA_STORE_DIR.getAbsolutePath());
        return credential;
    }

    /**
     * Build and return an authorized Sheets API client service.
     * @return an authorized Sheets API client service
     * @throws IOException
     */
    public static Sheets getSheetsService() throws IOException {
        Credential credential = authorize();
        return new Sheets.Builder(HTTP_TRANSPORT, JSON_FACTORY, credential)
                .setApplicationName(APPLICATION_NAME)
                .build();
    }
    
	public List<List<Object>> getSpreadSheetRecords(String spreadsheetId, String range) throws IOException {
		Sheets service = getSheetsService();		
        ValueRange response = service.spreadsheets().values()
                .get(spreadsheetId, range)
                .execute();
        List<List<Object>> values = response.getValues();
        if (values != null && values.size() != 0) {
        	return values;
        } else {
            System.out.println("No data found.");
            return null;
        }
	}
}

In the above class, we have defined a method 'getSpreadSheetRecords(String spreadsheetId, String range)' which does the job to return the required data based on the spreadsheetId and range we specify.

spreadsheetId: -

Method requires a spreadsheetId parameter which is used to identify which spreadsheet is to be accessed or altered. This ID is the value between the "/d/" and the "/edit" in the URL of your spreadsheet.

For example, consider the following URL that references a Google Sheets spreadsheet:

https://docs.google.com/spreadsheets/d/1qpyC0XzvTcKT6EISywvqESX3A0MwQoFD...

range: -

Method require a range in A1 notation. This is a string like Sheet1!A1:B2, that refers to a group of cells in the spreadsheet. You can see the name of the sheet on the tab near the bottom of the screen. It defaults to Sheet 1.

For example, valid ranges are: -
Sheet1!A1:B2 refers to the first two cells in the top two rows of Sheet1.

A1:B2 refers to the first two cells in the top two rows of the first visible sheet.

If we specify range as "A1:B2" is represented as [['a1', 'b1'], ['a2', 'b2']] (where the strings are place holders for actual content of those cells in Sheet1)

NOTE:
If the sheet name has spaces or starts with a bracket, surround the sheet name with single quotes ('), e.g 'Sheet One'!A1:B2. For simplicity, it is safe to always surround the sheet name with single quotes.

Below is the test Data which is in Google Spreadsheet

Google Spreadsheet test data for selenium

Now let us try to create a class with one scenario to access google spreadsheet and validate data returned by selenium.

package spreadsheetAutomation;

import java.io.IOException;
import java.util.Arrays;
import java.util.List;

import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.openqa.selenium.support.ui.WebDriverWait;
import org.testng.Assert;
import org.testng.annotations.AfterClass;
import org.testng.annotations.BeforeClass;
import org.testng.annotations.Test;

public class automationTest {
	
	public WebDriver driver;
	public WebDriverWait wait;
	String appURL = "appURL";

	private String spreadsheetId = "1C6yrt6yr27luKlbclkOYFmTdL8tD5Wfh__dIen0"; 
	private String range = "UserInfo!A3:E";
	String userName = "johns";
	String password = "test@123";
	
	@BeforeClass
	public void testSetup() {
		System.setProperty("webdriver.gecko.driver","path of geckodriver.exe");
		driver=new FirefoxDriver();
		wait = new WebDriverWait(driver, 5);
	}

	//Verify user profile information after login
	@Test
	public void verifyProfileInfo() throws IOException {
		login(userName,password);
		String[] userProfileInfo = getProfileInfo();
		List<String> userData = Arrays.asList(userProfileInfo);
		//Get data from sheet  and verify first profile info of john
		GoogleSheetAPI sheetAPI = new GoogleSheetAPI();
		List<List<Object>> values = sheetAPI.getSpreadSheetRecords(spreadsheetId, range);
		for (List<Object> row : values) {
			if(row.get(0).equals(userName)) {
			Assert.assertEquals(userData, row);
			break;
			}
		}
	}
	
	public String[] getProfileInfo() {
		//Navigate to Profile page 
		driver.findElement(By.id("user-profile")).click();
		String firstName = driver.findElement(By.id("firstname")).getText();
		String lastName = driver.findElement(By.id("lastname")).getText();
		String age = driver.findElement(By.id("user-age")).getText();
		String sex = driver.findElement(By.id("user-sex")).getText();
		String[] arrayUserInfo = {firstName, lastName, age, sex};
		return arrayUserInfo;
	}
	
	public void login(String userName, String password) {
		driver.findElement(By.id("session_key-login")).sendKeys(userName);
		driver.findElement(By.id("session_password-login")).sendKeys(password);
		driver.findElement(By.id("signin")).click();
	}
	
	@AfterClass
	public void tearDown() {
		if(driver!=null) {
		driver.quit();
		}
	}

}

In the above code, we have single test method 'verifyProfileInfo()' which will validate user profile information firstName, lastName, age, and sex once the user login.

There are other two methods 'login(username,password)' which helps to login to the application and 'getProfileInfo()' will get text and returns a string array with the profile information.

There are two lines of code in test method 'verifyProfileInfo()' which gets the spreadsheet data for us.

GoogleSheetAPI sheetAPI = new GoogleSheetAPI();
List<List<Object>> values = sheetAPI.getSpreadSheetRecords(spreadsheetId, range);

'values' will have all the spreadsheet data (rows and columns). We will iterate each row and verify if the username has profile information in the sheet.

If the username is found in the sheet, we will try to compare both the list o data. Here a list is which returned by 'getProfileInfo()' using selenium and other list is a row.

Google Spreadsheets have many settings that let you to interact with the spreadsheet. Reading/writing cell values only (via the spreadsheets.values collection) and Reading/writing any aspect of the spreadsheet (via the spreadsheets collection).

Selenium Tutorials: 

Comments

hi,
is it possible to write back to the sheets available in Google Sheets?

Yes,This is possible to write

Good Article

That's a good article. I would like to implement but is it priced? Is it free? I had trouble searching for the pricing. Please help me. Thanks!

You can use it for free.

I have a google sheet with 4 sheets. It is possible to get all data with 1 call. I have to perform 1 call to get the sheets and after that 1 call for each sheet to get data from it

I am not able to read clinet_secret.json file , it is throwing null pointer .
what is the reason for the same.
where should I keep json file can someone comment on above.

How to write the data on google sheet using Selenium .

Add new comment

CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.