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

Question

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

Not sure if this helps but have you checked the Date and [Number Formats guide](https://developers.google.com/sheets/guides/formats) for Sheets API? — ReyAnthonyRenacia, Oct 27 '16 at 12:38
Yes,, I reviewed that document while developing the code. I believe the patterns are correct. — conteh, Oct 27 '16 at 14:14

score 1 · Accepted Answer · edited May 23 '17 at 11:48

1

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.

edited May 23 '17 at 11:48

Community

1
1

answered Nov 02 '16 at 16:03

Nick Ko

435
4
16

Thanks, Yeah the problem stemmed from my declarations of the types. I had declared String arrays for the arrays of numeric data because I wasn't doing a calculation on that data. There were just rankings between 1-5. When I changed the type from a String array to an Integer array then the apostrophe went away. Similarly the date I had converted into a formatted string in the code prior to the addition or code to set the pattern. If I just load up the date type, then the apostrophe is removed. – conteh Nov 04 '16 at 14:22
I was getting similar issues with Date types, when I was using the v4, but for the project purposes I had to roll back to v3, which coincidentally got rid of the apostrophe issue I was having. I'll keep that in mind if I have to use the library again. – Nick Ko Nov 04 '16 at 16:05

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

1 Answers1