Friday, July 22, 2022

[SOLVED] JSON response from SpreadSheet API

Issue

I have spread sheet mentioned below

Name    Marks
Adam    81
Mark    12
Carl    89

When I am using spreadsheet API as mentioned below,

href="https://sheets.googleapis.com/v4/spreadsheets/spread_sheet_id/values/Sheet1?key=API_key" rel="nofollow noreferrer">https://sheets.googleapis.com/v4/spreadsheets/spread_sheet_id/values/Sheet1?key=API_key

I am getting a response as a nested list in values field,

{
"range": "Sheet1!A1:Z1000",
"majorDimension": "ROWS",
"values": [
    [
        "Name",
        "Marks"
    ],
    [
        "Adam",
        "81"
    ],
    [
        "Mark",
        "12"
    ],
    [
        "Carl",
        "89"
    ]
]

}

But what I do want is a proper json format in the values field,

"values": [ {"Name":"Adam", "Marks":81},
            {"Name":"Mark", "Marks":12},
            {"Name":"Carl", "Marks":89} ]

Is that can be done using Google Sheet API alone?


Solution

I believe your goal is as follows.

  • You want to achieve the following conversion.

    • From

        {
     "range":"Sheet1!A1:Z1000",
     "majorDimension":"ROWS",
     "values":[
        [
           "Name",
           "Marks"
        ],
        [
           "Adam",
           "81"
        ],
        [
           "Mark",
           "12"
        ],
        [
           "Carl",
           "89"
        ]
     ]
  }

    • To

        {
     "values":[
        {
           "Name":"Adam",
           "Marks":81
        },
        {
           "Name":"Mark",
           "Marks":12
        },
        {
           "Name":"Carl",
           "Marks":89
        }
     ]
  }

  • You want to achieve the above conversion using Sheets API.

  • When I asked the language that you are using, you say I use shell script. From this, you want to achieve this using a shell script.

Unfortunately, in the current stage, your expected values cannot be directly retrieved using Sheets API. It seems that this is the current specification of the Google side. In this case, it is required to convert the output values from Sheets API using a script.

When a shell script is used for achieving your goal, how about the following sample script?

Sample script:

In this script, jq is used. Ref

#!/bin/sh
data='{ "range": "Sheet1!A1:Z1000", "majorDimension": "ROWS", "values": [["Name", "Marks"], ["Adam", "81"], ["Mark", "12"], ["Carl", "89"]] }'
echo ${data} | jq '.values[0][0] as $h1 | .values[0][1] as $h2 | .values[1:] | {"values": map({($h1): .[0], ($h2): .[1]})}'

Note:

  • As additional information, when I saw your tags in your questions, python is included. So, when you want to achieve this using a python script, how about the following sample script?

      import json

  data = '{ "range": "Sheet1!A1:Z1000", "majorDimension": "ROWS", "values": [["Name", "Marks"], ["Adam", "81"], ["Mark", "12"], ["Carl", "89"]] }'
  obj = json.loads(data)
  values = obj["values"]
  (h1, h2) = values[0]
  res = {"values": [{(h1): v1, (h2): v2} for (v1, v2) in values[1:]]}
  print(res)
    • When this script is run, you can obtain the value of {'values': [{'Name': 'Adam', 'Marks': '81'}, {'Name': 'Mark', 'Marks': '12'}, {'Name': 'Carl', 'Marks': '89'}]}.


Answered By - Tanaike
Answer Checked By - Pedro (WPSolving Volunteer)
at