Introduction
nowadays google-sheet is part of every organization. either it is any personal work or organization framework. Its cloud-based nature allows team members to access and edit project data simultaneously, ensuring real-time updates and seamless coordination. Utilizing Google Sheets in project code enables developers to automate data manipulation tasks, such as fetching and updating information from external sources, analyzing project metrics, and generating reports. today we are going to learn how to read data from google-sheet.
Prerequisite
To implement google-sheet in your project. first, we need to create authorization credentials. There are 2 ways to setup your account
1) O-Auth Account Authorization
OAuth Account authorization is required when a third-party application trying to use your application or the developer want a user to verify for account. I have provided all steps to set up O-Auth Account here in 𝗦𝗲𝘁𝘂𝗽 𝗚𝗼𝗼𝗴𝗹𝗲 𝗖𝗹𝗼𝘂𝗱 𝗖𝗿𝗲𝗱𝗲𝗻𝘁𝗶𝗮𝗹𝘀 𝗢𝗔𝘂𝘁𝗵 𝟮.𝟬 𝗶𝗻 𝗼𝗿𝗱𝗲𝗿 𝘁𝗼 𝗮𝗰𝗰𝗲𝘀𝘀 𝗚𝗼𝗼𝗴𝗹𝗲 𝗦𝗵𝗲𝗲𝘁𝘀 𝗔𝗣𝗜𝘀 Blog
2) Service Account Authorization
Service Account authorization is required when a developer don't want a UI popup to verify from user. It should automatically happen without any manual intervention. This account is required when we are trying to setup automation frameworks. I have provided all steps to setup Service Account here in A stepwise Guide to create google Service account Blog
so I have divided this tutorial in two parts.
1 - Read data from google sheet using O-Auth Credentials
2 - Read data from google sheet using Service Account
1 - read data from google-sheet using O-Auth Authorization
Step 1: Set Up Google Sheets API
- Go to the Google Developers Console.
- Create a new project or select an existing one.
- Enable the Google Sheets API for your project.
- Create credentials (OAuth client ID) for a desktop application.
- Download the credentials file (a JSON file) and save it in resource folder. (for this example. i have given name as client_secret.json)
Step 2: Add Required Maven Dependencies
You need to add the necessary dependencies to your Java project. You can use Maven or Gradle to manage dependencies. For Maven, add the following to your pom.xml:
<dependency>
<groupId>com.google.api-client</groupId>
<artifactId>google-api-client</artifactId>
<version>1.23.0</version>
<exclusions>
<exclusion>
<groupId>com.google.guava</groupId>
<artifactId>guava-jdk5</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>com.google.oauth-client</groupId>
<artifactId>google-oauth-client-jetty</artifactId>
<version>1.23.0</version>
</dependency>
<dependency>
<groupId>com.google.apis</groupId>
<artifactId>google-api-services-sheets</artifactId>
<version>v4-rev493-1.23.0</version>
</dependency>
<dependency>
<groupId>com.google.auth</groupId>
<artifactId>google-auth-library-oauth2-http</artifactId>
<version>1.23.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/junit/junit -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
<scope>test</scope>
</dependency>
Step 3: get Google-sheet Id from Google-sheet
Create a new google Sheet in your service account. once you open google sheet in any browser, from your url get your sheet id.
Example
https://docs.google.com/spreadsheets/d/1942Y4Z6mbMpVxwd4RFPcG6OnnXQMJX5EQdGI0n5V0IA/edit#gid=0
red color string is google-sheet id.
Step 4: Authenticate with Google Sheets API
import automation.SheetsServiceUtil;
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.json.jackson2.JacksonFactory;
import com.google.api.client.util.store.MemoryDataStoreFactory;
import com.google.api.services.sheets.v4.Sheets;
import com.google.api.services.sheets.v4.SheetsScopes;
import com.google.api.services.sheets.v4.model.*;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.security.GeneralSecurityException;
import java.util.Arrays;
import java.util.List;
public class ReadingDataFromGoogleSheetWithOAuth{
private static Sheets sheetsService;
private static final String SPREADSHEET_ID = "Your Google Sheet Id";
private static final String APPLICATION_NAME = "Google Sheets O-Auth Example";
public static void main(String...args)throws Exception{
o_auth_authorize();
List ranges = Arrays.asList("Sheet1!A1:D4");
BatchGetValuesResponse readResult = sheetsService.spreadsheets().values()
.batchGet(SPREADSHEET_ID)
.setRanges(ranges)
.execute();
ValueRange sheetValues = readResult.getValueRanges().get(0);
for (List values : sheetValues.getValues()) {
for (Object value : values) {
System.out.println(value.toString());
}
}
}
public static void o_auth_authorize() throws IOException, GeneralSecurityException {
InputStream in = ReadingDataFromGoogleSheetWithOAuth.
class.getResourceAsStream("/client_secret.json");
GoogleClientSecrets clientSecrets = GoogleClientSecrets.load(
JacksonFactory.getDefaultInstance(), new InputStreamReader(in));
List scopes = Arrays.asList(SheetsScopes.SPREADSHEETS);
GoogleAuthorizationCodeFlow flow = new GoogleAuthorizationCodeFlow.Builder
(GoogleNetHttpTransport.newTrustedTransport(),
JacksonFactory.getDefaultInstance(), clientSecrets, scopes)
.setDataStoreFactory(new MemoryDataStoreFactory())
.setAccessType("offline").build();
Credential credential = new AuthorizationCodeInstalledApp(flow,
new LocalServerReceiver()).authorize("user");
sheetsService = new Sheets.Builder(GoogleNetHttpTransport.newTrustedTransport(),
JacksonFactory.getDefaultInstance(), credential).setApplicationName(APPLICATION_NAME).build();
}
Explanation:
InputStream in = ReadingDataFromGoogleSheetWithOAuth
.class.getResourceAsStream("/client_secret.json");
1 - we are reading client_secret.json file. file should be present in resource folder
GoogleAuthorizationCodeFlow flow = new GoogleAuthorizationCodeFlow.Builder
(GoogleNetHttpTransport.newTrustedTransport(),
JacksonFactory.getDefaultInstance(), clientSecrets, scopes)
.setDataStoreFactory(new MemoryDataStoreFactory())
.setAccessType("offline").build();
Credential credential = new AuthorizationCodeInstalledApp(flow, new LocalServerReceiver())
.authorize("user");
sheetsService = new Sheets.Builder(GoogleNetHttpTransport.newTrustedTransport(),
JacksonFactory.getDefaultInstance(), credential).setApplicationName(APPLICATION_NAME).build();
2 - GoogleAuthorizationCodeFlow class will set all scope and clientSecrets and access type as offline.
Credential Object will receive Sheets object which we can use get read data from sheets. this code mainly useful to authenticate google sheet.
BatchGetValuesResponse readResult = sheetsService.spreadsheets().values()
.batchGet(SPREADSHEET_ID)
.setRanges(ranges)
.execute
3 - This code internally trigger spreadsheet services and get BatchGetValueResponse for ranges which we have provided in List of range
ValueRange sheetValues = readResult.getValueRanges().get(0);
for (List values : sheetValues.getValues()) {
for (Object value : values) {
System.out.println(value.toString());
}
}
4 - Here we are reading response received from service. and iterating using foreach loop.
so here we have completed our part 1 to read google sheet using O-Auth authorization.
2 - read data from google-sheet using Service Account Authorization
Step 1: Set Up Google Sheets API
- Go to the Google Developers Console.
- Create a new project or select an existing one.
- Enable the Google Sheets API for your project.
- Create credentials for Service Account
- Download the credentials file (a JSON file) and save it in resource folder. (for this example. i have given name as service-account-auth.json)
Step 2: Add Required Maven Dependencies
You need to add the necessary dependencies to your Java project. You can use Maven or Gradle to manage dependencies. For Maven, add the following to your pom.xml:
<dependency>
<groupId>com.google.api-client</groupId>
<artifactId>google-api-client</artifactId>
<version>1.23.0</version>
<exclusions>
<exclusion>
<groupId>com.google.guava</groupId>
<artifactId>guava-jdk5</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>com.google.oauth-client</groupId>
<artifactId>google-oauth-client-jetty</artifactId>
<version>1.23.0</version>
</dependency>
<dependency>
<groupId>com.google.apis</groupId>
<artifactId>google-api-services-sheets</artifactId>
<version>v4-rev493-1.23.0</version>
</dependency>
<dependency>
<groupId>com.google.auth</groupId>
<artifactId>google-auth-library-oauth2-http</artifactId>
<version>1.23.0</version>
</dependency>
Step 3: get Google-sheet Id from Google-sheet
Create a new google Sheet in your service account. once you open google sheet in any browser, from your url get your sheet id.
Example
https://docs.google.com/spreadsheets/d/1942Y4Z6mbMpVxwd4RFPcG6OnnXQMJX5EQdGI0n5V0IA/edit#gid=0
red color string is google-sheet id.
Step 4: Authenticate with Google Sheets API
import java.io.IOException;
import java.io.InputStream;
import java.security.GeneralSecurityException;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Objects;
import automation.SheetServiceUtils2;
import com.google.api.client.googleapis.javanet.GoogleNetHttpTransport;
import com.google.api.client.http.javanet.NetHttpTransport;
import com.google.api.client.json.gson.GsonFactory;
import com.google.api.services.sheets.v4.SheetsScopes;
import com.google.auth.oauth2.ServiceAccountCredentials;
import com.google.api.services.sheets.v4.Sheets;
import com.google.api.services.sheets.v4.model.BatchGetValuesResponse;
import com.google.api.services.sheets.v4.model.ValueRange;
public class GoogleSheetsExampleServiceAccount {
private static Sheets sheetsService;
private static final String APPLICATION_NAME = "Google Sheets Service Account Example";
private static final List AUTH_SCOPES = List.of(SheetsScopes.SPREADSHEETS);
private static final GsonFactory JSON_FACTORY = GsonFactory.getDefaultInstance();
private static ServiceAccountCredentials credentials;
private static final String SPREADSHEET_ID = "Google-Sheet Id";
public static void getSheetsService() throws IOException, GeneralSecurityException {
if (sheetsService == null) {
NetHttpTransport httpTransport = GoogleNetHttpTransport.newTrustedTransport();
sheetsService = new Sheets.Builder(httpTransport, JSON_FACTORY, request -> {
})
.setApplicationName(APPLICATION_NAME)
.build();
}
}
public static void getCredentials() throws Exception {
getSheetsService();
if (credentials != null) {
if (credentials.getAccessToken().getExpirationTime().before(new Date())) {
credentials.refresh();
}
return;
}
try (InputStream in =
GoogleSheetsExampleServiceAccount.class
.getResourceAsStream("/service-account-auth.json")) {
credentials = (ServiceAccountCredentials) ServiceAccountCredentials
.fromStream(Objects.requireNonNull(
GoogleSheetsExampleServiceAccount.class
.getResourceAsStream("/service-account-auth.json")))
.createScoped(AUTH_SCOPES);
credentials.refresh();
}
}
public static void main(String...args)throws Exception{
List ranges = Arrays.asList("Sheet1!A1:A4","Sheet1!B1:B4");
getCredentials();
BatchGetValuesResponse readResult = sheetsService.spreadsheets().values()
.batchGet(SPREADSHEET_ID)
.setRanges(ranges)
.setAccessToken(credentials.getAccessToken().getTokenValue())
.execute();
ValueRange firstColumn = readResult.getValueRanges().get(0);
for(List values : firstColumn.getValues()){
for(Object value : values){
System.out.println(value.toString());
}
}
ValueRange secondColumn = readResult.getValueRanges().get(1);
for(List values : secondColumn.getValues()){
for(Object value : values){
System.out.println(value.toString());
}
}
}
}
Here I have included my complete git-hub repository to learn google sheet implementation
