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

问题描述

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

任何人都可以提供示例java代码以使用Google Sheet API V4将数据写入Google表格中。 b

我有一个外观表单文档，但它并不清楚。

谢谢。

解决方案

要写入表格 ，您将需要电子表格ID，A1表示法中的范围以及您希望的数据写入在一个适当的请求主体对象中。

要将数据写入单个范围，请使用spreadsheets.value.update请求：

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

如果要编写多个不连续范围，可以使用spreadsheets.value.batc hUpdate请求：

  POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheet_id/values:batchUpdate 
 
 public class GoogleSheetsApiTest {
 
 //生成服务帐号和P12键：
 // https://developers.google.com/identity/protocols/OAuth2ServiceAccount 
 private final String CLIENT_ID =<您的服务帐户电子邮件地址>; 
 //添加请求的范围。 
 private final List< String> SCOPES = Arrays 
 .asList（https://spreadsheets.google.com/feeds）; 
 //获取服务帐户时创建的p12文件的名称
 private final String P12FILE =/<您的p12文件名称> .p12; 
 
 $ b @Test 
 public void testConnectToSpreadSheet（）throws GeneralSecurityException，
 IOException，ServiceException，URISyntaxException {
 
 SpreadsheetService service = new SpreadsheetService 
google-spreadsheet）; 
 GoogleCredential凭证= getCredentials（）; 
 service.setOAuth2Credentials（凭证）; 
 
 URL SPREADSHEET_FEED_URL =新网址（
https://spreadsheets.google.com/feeds/worksheets/1UXoGD2gowxZ2TY3gooI9y7rwWTPBOA0dnkeNYwUqQRA/private/basic）; 
 SpreadsheetFeed feed = service.getFeed（SPREADSHEET_FEED_URL，
 SpreadsheetFeed.class）; 
列表< SpreadsheetEntry> spreadsheets = feed.getEntries（）; 
 
 if（spreadsheets.size（）== 0）{
 // // TODO：没有电子表格，按照相应的步骤行事。 
} 
 // 
 SpreadsheetEntry spreadsheet = spreadsheets.get（0）; 
 System.out.println（spreadsheet.getTitle（）。getPlainText（））; 
 
 
 $ b私有GoogleCredential getCredentials（）抛出GeneralSecurityException，
 IOException，URISyntaxException {
 JacksonFactory JSON_FACTORY = JacksonFactory.getDefaultInstance（）; 
 HttpTransport httpTransport = GoogleNetHttpTransport 
 .newTrustedTransport（）; 
 
 URL fileUrl = this.getClass（）。getResource（P12FILE）; 
 GoogleCredential凭证=新GoogleCredential.Builder（）
 .setTransport（httpTransport）
 .setJsonFactory（JSON_FACTORY）
 .setServiceAccountId（CLIENT_ID）
 .setServiceAccountPrivateKeyFromP12File（
 new File（fileUrl.toURI（）））
 .setServiceAccountScopes（SCOPES）.build（）; 
 
返回凭证; 
} 
 
} 
  

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.

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.

Thank you.

解决方案

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.

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

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 - Java示例代码将数据写入Google表单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！