Google api實戰與操作
- 一. Google API 權限配置
- 二. 操作API
- 2.1 引入依賴
- 2.2 導入代碼
Google官網
實現一套用java程序控制GoogleAPI實現自動生成監控日報等功能,具體能操作Gsheet及document
一. Google API 權限配置
打開上面官網,新建項目
啟用API
搜索sheet及document
.點擊試用后進入API界面 點擊創建憑據
創建OAuth 客戶端重定向記得跟下面配置一樣,因為需要先登錄才能授權
生成完成后點擊下載到本地,相當于你的Token
這時候就可以生成表格了
二. 操作API
2.1 引入依賴
<!-- google文檔--><dependency><groupId>com.google.api-client</groupId><artifactId>google-api-client</artifactId><version>1.31.2</version></dependency><dependency><groupId>com.google.apis</groupId><artifactId>google-api-services-sheets</artifactId><version>v4-rev614-1.18.0-rc</version></dependency><dependency><groupId>com.google.oauth-client</groupId><artifactId>google-oauth-client-jetty</artifactId><version>1.31.4</version></dependency><dependency><groupId>com.google.cloud</groupId><artifactId>google-cloud-storage</artifactId></dependency><!-- https://mvnrepository.com/artifact/com.google.apis/google-api-services-drive --><dependency><groupId>com.google.apis</groupId><artifactId>google-api-services-drive</artifactId><version>v3-rev197-1.25.0</version></dependency><dependency><groupId>com.google.apis</groupId><artifactId>google-api-services-docs</artifactId><version>v1-rev20220609-2.0.0</version></dependency><dependency><groupId>org.gitlab4j</groupId><artifactId>gitlab4j-api</artifactId><version>5.2.0</version></dependency><dependency><groupId>com.google.cloud</groupId><artifactId>libraries-bom</artifactId><version>25.4.0</version><type>pom</type><scope>import</scope></dependency>
將上面生成的Token導入項目
2.2 導入代碼
package com.shopee.bank.business.utility;import com.baomidou.mybatisplus.extension.api.R;
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.googleapis.json.GoogleJsonResponseException;
import com.google.api.client.http.javanet.NetHttpTransport;
import com.google.api.client.json.JsonFactory;
import com.google.api.client.json.gson.GsonFactory;
import com.google.api.client.util.store.FileDataStoreFactory;
import com.google.api.services.docs.v1.Docs;
import com.google.api.services.docs.v1.DocsScopes;
import com.google.api.services.docs.v1.model.*;
import com.google.api.services.drive.Drive;
import com.google.api.services.drive.DriveScopes;
import com.google.api.services.drive.model.File;
import com.google.api.services.sheets.v4.Sheets;
import com.google.api.services.sheets.v4.SheetsScopes;
import com.google.api.services.sheets.v4.model.Spreadsheet;
import com.google.api.services.sheets.v4.model.SpreadsheetProperties;
import com.google.api.services.sheets.v4.model.UpdateValuesResponse;
import com.google.api.services.sheets.v4.model.ValueRange;
import com.google.common.collect.Lists;
import io.swagger.models.auth.In;
import lombok.Builder;
import lombok.Data;import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.security.GeneralSecurityException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.stream.Collectors;/*** @author kaiyi.wang* @ClassName GoogleSheetUtil.java* @Description* @createTime 2022/07/05*/
public class GoogleUtil {private static final String APPLICATION_NAME = "Quickstart";private static final JsonFactory JSON_FACTORY = GsonFactory.getDefaultInstance();private static final String TOKENS_DIRECTORY_PATH = "tokens";private static final List<String> SCOPES_READ = Lists.newArrayList(SheetsScopes.SPREADSHEETS_READONLY);private static final List<String> SCOPES_CREATE = Lists.newArrayList(SheetsScopes.SPREADSHEETS,DriveScopes.DRIVE_FILE);private static final List<String> DOCS_SCOPES = Lists.newArrayList(DocsScopes.all());public static final String PHFoldID="1msqpxxxxx9vx82Cln";public static final String IDFoldID="1VyxuzHxxxxxxxHR";/*** TODO 下載的應用授權文件,這里記得換成自己的授權文件*/private static final String CREDENTIALS_FILE_PATH = "/credentials.json";private static Credential getCredentials(final NetHttpTransport HTTP_TRANSPORT,List<String> scopes) throws IOException {// Load client secrets.InputStream in = GoogleUtil.class.getResourceAsStream(CREDENTIALS_FILE_PATH);if (in == null) {throw new FileNotFoundException("Resource not found: " + CREDENTIALS_FILE_PATH);}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(new FileDataStoreFactory(new java.io.File(TOKENS_DIRECTORY_PATH))).setAccessType("offline").build();LocalServerReceiver receiver = new LocalServerReceiver.Builder().setPort(8888).build();return new AuthorizationCodeInstalledApp(flow, receiver).authorize("user");}public static void main(String[] args) throws IOException, GeneralSecurityException {
// createSpreadsheet("測試");
// }public static String createSpreadsheet(String title) throws IOException, GeneralSecurityException {final NetHttpTransport HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();// Create the sheets API clientSheets service = new Sheets.Builder(new NetHttpTransport(),GsonFactory.getDefaultInstance(),getCredentials(HTTP_TRANSPORT,SCOPES_CREATE)).setApplicationName("Sheets samples").build();// Create new spreadsheet with a titleSpreadsheet spreadsheet = new Spreadsheet().setProperties(new SpreadsheetProperties().setTitle(title));spreadsheet = service.spreadsheets().create(spreadsheet).setFields("spreadsheetId").execute();// Prints the new spreadsheet idSystem.out.println("Spreadsheet ID: " + spreadsheet.getSpreadsheetId());return spreadsheet.getSpreadsheetId();}public static void updateSheet(String sid,List<List<Object>> writeData,String dimension,String writeRange) throws IOException, GeneralSecurityException {final NetHttpTransport HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();// Create the sheets API clientSheets service = new Sheets.Builder(new NetHttpTransport(),GsonFactory.getDefaultInstance(),getCredentials(HTTP_TRANSPORT,SCOPES_CREATE)).setApplicationName("Sheets samples").build();writeSomething(writeData, service, sid,dimension,writeRange);}//COLUMNS / ROWSpublic static void writeSomething(List<List<Object>> myData, Sheets service, String sid,String dimension,String writeRange) {try {
// String writeRange = "工作表1!A:F";ValueRange vr = new ValueRange().setValues(myData).setMajorDimension(dimension);UpdateValuesResponse raw = service.spreadsheets().values().update(sid, writeRange, vr).setValueInputOption("RAW").execute();} catch (Exception e) {e.printStackTrace();}}@Data@Builderpublic static class VInfo{private String name;private String count;}/*** A1 符號* 一種語法,用于使用包含工作表名稱以及使用列字母和行號的開始和結束單元格坐標的字符串來定義單元格或單元格范圍。在引用絕對范圍的單元格時,此方法最常見且最有用。** 顯示示例* Sheet1!A1:B2指的是 Sheet1 前兩行中的前兩個單元格。* Sheet1!A:A指 Sheet1 第一列中的所有單元格。* Sheet1!1:2指 Sheet1 前兩行中的所有單元格。* Sheet1!A5:A指的是工作表 1 第一列的所有單元格,從第 5 行開始。* A1:B2指第一個可見工作表的前兩行中的前兩個單元格。* Sheet1指 Sheet1 中的所有單元格。* 'My Custom Sheet'!A:A指名為“我的自定義工作表”的工作表第一列中的所有單元格。帶有空格、特殊字符或字母數字組合的工作表名稱需要單引號。* 'My Custom Sheet'指“我的自定義工作表”中的所有單元格。* 提示:在可能的情況下,為電子表格中的對象使用不同的名稱。例如,A1 指的是第一個可見工作表中的單元格 A1,而“A1”指的是名為 A1 的工作表中的所有單元格。同樣,Sheet1 引用 Sheet1 中的所有單元格。但是,如果有一個名為“Sheet1”的命名范圍,則 Sheet1 指的是命名范圍,而“Sheet1”指的是工作表。* @throws GeneralSecurityException* @throws IOException*/// 讀取電子表格public static void readSheet() throws GeneralSecurityException, IOException {// Build a new authorized API client service.final NetHttpTransport HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();final String spreadsheetId = "1XiZZT2ctZ8JMjGy2GYOPmsat5CD24U9r0wo6vro-z9Q"; // 這個是官方的 spreadsheetId,讀取自己的Google Sheet換成對應ID即可final String range = "aml_process_tab!A1:F12"; // 讀取的表格范圍,命名規范: {sheet表名稱}!{開始單元格}:{結束單元格}Sheets service = new Sheets.Builder(HTTP_TRANSPORT, JSON_FACTORY, getCredentials(HTTP_TRANSPORT,SCOPES_READ)).setApplicationName(APPLICATION_NAME).build();ValueRange response = service.spreadsheets().values().get(spreadsheetId, range).execute();List<List<Object>> values = response.getValues();if (values == null || values.isEmpty()) {System.out.println("No data found.");} else {for (List row : values) {for (int i = 0; i < row.size(); i++) {System.out.print(row.get(i) + "\t\t");}System.out.println("");}}}public static List<String> moveFileToFolder(String fileId, String folderId)throws IOException, GeneralSecurityException {final NetHttpTransport HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();Drive service = new Drive.Builder(HTTP_TRANSPORT, JSON_FACTORY, getCredentials(HTTP_TRANSPORT,SCOPES_CREATE)).setApplicationName(APPLICATION_NAME).build();// Retrieve the existing parents to removeFile file = service.files().get(fileId).setFields("parents").execute();StringBuilder previousParents = new StringBuilder();for (String parent : file.getParents()) {previousParents.append(parent);previousParents.append(',');}try{// Move the file to the new folderfile = service.files().update(fileId, null).setAddParents(folderId).setRemoveParents(previousParents.toString()).setFields("id, parents").execute();return file.getParents();}catch (GoogleJsonResponseException e) {// TODO(developer) - handle error appropriatelySystem.err.println("Unable to move file: " + e.getDetails());throw e;}}/*** 復制文件到目標目錄下* @param documentId 目標docs* @param foldID 遷移目錄* @param copyName 復制文件后的名字* @throws GeneralSecurityException* @throws IOException*/public static String copyDocs(String documentId,String foldID,String copyName) throws GeneralSecurityException, IOException {final NetHttpTransport HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();
// Docs service = new Docs.Builder(HTTP_TRANSPORT, JSON_FACTORY, getCredentials(HTTP_TRANSPORT,DOCS_SCOPES))
// .setApplicationName(APPLICATION_NAME)
// .build();Drive service = new Drive.Builder(HTTP_TRANSPORT, JSON_FACTORY, getCredentials(HTTP_TRANSPORT,DOCS_SCOPES)).setApplicationName(APPLICATION_NAME).build();File copyMetadata = new File().setName(copyName);File documentCopyFile =service.files().copy(documentId, copyMetadata).execute();String documentCopyId = documentCopyFile.getId();moveFileToFolder(documentCopyId, foldID);return documentCopyId;}private static Docs getDocsService() {final NetHttpTransport HTTP_TRANSPORT;Docs service;try {HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();service = new Docs.Builder(HTTP_TRANSPORT, JSON_FACTORY, getCredentials(HTTP_TRANSPORT,DOCS_SCOPES)).setApplicationName(APPLICATION_NAME).build();} catch (GeneralSecurityException | IOException e) {throw new RuntimeException(e);}return service;}/*** 指定索引處插入文本* @param docsId* @param index 文字的索引 從0開始* @param text 插入文本* @throws IOException*/public static void updateDocs(String docsId, Integer index, String text) throws IOException {Docs service = getDocsService();List<Request> requests = new ArrayList<>();requests.add(new Request().setInsertText(new InsertTextRequest()
// .setText("07.18~07.24").setText(text)
// .setLocation(new Location().setIndex(2))));.setLocation(new Location().setIndex(index))));BatchUpdateDocumentRequest body = new BatchUpdateDocumentRequest().setRequests(requests);BatchUpdateDocumentResponse response = service.documents().batchUpdate(docsId, body).execute();System.out.println("updated: "+response.getDocumentId());}/*** 最末尾處插入空表格* @param docsId* @throws IOException*/public static void insertSheetInDocs(String docsId) throws IOException {Docs service = getDocsService();List<Request> requests = new ArrayList<>();requests.add(new Request().setInsertTable(new InsertTableRequest().setEndOfSegmentLocation(new EndOfSegmentLocation()).setRows(3).setColumns(3)));BatchUpdateDocumentRequest body =new BatchUpdateDocumentRequest().setRequests(requests);BatchUpdateDocumentResponse response =service.documents().batchUpdate(docsId, body).execute();}/*** docs中表格插入數據 必須倒著寫不然索引會變(代碼內已處理)* @param docsId 操作文檔* @param indexOfTable 文檔中第幾個表* @param data 行數據* @param row 寫入第幾行數據 注意第一可能為標題* @throws IOException 表格若有數據會報錯*/public static void updateDocsSheetRow(String docsId,Integer indexOfTable,List<Object> data,int row) throws IOException {Docs service = getDocsService();// 獲取結構Document document = service.documents().get(docsId).execute();List<StructuralElement> tables = document.getBody().getContent().stream().filter(e -> e.getTable() != null).collect(Collectors.toList());if(indexOfTable>=tables.size()){throw new IllegalArgumentException("out of size");}// 獲取tableStructuralElement table = tables.get(indexOfTable);// 拿到table行索引List<Integer> indexs = table.getTable().getTableRows().get(row).getTableCells().stream().map(TableCell::getStartIndex).collect(Collectors.toList());Collections.reverse(indexs);Collections.reverse(data);List<Request> requests = insertRowList(indexs, data);BatchUpdateDocumentRequest body =new BatchUpdateDocumentRequest().setRequests(requests);BatchUpdateDocumentResponse response = service.documents().batchUpdate(docsId, body).execute();}public static List<Request> insertRowList(List<Integer> reIndex,List<Object> rowData){int i=0;List<Request> rows=new ArrayList<>();for (Object e : rowData) {Request request = new Request().setInsertText(new InsertTextRequest().setText(String.valueOf(e)).setLocation(new Location().setIndex(reIndex.get(i++)+1)));rows.add(request);}
// Collections.reverse(rows);return rows;}}
注釋都有 湊活看看 下次詳細講解 拉取Grafana自動生成報表