Read data from Google sheet code

Nowaday, Google document is popular for working and one of open source tools can be replaced Microsoft Office. So Google have apis to support users access Google files via script. In this post I will show an example Java code to read data from Google sheet, You can reuse it for your automation framework incase you stored data in Google sheet files.

 

The first, you need get Google Authentication key by following steps:

Turn on the Google Sheets API

1. Use this wizard to create or select a project in the Google Developers Console and automatically turn on the API. Click Continue, then Go to credentials.
2. On the Add credentials to your project page, click the Cancel button.
3. 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.
4. Select the Credentials tab, click the Create credentials button and select OAuth client ID.
5. Select the application type Other, enter the name “Google Sheets API Quickstart”, and click the Create button.
6. Click OK to dismiss the resulting dialog.
7. Click the file_download (Download JSON) button to the right of the client ID.
8. Move this file to your working directory and rename it client_secret.json.

or find more detail at: https://developers.google.com/sheets/api/quickstart/java

The second, you need create a maven project with Inteliji and with pom.xml file look like below:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>

<groupId>groupId</groupId>
<artifactId>Sample</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<!-- https://mvnrepository.com/artifact/com.google.api-client/google-api-client -->
<dependency>
<groupId>com.google.api-client</groupId>
<artifactId>google-api-client</artifactId>
<version>1.22.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.google.oauth-client/google-oauth-client-jetty -->
<dependency>
<groupId>com.google.oauth-client</groupId>
<artifactId>google-oauth-client-jetty</artifactId>
<version>1.22.0</version>
</dependency>

<!-- https://mvnrepository.com/artifact/com.google.apis/google-api-services-sheets -->
<dependency>
<groupId>com.google.apis</groupId>
<artifactId>google-api-services-sheets</artifactId>
<version>v4-rev456-1.22.0</version>
</dependency>
</dependencies>
</project>

Third, copy client_secret.json into /src/main/resources 

And add QuickStart class : /src/main/java

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.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.Arrays;
import java.util.List;

public class Quickstart {
/** 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 =
Quickstart.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 static void main(String[] args) throws IOException {
// Build a new authorized API client service.
Sheets service = getSheetsService();

// Prints the names and majors of students in a sample spreadsheet:
// https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
String spreadsheetId = "1_2LIIZjd5_a9TCho_kE1pXW8iJrzdJQAX4sUrpvsqqU";
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));
}
}
}
}

Then run this code, the console output:

 

Hope this help!!!