java操作google sheet，service acount验证方式

google sheet 读写数据

一、导入依赖

<dependency>
            <groupId>com.google.oauth-client</groupId>
            <artifactId>google-oauth-client-jetty</artifactId>
            <version>1.34.1</version>
        </dependency>
        <dependency>
            <groupId>com.google.api-client</groupId>
            <artifactId>google-api-client</artifactId>
            <version>2.0.0</version>
        </dependency>
        <dependency>
            <groupId>com.google.apis</groupId>
            <artifactId>google-api-services-sheets</artifactId>
            <version>v4-rev20220927-2.0.0</version>
        </dependency>
        <dependency>
            <groupId>com.google.http-client</groupId>
            <artifactId>google-http-client-jackson2</artifactId>
            <version>1.31.0</version>
        </dependency>

二、
1、利用/credentials.json（由service account生成）转换为内部Credential

public class GoogleSheetUtil {

    /**
     * Global instance of the scopes required by this quickstart.
     * If modifying these scopes, delete your previously saved tokens/ folder.
     */
    private static final List<String> SCOPES = Collections.singletonList(SheetsScopes.SPREADSHEETS);
    private static final String CREDENTIALS_FILE_PATH = "/credentials.json";
    private static final JsonFactory JSON_FACTORY = JacksonFactory.getDefaultInstance();

    /**
     * Creates an authorized Credential object.
     *
     * @param HTTP_TRANSPORT The network HTTP Transport.
     * @return An authorized Credential object.
     * @throws IOException If the credentials.json file cannot be found.
     */
    public static Credential getCredentials(final HttpTransport HTTP_TRANSPORT) throws IOException {
        // Load client secrets.
        InputStream in = GoogleSheetUtil.class.getResourceAsStream(CREDENTIALS_FILE_PATH);
        GoogleCredential credential = GoogleCredential.fromStream(in, HTTP_TRANSPORT, JSON_FACTORY)
                .createScoped(SCOPES);
        return credential;
    }
}

2、操作sheet基本方法

range参数参考文档：https://developers.google.com/sheets/api/guides/concepts#cell

import com.google.api.client.googleapis.javanet.GoogleNetHttpTransport;
import com.google.api.client.http.javanet.NetHttpTransport;
import com.google.api.client.json.JsonFactory;
import com.google.api.client.json.jackson2.JacksonFactory;
import com.google.api.services.sheets.v4.Sheets;
import com.google.api.services.sheets.v4.model.*;
import com.google.common.annotations.VisibleForTesting;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import javax.annotation.PostConstruct;
import java.io.IOException;
import java.security.GeneralSecurityException;
import java.util.Collections;
import java.util.List;


@Service
public class GoogleSheetRequester  {
    private static final String APPLICATION_NAME = "google sheets application";
    private static final JsonFactory JSON_FACTORY = JacksonFactory.getDefaultInstance();
    private static final String rangeAll = "!A:Z";
    private static final String rangeRowTemplate = "!A${rows}:Z";
    private Sheets service;
    protected final Logger logger = LoggerFactory.getLogger(this.getClass());


    @PostConstruct
    public void init() throws GeneralSecurityException, IOException {
        this.service = getSheets();
    }


    private static Sheets getSheets() throws GeneralSecurityException, IOException {
        final NetHttpTransport HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();
        return new Sheets.Builder(HTTP_TRANSPORT, JSON_FACTORY, GoogleSheetUtil.getCredentials(HTTP_TRANSPORT))
                .setApplicationName(APPLICATION_NAME)
                .build();
    }

    private void update(String spreadsheetId, String range, List<List<Object>> values) {
        ValueRange valueRange = new ValueRange();
        valueRange.setValues(values);

        try {
            service.spreadsheets().values()
                    .update(spreadsheetId, range, valueRange)
                    .setValueInputOption(ValueInputOption.RAW.toString())
                    .execute();
        } catch (IOException e) {
            logger.error("update google sheet failed ,{}", e.getMessage());
        }
    }

    private void append(String spreadsheetId, String range, List<List<Object>> values) {
        ValueRange valueRange = new ValueRange();
        valueRange.setValues(values);

        try {
            service.spreadsheets().values()
                    .append(spreadsheetId, range, valueRange)
                    .setValueInputOption(ValueInputOption.RAW.toString())
                    .execute();
        } catch (IOException e) {
            logger.error("append google sheet failed,{}", e.getMessage());
        }
    }
    
    // 通过spreadsheetId获取数据 
    private List<List<Object>> get(String spreadsheetId, String range, String sheetName) {
        ValueRange response;
        try {
            response = service.spreadsheets().values()
                    .get(spreadsheetId, range)
                    .execute();
        } catch (IOException e) {
            logger.info("新建sheet[{}]", sheetName);
            createSheet(sheetName, spreadsheetId);
            return null;
        }
        if (response == null) {
            logger.error("get range :{} do not exist", range);
            return null;
        }
        return response.getValues();
    }

    // 创建sheet
    @VisibleForTesting
    public void createSheet(String sheetName, String spreadSheetId) {
        // Define the new sheet's properties
        SheetProperties properties = new SheetProperties();
        properties.setTitle(sheetName);

        // Define the request to create the new sheet
        AddSheetRequest addSheetRequest = new AddSheetRequest();
        addSheetRequest.setProperties(properties);

        // Define the overall request to batch update the spreadsheet
        BatchUpdateSpreadsheetRequest batchUpdateRequest = new BatchUpdateSpreadsheetRequest();
        batchUpdateRequest.setRequests(Collections.singletonList(new Request().setAddSheet(addSheetRequest)));

        // Execute the batch update request to create the new sheet
        try {
            service.spreadsheets().batchUpdate(spreadSheetId, batchUpdateRequest).execute();
        } catch (IOException e) {
            logger.error("create sheet failed,{}", e.getMessage());
        }
    }

}

3、用到枚举

public enum ValueInputOption {
    //表示输入的值是原始文本，不会被解析或格式化
    RAW,
    //表示输入的值会被解析和格式化，例如公式和日期格式
    USER_ENTERED,
    //表示输入的值选项未指定，将使用默认选项
    INPUT_VALUE_OPTION_UNSPECIFIED
}