Write data to Google Sheet using Google Sheet API

问题:

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.

回答1:

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;
}

}

回答2:

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.jackson2.JacksonFactory;
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 =
    JacksonFactory.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;
}

}