CODEWITHSUNDEEP
javagoogle-sheetsgoogle-sheets-api
Java_Alert
Java_Alert

Reputation: 1179

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.

Upvotes: 0

Views: 985

Answers (1)

Eric Koleda
Eric Koleda

Reputation: 12673

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.

Upvotes: 2

Related Questions