网站建设管理人员工作职责,做网站怎么接业务,推广型网站建设网址,江苏工业互联网公司排名Google api实战与操作 一. Google API 权限配置二. 操作API2.1 引入依赖2.2 导入代码 Google官网
实现一套用java程序控制GoogleAPI实现自动生成监控日报等功能,具体能操作Gsheet及document
一. Google API 权限配置
打开上面官网,新建项目 启用API 搜索sheet及document … Google api实战与操作 一. Google API 权限配置二. 操作API2.1 引入依赖2.2 导入代码 Google官网
实现一套用java程序控制GoogleAPI实现自动生成监控日报等功能,具体能操作Gsheet及document
一. Google API 权限配置
打开上面官网,新建项目 启用API 搜索sheet及document
.点击试用后进入API界面 点击创建凭据 创建OAuth 客户端重定向记得跟下面配置一样,因为需要先登录才能授权 生成完成后点击下载到本地,相当于你的Token
这时候就可以生成表格了
二. 操作API
2.1 引入依赖
!-- google文档--dependencygroupIdcom.google.api-client/groupIdartifactIdgoogle-api-client/artifactIdversion1.31.2/version/dependencydependencygroupIdcom.google.apis/groupIdartifactIdgoogle-api-services-sheets/artifactIdversionv4-rev614-1.18.0-rc/version/dependencydependencygroupIdcom.google.oauth-client/groupIdartifactIdgoogle-oauth-client-jetty/artifactIdversion1.31.4/version/dependencydependencygroupIdcom.google.cloud/groupIdartifactIdgoogle-cloud-storage/artifactId/dependency!-- https://mvnrepository.com/artifact/com.google.apis/google-api-services-drive --dependencygroupIdcom.google.apis/groupIdartifactIdgoogle-api-services-drive/artifactIdversionv3-rev197-1.25.0/version/dependencydependencygroupIdcom.google.apis/groupIdartifactIdgoogle-api-services-docs/artifactIdversionv1-rev20220609-2.0.0/version/dependencydependencygroupIdorg.gitlab4j/groupIdartifactIdgitlab4j-api/artifactIdversion5.2.0/version/dependencydependencygroupIdcom.google.cloud/groupIdartifactIdlibraries-bom/artifactIdversion25.4.0/versiontypepom/typescopeimport/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 ListString SCOPES_READ Lists.newArrayList(SheetsScopes.SPREADSHEETS_READONLY);private static final ListString SCOPES_CREATE Lists.newArrayList(SheetsScopes.SPREADSHEETS,DriveScopes.DRIVE_FILE);private static final ListString DOCS_SCOPES Lists.newArrayList(DocsScopes.all());public static final String PHFoldID1msqpxxxxx9vx82Cln;public static final String IDFoldID1VyxuzHxxxxxxxHR;/*** TODO 下载的应用授权文件这里记得换成自己的授权文件*/private static final String CREDENTIALS_FILE_PATH /credentials.json;private static Credential getCredentials(final NetHttpTransport HTTP_TRANSPORT,ListString 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,ListListObject 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(ListListObject 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();}}DataBuilderpublic 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();ListListObject 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 ListString 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();ListRequest requests new ArrayList();requests.add(new Request().setInsertText(new InsertTextRequest()
// .setText(07.1807.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();ListRequest 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,ListObject data,int row) throws IOException {Docs service getDocsService();// 获取结构Document document service.documents().get(docsId).execute();ListStructuralElement tables document.getBody().getContent().stream().filter(e - e.getTable() ! null).collect(Collectors.toList());if(indexOfTabletables.size()){throw new IllegalArgumentException(out of size);}// 获取tableStructuralElement table tables.get(indexOfTable);// 拿到table行索引ListInteger indexs table.getTable().getTableRows().get(row).getTableCells().stream().map(TableCell::getStartIndex).collect(Collectors.toList());Collections.reverse(indexs);Collections.reverse(data);ListRequest requests insertRowList(indexs, data);BatchUpdateDocumentRequest body new BatchUpdateDocumentRequest().setRequests(requests);BatchUpdateDocumentResponse response service.documents().batchUpdate(docsId, body).execute();}public static ListRequest insertRowList(ListInteger reIndex,ListObject rowData){int i0;ListRequest rowsnew 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自动生成报表
