Java_Alert Java_Alert - 2 months ago 14
Java Question

Date insertion in Google sheet appending ' in cell

To write the data into the Google spreadsheet I am using following code.

private static void writeValuesInSpreedSheet(Sheets service, String spreadsheetId, int sheetSize) throws IOException {
String range = "A"+(sheetSize+1)+":K"+(sheetSize+1);
List<List<Object>> newData = new ArrayList<>();
List<Object> rowValues = new ArrayList();
rowValues.add(getCurentDateInESTFormat());
rowValues.add("2");
rowValues.add("3");
rowValues.add("4");
rowValues.add("5");
rowValues.add("6");
rowValues.add("7");
rowValues.add("8");
rowValues.add("9");
rowValues.add("10");
rowValues.add("11");
/* List<Object> rowValues1 = new ArrayList();
rowValues1.add("1");
rowValues1.add("2");*/
newData.add(rowValues);
//newData.add(rowValues1);
// response.setValues(newData);


ValueRange oRange = new ValueRange();
oRange.setRange(range); // I NEED THE NUMBER OF THE LAST ROW
oRange.setValues(newData);
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();

System.out.println("Response Values " +oResp1.values());

}

private static Object getCurentDateInESTFormat() {
SimpleDateFormat sdfAmerica = new SimpleDateFormat("MM/dd/YYYY");
sdfAmerica.setTimeZone(TimeZone.getTimeZone("America/New_York"));
String sDateInAmerica = sdfAmerica.format(new Date());

return sDateInAmerica;
}


In sheet we have defined the date and currency type of respective column.

I am able to write the data but eventually its prepending
'
in the data for example -
'09/04/2016


Because of this we are not able to open it into date format. I have attached one screen shot as well.

enter image description here

We are using Google Sheets API V4.

I am asking this question because i did not find any link/solution related to it.

Answer

When using the ValueInputOption RAW you need to pass dates in the serial number format. The Apache POI library provides a getExcelDate method that will handle most of this for you, but you'll need to add a day to account for the difference in the epoch used.