We read Google tables from the web application
Google has several APIs for accessing its spreadsheets. Let's figure out how to read data from spreadsheet tables in a java web application using API version 4.
Create a new project through the google console .
Activate Sheets API.
To use the selected API, you need to create credentials. We will call the API from the browser.
')
Create an OAuth 2 client ID and set the URL restrictions. It is necessary to specify both productive and development url.
The access request window can also be customized by specifying the display name, logo and license.
The output should be the credential file client_secrets.json. The finished file must then be placed in the resources of your project.
Go ahead. The Google Sheets API v4 supports various scripts for authorization using an authorization code:
Scripts for web and client applications are suitable for us. The scheme of work for them is common:
The first step is to request the google authorization key. The user will be shown the form of access. Having received an authorization code, it must be exchanged for an access token, without which it is impossible to communicate with the Google API. The sequence of actions can be observed in the OAuth 2.0 sandbox .
We take spring boot as the basis for a web application. The dependencies are as follows:
Create two services. GoogleConnection will load client data from a local file and store identification data after receiving it.
And GoogleSheets will perform the main work - read the tabular data.
We distribute the entire sequence of operations among the three controllers. Controller for authorization.
The result of his work will be a redirect to google for login.
Then request the access of the google application to the user tables:
In case of successful authorization, the feedback controller will exchange the code for tokens and redirect to the original url. For the exchange itself is responsible class GoogleAuthorizationCodeTokenRequest .
And, in fact, a working controller that implements the reading of tabular data.
We also need an interceptor so that it is impossible to access the working controller without authentication.
Compared to the API v3, each line is a list of objects.
Using A1 notation, we read page by page, not in blocks. To do this, add the page id parameter and the tab name in the application settings.
We start. We are checking.
As a result, you should get an idea of which Google API classes are used to connect to your OAuth 2.0 services and how you can use them from a web application.
The application code responsible for working with OAuth2 can be simplified with spring. To do this, connect Spring Security OAuth .
This will allow us to hide the OAuth2 routine operations under the hood and protect our application.
Let's transfer user secrets in application.properties.
Having connected security to the project, we have already activated basic authentication . Replace it with something more suitable.
Since we read the data from Google, even if it is engaged in user authentication for our application. To do this, add just one annotation @ EnableOAuth2Sso.
An SSO authentication point will be created and configured. No need to override the WebSecurityConfigurerAdapter. We just have to set a couple of parameters in the configuration.
In this case, the login-path must match the URI for the redirect given in the google project. And the scope parameter must contain the profile value as well.
Additional controllers and the interceptor are no longer needed. Now their work will do spring.
Modify the GoogleConnection class. Credential's we will create using an authorization code stored after authentication in OAuth2 context. And we will take client data from the application config.
Displaying data in the browser, error handling, use of the session, logout and other things we leave without consideration. Their availability and configuration will depend on the specific requirements.
That's all. Working source is on github'e . Different approaches - in different branches.
Google app
Create a new project through the google console .
Activate Sheets API.
To use the selected API, you need to create credentials. We will call the API from the browser.
')
Create an OAuth 2 client ID and set the URL restrictions. It is necessary to specify both productive and development url.
The access request window can also be customized by specifying the display name, logo and license.
The output should be the credential file client_secrets.json. The finished file must then be placed in the resources of your project.
Authorization Scripts
Go ahead. The Google Sheets API v4 supports various scripts for authorization using an authorization code:
- For web server applications. You need to implement a couple of defined servlets and add them to your web.xml
- Service accounts. Service accounts are created on google and provide clients with access to their resources, and not to user resources.
- For installed applications. An example of working with API from a console application.
- For client applications. In the browser, we form a request for obtaining an access code, which can then be exchanged for tokens.
- For android.
Scripts for web and client applications are suitable for us. The scheme of work for them is common:
The first step is to request the google authorization key. The user will be shown the form of access. Having received an authorization code, it must be exchanged for an access token, without which it is impossible to communicate with the Google API. The sequence of actions can be observed in the OAuth 2.0 sandbox .
Google oauth2 app
We take spring boot as the basis for a web application. The dependencies are as follows:
<!-- Google OAuth Client Library for Java. --> <dependency> <groupId>com.google.oauth-client</groupId> <artifactId>google-oauth-client-java6</artifactId> <version>${google.oauth.client.version}</version> </dependency> <!-- Google OAuth2 API V2 Rev124 1.22.0 --> <dependency> <groupId>com.google.apis</groupId> <artifactId>google-api-services-oauth2</artifactId> <version>${google.oauth2.version}</version> </dependency> <!-- Google Sheets API V4 Rev38 1.22.0 --> <dependency> <groupId>com.google.apis</groupId> <artifactId>google-api-services-sheets</artifactId> <version>${google.sheets.version}</version> </dependency> Create two services. GoogleConnection will load client data from a local file and store identification data after receiving it.
GoogleConnectionService.java
@Service public class GoogleConnectionService implements GoogleConnection { private static final String CLIENT_SECRETS = "/client_secrets.json"; // .. @Override public GoogleClientSecrets getClientSecrets() { if (clientSecrets == null) { try { // load client secrets InputStreamReader clientSecretsReader = new InputStreamReader(getSecretFile()); clientSecrets = GoogleClientSecrets.load(Global.JSON_FACTORY, clientSecretsReader); } catch (IOException e) { e.printStackTrace(); } } return clientSecrets; } @Override public Credential getCredentials() { return credential; } // .. } And GoogleSheets will perform the main work - read the tabular data.
GoogleSheetsService.java
@Service public class GoogleSheetsService implements GoogleSheets { private Sheets sheetsService = null; @Override public List<List<Object>> readTable(GoogleConnection connection) throws IOException { Sheets service = getSheetsService(connection); return readTable(service, spreadsheetId, sheetName); } private Sheets getSheetsService(GoogleConnection gc) throws IOException { if (this.sheetsService == null) { this.sheetsService = new Sheets.Builder(Global.HTTP_TRANSPORT, Global.JSON_FACTORY, gc.getCredentials()) .setApplicationName(appName).build(); } return this.sheetsService; } } We distribute the entire sequence of operations among the three controllers. Controller for authorization.
GoogleAuthorizationController.java
@RestController public class GoogleAuthorizationController { @Autowired private GoogleConnectionService connection; @RequestMapping(value = "/ask", method = RequestMethod.GET) public void ask(HttpServletResponse response) throws IOException { // Step 1: Authorize --> ask for auth code String url = new GoogleAuthorizationCodeRequestUrl(connection.getClientSecrets(), connection.getRedirectUrl(), Global.SCOPES).setApprovalPrompt("force").build(); response.sendRedirect(url); } } The result of his work will be a redirect to google for login.
Then request the access of the google application to the user tables:
In case of successful authorization, the feedback controller will exchange the code for tokens and redirect to the original url. For the exchange itself is responsible class GoogleAuthorizationCodeTokenRequest .
GoogleCallbackController.java
@RestController public class GoogleCallbackController { @Autowired private GoogleConnectionService connection; @RequestMapping(value = "/oauth2callback", method = RequestMethod.GET) public void callback(@RequestParam("code") String code, HttpServletResponse response) throws IOException { // Step 2: Exchange code --> access tocken if (connection.exchangeCode(code)) { response.sendRedirect(connection.getSourceUrl()); } else { response.sendRedirect("/error"); } } } And, in fact, a working controller that implements the reading of tabular data.
GoogleSheetController.java
@RestController public class GoogleSheetController { @Autowired private GoogleConnection connection; @Autowired private GoogleSheets sheetsService; @RequestMapping(value = "/api/sheet", method = RequestMethod.GET) public ResponseEntity<List<List<Object>>> read(HttpServletResponse response) throws IOException { List<List<Object>> responseBody = sheetsService.readTable(connection); return new ResponseEntity<List<List<Object>>>(responseBody, HttpStatus.OK); } } We also need an interceptor so that it is impossible to access the working controller without authentication.
GoogleSheetsInterceptor.java
public class GoogleSheetsInterceptor implements HandlerInterceptor { // .. @Override public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object obj) throws Exception { if (connection.getCredentials() == null) { connection.setSourceUrl(request.getRequestURI()); response.sendRedirect("/ask"); return false; } return true; } } Compared to the API v3, each line is a list of objects.
private List<List<Object>> readTable(Sheets service, String spreadsheetId, String sheetName) throws IOException { ValueRange table = service.spreadsheets().values().get(spreadsheetId, sheetName).execute(); List<List<Object>> values = table.getValues(); return values; } Using A1 notation, we read page by page, not in blocks. To do this, add the page id parameter and the tab name in the application settings.
google.spreadsheet.id=.. google.spreadsheet.sheet.name=.. We start. We are checking.
As a result, you should get an idea of which Google API classes are used to connect to your OAuth 2.0 services and how you can use them from a web application.
Spring sso application
The application code responsible for working with OAuth2 can be simplified with spring. To do this, connect Spring Security OAuth .
<dependency> <groupId>org.springframework.security.oauth</groupId> <artifactId>spring-security-oauth2</artifactId> </dependency> This will allow us to hide the OAuth2 routine operations under the hood and protect our application.
Let's transfer user secrets in application.properties.
security.oauth2.client.client-id=Enter Client Id security.oauth2.client.client-secret=Enter Client Secret security.oauth2.client.accessTokenUri=https://accounts.google.com/o/oauth2/token security.oauth2.client.userAuthorizationUri=https://accounts.google.com/o/oauth2/auth security.oauth2.client.scope=openid,profile,https://www.googleapis.com/auth/spreadsheets security.oauth2.resource.user-info-uri=https://www.googleapis.com/oauth2/v3/userinfo Having connected security to the project, we have already activated basic authentication . Replace it with something more suitable.
Since we read the data from Google, even if it is engaged in user authentication for our application. To do this, add just one annotation @ EnableOAuth2Sso.
@EnableOAuth2Sso @SpringBootApplication public class Application { //.. } An SSO authentication point will be created and configured. No need to override the WebSecurityConfigurerAdapter. We just have to set a couple of parameters in the configuration.
security.ignored=/ security.basic.enabled=false security.oauth2.sso.login-path=/oauth2callback In this case, the login-path must match the URI for the redirect given in the google project. And the scope parameter must contain the profile value as well.
Additional controllers and the interceptor are no longer needed. Now their work will do spring.
Modify the GoogleConnection class. Credential's we will create using an authorization code stored after authentication in OAuth2 context. And we will take client data from the application config.
GoogleConnectionService.java
@Service public class GoogleConnectionService implements GoogleConnection { @Autowired private OAuth2ClientContext oAuth2ClientContext; private GoogleCredential googleCredentials = null; // .. @Override public Credential getCredentials() { if (googleCredentials == null) { googleCredentials = new GoogleCredential.Builder() .setTransport(Global.HTTP_TRANSPORT) .setJsonFactory(Global.JSON_FACTORY) .setClientSecrets(clientId, clientSecret) .build() .setAccessToken(response.getAccessToken()) .setFromTokenResponse(oAuth2ClientContext .getAccessToken().getValue()); } return googleCredentials; } } Displaying data in the browser, error handling, use of the session, logout and other things we leave without consideration. Their availability and configuration will depend on the specific requirements.
That's all. Working source is on github'e . Different approaches - in different branches.
Source: https://habr.com/ru/post/321444/
All Articles