使用 Google Sheet API V4 将数据写入 Google Sheet - Java 示例代码

Write data to Google Sheet using Google Sheet API V4 - Java Sample Code

提问by Ashwiinn Karaangutkar

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.

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

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

任何人都可以提供示例 Java 代码以使用 Google Sheet API V4 将数据写入 Google Sheet？

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

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

Thank you.

谢谢你。

回答by Android Enthusiast

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

要 写入工作表，您需要电子表格 ID、A1 表示法中的范围以及您希望写入的数据，这些数据 排列在适当的请求正文对象中。

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

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

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:

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

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 {
    HymansonFactory JSON_FACTORY = HymansonFactory.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;
}

}

回答by Ashwiinn Karaangutkar

Please find answer for above question:

请找到上述问题的答案：

package com.googledoc;
import com.google.api.client.auth.oauth2.Credential;
importcom.google.api.client.extensions.java6.auth.oauth2.AuthorizationCodeInstalledApp;
import com.google.api.client.extensions.jetty.auth.oauth2.LocalServerReceiver;
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.http.HttpTransport;
import com.google.api.client.json.Hymanson2.HymansonFactory;
import com.google.api.client.json.JsonFactory;
import com.google.api.client.util.store.FileDataStoreFactory;
import com.google.api.services.sheets.v4.SheetsScopes;
import com.google.api.services.sheets.v4.model.*;


import com.google.api.services.sheets.v4.Sheets;
import com.google.api.services.sheets.v4.Sheets.Spreadsheets;

import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import org.mortbay.log.Log;

public class SheetsQuickstart {
/** Application name. */
private static final String APPLICATION_NAME =
    "Google Sheets API Java Quickstart";

/** Directory to store user credentials for this application. */
private static final java.io.File DATA_STORE_DIR = new java.io.File(
    System.getProperty("user.home"), ".credentials/sheets.googleapis.com-java-quickstart.json");

/** Global instance of the {@link FileDataStoreFactory}. */
private static FileDataStoreFactory DATA_STORE_FACTORY;

/** Global instance of the JSON factory. */
private static final JsonFactory JSON_FACTORY =
    HymansonFactory.getDefaultInstance();

/** Global instance of the HTTP transport. */
private static HttpTransport HTTP_TRANSPORT;

/** Global instance of the scopes required by this quickstart.
 *
 * If modifying these scopes, delete your previously saved credentials
 * at ~/.credentials/sheets.googleapis.com-java-quickstart.json
 */
private static final List<String> SCOPES =
    Arrays.asList(SheetsScopes.SPREADSHEETS);

public static ValueRange response;
public static UpdateValuesResponse request;

public static void main (String[] args) throws Exception {

  //List<List<Object>> values = SheetsQuickstart.getResponse("BrowserSheet","A1","A").getValues ();
  SheetsQuickstart.setValue("BrowserSheet","A1","A");
    }

static {
    try {
        HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();
        DATA_STORE_FACTORY = new FileDataStoreFactory(DATA_STORE_DIR);
    } catch (Throwable t) {
        t.printStackTrace();
        System.exit(1);
    }
    }


/**
 * Creates an authorized Credential object.
 * @return an authorized Credential object.
 * @throws IOException
 */
public static Credential authorize() throws IOException {
    // Load client secrets.
    InputStream in =
        SheetsQuickstart.class.getResourceAsStream("/resources/client_secret.json");
    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(DATA_STORE_FACTORY)
            .setAccessType("offline")
            .build();
    Credential credential = new AuthorizationCodeInstalledApp(
        flow, new LocalServerReceiver()).authorize("user");
    System.out.println(
            "Credentials saved to " + DATA_STORE_DIR.getAbsolutePath());
    return credential;
}

/**
 * Build and return an authorized Sheets API client service.
 * @return an authorized Sheets API client service
 * @throws IOException
 */
public static Sheets getSheetsService() throws IOException {
    Credential credential = authorize();
    return new Sheets.Builder(HTTP_TRANSPORT, JSON_FACTORY, credential)
            .setApplicationName(APPLICATION_NAME)
            .build();
}

public static ValueRange getResponse(String SheetName,String RowStart, String RowEnd) throws IOException{
    // Build a new authorized API client service.
    Sheets service = getSheetsService();


    // Prints the names and majors of students in a sample spreadsheet:
    String spreadsheetId = "1234";
    String range = SheetName+"!"+RowStart+":"+RowEnd;
    response = service.spreadsheets().values()
        .get(spreadsheetId, range).execute ();

    return response;

}


public static void setValue(String SheetName,String RowStart, String RowEnd) throws IOException{
  // Build a new authorized API client service.
  Sheets service = getSheetsService();
  // Prints the names and majors of students in a sample spreadsheet:
  String spreadsheetId = "1234";
  String range = RowStart+":"+RowEnd;

  List<List<Object>> arrData = getData();

  ValueRange oRange = new ValueRange();
  oRange.setRange(range); // I NEED THE NUMBER OF THE LAST ROW
  oRange.setValues(arrData);

  List<ValueRange> oList = new ArrayList<>();
  oList.add(oRange);

  BatchUpdateValuesRequest oRequest = new BatchUpdateValuesRequest();
  oRequest.setValueInputOption("RAW");
  oRequest.setData(oList);

  BatchUpdateValuesResponse oResp1 = service.spreadsheets().values().batchUpdate(spreadsheetId, oRequest).execute();

 // service.spreadsheets().values().update (spreadsheetId, range,) ;     
  //return request;

  }

public static List<List<Object>> getData ()  {

  List<Object> data1 = new ArrayList<Object>();
  data1.add ("Ashwin");

  List<List<Object>> data = new ArrayList<List<Object>>();
  data.add (data1);

  return data;
}

}