Zenokin Zenokin - 3 years ago 109
Android Question

How to handle my JSON string from Google Sheets API v4 in Java

I'm new to the Google Sheets API and it seems like I can't make any progress.

I have a Google Spreadsheet and I'm using the API with this URL to get the JSON data:


This will end up giving me something like this:

"range": "'01Jul2017'!A1:T2828",
"majorDimension": "ROWS",
"values": [
"Acid Arrow",
"Air Walk",

Now my problem is that I can't find how to handle my data. I'm used to reading JSON data with keys (e.g. "school":"conjuration") so I can use the following:


Can anyone help me out or just put me in the right direction?

Answer Source

You can convert the JSON string to a JSON tree and then iterate over all of the nodes and set your values. How to convert it to a tree depends on what Json library you are using. Below is an example using Jackson 2.

ObjectMapper mapper = new ObjectMapper();
JsonNode jsonTree = mapper.readTree(json);

JsonNode valuesArray = jsonTree.get("values");

// each array in values
for (JsonNode valuesItemArray : valuesArray) {
    // each sub-array
    for (JsonNode valuesItem : valuesItemArray) {
        // valuesItem is name, school, etc..

You can also use a custom deserializer for the values field so that you don't have to handle all of the JSON, but the deserializer will just do the same thing as above.


If you can assume that the first array with name, school, etc. is always first and is always in the right order, then you can do something like below.

ObjectMapper mapper = new ObjectMapper();
JsonNode jsonTree = mapper.readTree(json);

ArrayNode valuesArray = (ArrayNode) jsonTree.get("values");

List<Map<String, String>> spells = new ArrayList<Map<String, String>>();
Map<String, String> spellMap = null;

for (int i = 1; i < valuesArray.size(); i++) {
    ArrayNode valuesItemArray = (ArrayNode) valuesArray.get(i);

    spellMap = new HashMap<String, String>();
    spellMap.put("name", valuesItemArray.get(0).asText());
    spellMap.put("school", valuesItemArray.get(1).asText());
    spellMap.put("subschool", valuesItemArray.get(2).asText());
    spellMap.put("descriptor", valuesItemArray.get(3).asText());


Then you can iterate over the list and get the value by using .get("name") or .get("school") on each map.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download