Numberformat Google Spreadsheet API v4 Java

Question

I am reading a Google Spreadsheet like described in the Java Quickstart

https://developers.google.com/sheets/quickstart/java

The Quickstart explaines how to read data from a give range

.....
String range = "Class Data!A2:E";
ValueRange response = service.spreadsheets().values()
    .get(spreadsheetId, range)
    .execute();

List<List<Object>> values = response.getValues();

NumberFormat nf = NumberFormat.getInstance(Locale.GERMAN);

for (int i = 1; i < values.size(); i++) {
    List row = values.get(i);
    double l1 = nf.parse(row.get(1).toString()).doubleValue();
....

As you can see I am reading double values from the response

I expect a dedicated format for the double values (e.g. 12,34 instead of 12.34)

Can I already pass as a parameter to the request the number format I expect? Something like:

service.spreadsheets().values().get(spreadsheetId, range).myNumberFormat("##,#####").execute()

Regards

Michael

Sam Berlin Sam Berlin · Accepted Answer · 2016-12-05T17:24:11

The response you get from the spreadsheet is, by default, already formatted the way it is in the spreadsheet. Your call to nf.parse(..) is translating from that formatted version into a native java type, so you lose the formatting.

If you want the raw values directly (so you don't need to do parsing locally), you can set the valueRenderOption to UNFORMATTED_VALUE. See this sample for more information.

I'm not too sure exactly what question you're asking, since your code sample already gets a formatted version and you're explicitly dropping that formatting by parsing it.

If you'd like a specific kind of formatting that's not already in your spreadsheet, then retrieve the raw value (using the UNFORMATTED_VALUE valueRenderOption) and use your NumberFormat locally to translate to that format. Alternately, set your desired formatting in the spreadsheet and just retrieve the values and don't re-parse them.

Numberformat Google Spreadsheet API v4 Java

1 Answers