Google Sheets allows users to create and format spreadsheets and simultaneously work with other people. The spreadsheets are used by consumers, businesses, schools to manage spreadsheet data. With the launch of Sheets API v4 test automation engineers also can interact with google sheets via Google Sheets API and can access, update spreadsheets like a normal user.
Advantages of using Google Drive for test automation;
*Can store the test data in an external file as shared repository.
*The reusability is high.
*The flexibility and maintainability of test scripts are high.
*Any change to the test data sheet will not hamper the test script.
Let’s see how to write or append test data to a Google Sheet.
To connect to the API and manipulate documents, need to add the ‘google-api-services-sheets’ and ‘google-oauth-client-jetty’ maven dependencies:
Create a sheet like below and make it as public. You can get the ID of the sheet from the link as highlighted.
Before access the sheets through an application, Google Sheets API will require OAuth 2.0 authorization. To get the credentials, need to create a project in the Google Developers Console and then enable the Google Sheets API for the created project.
Then go to the first step in the Google Quickstart guide and download the client configuration file ‘credentials.json’ by clicking on ‘Enable the Google Sheets API’ button.
Copy the downloaded ‘credentials.json’ file to ‘src/main/resources/’ in the working directory.
Below are the properties I’m going to use in my code. I have defined them in my framework property file which can be externally accessed.
Then create a class in your solution to maintain the Google Sheet operations. In my case the class name is ‘GoogleSpreadSheetHandler’. As the first step for interacting with Google Sheets, need to create a Sheets object which is the client for reading and writing through the API. That uses the ‘getCredentials’ method to get the credentials.
To write or append the test data to the sheet I have created a method called ‘appendDataToSpreadSheet’. Inside the method you have to retrieve following properties which we have defined inside the framework property file,
*spreadsheet ID: The ID of the spreadsheet to update
*Range : The range that you are going to use in the sheet.
*valueInputOption: controls how the input data will be added to the sheet (column-wise or row-wise).
*insertDataOption: How the input data should be inserted.
Above parameters are used to send the Google Sheets API request with the request body. The request body contains the data that I’m going to write to the Google Spread Sheet.
Your method call would be like below,
GoogleSpreadSheetHandler.appendDataToSpreadSheet(“a”,”b”,”c”);
Upon calling the method you would be able to write the test data to the Spread Sheet.
Cheers !!
Panduka Athauda 