CODEWITHSUNDEEP
javagoogle-sheets-api
conteh
conteh

Reputation: 1614

How to use Google Java Sheet API V4 to format cells as NUMBER and DATE

Greeting,

I'm trying to add formatting to my Google Sheets using the Java API V4.

The following code is my best understanding of what is required to format a series of columns and rows to be of type NUMBER with a single integer digit. Also there is one column with a DATE format. The code does not return any errors buts also does not alter the formatting on my sheet such that eash numeric values and date have a annoying apostrophe in front of each value like '5 and 'Oct 26 2016 13:34. I'm been trying to format the fields in order to remove that apostrophe which the sheet is adding automatically.

Any ideas what I'm doing wrong?

    public static void setProperties(String spreadsheetId, int length) throws IOException {


    List<Request> requests = new ArrayList<>();

    requests.add(new Request()
            .setRepeatCell(new RepeatCellRequest()
                    .setCell(new CellData()
                            .setUserEnteredFormat(new CellFormat().setNumberFormat(new NumberFormat().setPattern("0").setType("NUMBER"))))
                    .setRange(new GridRange()
                            .setSheetId(0)
                            .setStartRowIndex(3)
                            .setEndRowIndex(2 + length)
                            .setStartColumnIndex(3)
                            .setEndColumnIndex(22))
                    .setFields("userEnteredFormat.numberFormat")
            ));
    requests.add(new Request()
            .setRepeatCell(new RepeatCellRequest()
                    .setCell(new CellData()
                            .setUserEnteredFormat(new CellFormat().setNumberFormat(new NumberFormat().setPattern("0").setType("NUMBER"))))
                    .setRange(new GridRange()
                            .setSheetId(0)
                            .setStartRowIndex(3)
                            .setEndRowIndex(2 + length)
                            .setStartColumnIndex(24)
                            .setEndColumnIndex(24))
                    .setFields("userEnteredFormat.numberFormat")
            ));
    requests.add(new Request()
            .setRepeatCell(new RepeatCellRequest()
                    .setCell(new CellData()
                            .setUserEnteredFormat(new CellFormat().setNumberFormat(new NumberFormat().setPattern("mmm dd yyyy hh+:mm").setType("DATE"))))
                    .setRange(new GridRange()
                            .setSheetId(0)
                            .setStartRowIndex(3)
                            .setEndRowIndex(2 + length)
                            .setStartColumnIndex(23)
                            .setEndColumnIndex(23))
                    .setFields("userEnteredFormat.numberFormat")
            ));

    BatchUpdateSpreadsheetRequest batchUpdateRequest = new BatchUpdateSpreadsheetRequest()
            .setRequests(requests);
    SHEETS.spreadsheets().batchUpdate(spreadsheetId, batchUpdateRequest)
            .execute();
}




    <dependency>
        <groupId>com.google.apis</groupId>
        <artifactId>google-api-services-sheets</artifactId>
        <version>v4-rev21-1.22.0</version>
    </dependency>

Thanks, Conteh

Upvotes: 4

Views: 5451

Answers (1)

Nick Ko
Nick Ko

Reputation: 465

Check this question Google Sheet API V4(Java) append Date in cells, where a similar issue is reported.

Additionally, I've used Google Sheet API V3 and Google Sheet API V4 that issue is not present in v3.

Upvotes: 1

Related Questions