In this Article, we are going to see how we can utilize Google sheet data for Test Automation. The important advantage is that now teams working from remote locations can use test data from cloud space.
Prerequisites for Integration
- Java 1.7 or greater
- Internet and a Web browser
- A Google account with Google Drive enabled
Steps to Configure Google Sheet service
1. Log into your Gmail account like xyz@gmail.com and navigate to the Link https://console.developers.google.com in other tab.
2. Click on “ Library” under API & services section at bottom of the page or navigate to link – https://console.developers.google.com/apis/library
3. Click on “Drive API” under G Suite section.
4. Create or select a project in the Google Developers Console and enable the Google API. Click Continue, then Go to credentials.
5. On the Add credentials to your project page, click the Cancel button.
6. At top of the page, OAuth consent screen tab is present. Provide an Email address, Product name if not already set, and then click the Save button.
7. a) Move to ‘Credentials’
b) Click the button named ‘Create credentials’ & select ‘OAuth 2.0 client IDs’.
8. Select the app type as Other, provide the name for “your App Name”, and click the Create button.
9. Click OK to dismiss the resulting dialog.
10. Click the download button to the right side of the client ID.
11. Move that file to your desire directory and rename it as client_secret.json
12. Open the Google Drive from your Gmail account.
13. Create a new Google spreadsheet and save it as testSheet.
14. Click the Share button and share the spreadsheet with the Client Id of yours (e.g. – xxx-developer.gsserviceaccount.com) that you created.
15. Modify the Access Link setting to Anyone.
16. Click Save.
Java Code for Integration
package com.mst.selenium.googlesheetapi;
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.jackson2.JacksonFactory;
import com.google.api.client.json.JsonFactory;
import com.google.api.client.util.store.FileDataStoreFactory;
import com.google.api.services.sheets.v4.SheetsScopes;
import com.google.api.services.sheets.v4.model.*;
import com.google.api.services.sheets.v4.Sheets;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
public class GoogleSheet {
private static final String APPLICATION_NAME = “GoogleApISpreadsheet”;
private static final DATA_STORE_DIR = new java.io.File(“D:\\Key\\sheets.googleapis.com-java-quickstart”);
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;
/** Username from Service GAPI */
public static String ar[] = new String[3];
public static int a;
private static String result;
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 =
new FileInputStream(“D:\\Keys\\client_secret.json”);
GoogleClientSecrets clientSecrets =
GoogleClientSecrets.load(JSON_FACTORY, new InputStreamReader(in));
GoogleAuthorizationCodeFlow flow =
new GoogleAuthorizationCodeFlow.Builder(
HTTP_TRANSPORT, JSON_FACTORY, clientSecrets, SCOPES)
.setDataStoreFactory(DATA_STORE_FACTORY)
.setAccessType(“offline”)
.build();
Credential = new AuthorizationCodeInstalledApp(
flow, new LocalServerReceiver()).authorize(“user”);
//System.out.println(“Credentials saved to ” + DATA_STORE_DIR.getAbsolutePath());
return credential;
}
public static Sheets getSheetsService() throws IOException {
Credential credential = authorize();
return new Sheets.Builder(HTTP_TRANSPORT, JSON_FACTORY, credential)
.setApplicationName(APPLICATION_NAME)
.build();
}
public static String ReadData(String sheetName,String tcName,String header) throws IOException {
// Build a new authorized API client service.
Sheets service = getSheetsService();
String spreadsheetId = “1uftswxV5RNWWYtgbbpX82Vh73UNwi8F7WkYKJbYj9S0”;
String range = sheetName+”!A:Z”;
ValueRange response = service.spreadsheets().values()
.get(spreadsheetId, range)
.execute();
String range1 = sheetName+”!A:Z”;
ValueRange response1 = service.spreadsheets().values()
.get(spreadsheetId, range1)
.execute();
service.spreadsheets().values().get(spreadsheetId, range).execute().getValues().toArray();
List<List<Object>> values = response.getValues();
if (values == null || values.size() == 0) {
System.out.println(“No data found.”);
} else {
for (List row : values) {
if ((row.get(0).toString()).equals(“TC ID”))
{
for(int x = 0; x<=100; x++)
{
if ((row.get(x).toString()).equals(header))
{
a = x;
//System.out.println(a);
x = 100;
}
}
}
if ((row.get(0).toString()).equals(tcName))
{
result = row.get(a).toString();
System.out.println(result);
}
}
}
return result;
}
public static void main(String[] args) throws IOException {
// Build a new authorized API client service.
Sheets service = getSheetsService();
String spreadsheetId = “1uftswxV5RNWWYtgbbpX82Vh73UNwi8F7WkYKJbYj9S0”;
String range = “Class Data!A2:E”;
ValueRange response = service.spreadsheets().values()
.get(spreadsheetId, range)
.execute();
List<List<Object>> values = response.getValues();
if (values == null || values.size() == 0) {
System.out.println(“No data found.”);
} else {
System.out.println(“Name, Major”);
for (List row : values) {
// Print columns A and E, which correspond to indices 0 and 4.
System.out.printf(“%s, %s\n”, row.get(0), row.get(4));
}
}
}
}
