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.
Is there sample Java code to write data to Google Sheet using Google Sheet API V4?
I had a look at the sheet documentation but it was not clear.
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;
}
}
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;
}
}