JSON response from SpreadSheet API-CodePudding

I have spread sheet mentioned below

Name    Marks
Adam    81
Mark    12
Carl    89

When I am using spreadsheet API as mentioned below,

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?

CodePudding user response：

Try

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

function myFunction() {
  let values = []
  let json = JSON.parse(response)
  let val1 = json.values[0][0]
  let val2 = json.values[0][1]
  json.values.forEach((v, i) => {
    if (i > 0) {
      let x = {}
      x[val1] = v[0]
      x[val2] = v[1]
      values.push(x)
    }
  })
  console.log(`"values" : `   JSON.stringify(values))
}

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

  let values = []
  let json = JSON.parse(response)
  let val1 = json.values[0][0]
  let val2 = json.values[0][1]
  json.values.forEach((v, i) => {
    if (i > 0) {
      let x = {}
      x[val1] = v[0]
      x[val2] = v[1]
      values.push(x)
    }
  })
  console.log(`"values" : `   JSON.stringify(values))

CodePudding user response：

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"
        ]
     ]
  }

  {
     "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]})}'

You can test this using https://jqplay.org/s/RSRyWj_ZLXO.

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'}]}.