CODEWITHSUNDEEP
javagoogle-sheetsgoogle-sheets-api
Michael Meyer
Michael Meyer

Reputation: 2184

Numberformat Google Spreadsheet API v4 Java

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

Upvotes: 0

Views: 706

Answers (1)

Sam Berlin
Sam Berlin

Reputation: 3773

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.

Upvotes: 1

Related Questions