使用 Google Sheet API V4 将数据写入 Google Sheet - Java 示例代码 [英] Write data to Google Sheet using Google Sheet API V4 - Java Sample Code

问题描述

我开发了一个测试自动化框架，该框架当前在 Excel 表中为测试用例编写通过或失败值.我们已决定迁移到 Google 表格.

I have a developed a test automation framework that writes pass or fail values for test cases in excel sheet currently. We have decided to migrate to Google Sheets.

谁能提供一个示例 java 代码来使用 Google Sheet API V4 将数据写入 Google Sheet?

Can anyone provide a sample java code to Write data to Google Sheet using Google Sheet API V4?

我有一个看表文档，但不清楚.

I had a look sheet documentation but it was not clear though.

谢谢.

推荐答案

写入工作表，您将需要电子表格 ID、A1 表示法的范围以及您希望 写入 安排在适当的请求正文对象中.

To write to a sheet, you will need the spreadsheet ID, the range(s) in A1 notation, and the data you wish to write arranged in an appropriate request body object.

要将数据写入单个范围，请使用电子表格.value.update 请求:

To write data to a single range, use a spreadsheets.value.update request:

PUT https://sheets.googleapis.com/v4/spreadsheets/spreadsheet_id/values/range?valueInputOption=valueInputOption

如果你想写多个不连续的范围，你可以使用一个电子表格.value.batchUpdate请求:

If you want to write multiple discontinuous ranges, you can use a spreadsheets.value.batchUpdate request:

POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheet_id/values:batchUpdate

public class GoogleSheetsApiTest {

// Generate a service account and P12 key:
// https://developers.google.com/identity/protocols/OAuth2ServiceAccount
private final String CLIENT_ID = "<your service account email address>";
// Add requested scopes.
private final List<String> SCOPES = Arrays
        .asList("https://spreadsheets.google.com/feeds");
// The name of the p12 file you created when obtaining the service account
private final String P12FILE = "/<your p12 file name>.p12";


@Test
public void testConnectToSpreadSheet() throws GeneralSecurityException,
        IOException, ServiceException, URISyntaxException {

    SpreadsheetService service = new SpreadsheetService(
            "google-spreadsheet");
    GoogleCredential credential = getCredentials();
    service.setOAuth2Credentials(credential);

    URL SPREADSHEET_FEED_URL = new URL(
            "https://spreadsheets.google.com/feeds/worksheets/1UXoGD2gowxZ2TY3gooI9y7rwWTPBOA0dnkeNYwUqQRA/private/basic");
    SpreadsheetFeed feed = service.getFeed(SPREADSHEET_FEED_URL,
            SpreadsheetFeed.class);
    List<SpreadsheetEntry> spreadsheets = feed.getEntries();

    if (spreadsheets.size() == 0) {
        // // TODO: There were no spreadsheets, act accordingly.
    }
    //
    SpreadsheetEntry spreadsheet = spreadsheets.get(0);
    System.out.println(spreadsheet.getTitle().getPlainText());

}

private GoogleCredential getCredentials() throws GeneralSecurityException,
        IOException, URISyntaxException {
    JacksonFactory JSON_FACTORY = JacksonFactory.getDefaultInstance();
    HttpTransport httpTransport = GoogleNetHttpTransport
            .newTrustedTransport();

    URL fileUrl = this.getClass().getResource(P12FILE);
    GoogleCredential credential = new GoogleCredential.Builder()
            .setTransport(httpTransport)
            .setJsonFactory(JSON_FACTORY)
            .setServiceAccountId(CLIENT_ID)
            .setServiceAccountPrivateKeyFromP12File(
                    new File(fileUrl.toURI()))
            .setServiceAccountScopes(SCOPES).build();

    return credential;
}

}

这篇关于使用 Google Sheet API V4 将数据写入 Google Sheet - Java 示例代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！