'How to retrieve google sheet api v4 data into JSON format

I am able to parse data from google sheet api v4. Here is retrieve sheet data.

Sample:

https://sheets.googleapis.com/v4/spreadsheets/1vW-N2WRchAmxd8-isEx4oPpJourPsIIavqfvT_Nbtzw/values/Master?key=AIzaSyAwaXGDfOOdUX-YaGIn0S8Q7ae-ONC_cLs

But i want to retrive these data in below json format

Sample JSON format that i want to achieve:

{"values":[

{"Timestamp":"2021-03-09", "Student ID-Name":"", "Umail":"[email protected]", "Geoloc-Latitude":"", "Geoloc-Longitude":"", "Subject-Code":"PHY4101"},

{"Timestamp":"2021-03-09", "Student ID-Name":"", "Umail":"[email protected]", "Geoloc-Latitude":"", "Geoloc-Longitude":"", "Subject-Code":"MATH4103"},

{"Timestamp":"2021-04-13", "Student ID-Name":"", "Umail":"[email protected]", "Geoloc-Latitude":"", "Geoloc-Longitude":"", "Subject-Code":"HUM4105"}

]}

If anyone solve this, it's huge help for me...TIA

Solution 1:^[1]

Step 1: Fire up Google Spreadsheet and enter your data. Generate a shareable link and switch to "Anyone with the link on the internet can View." Copy the link to your clipboard. Step 3: Type [sheet.best] on the browser and click on the +Connect button. Step 4: On the Connection URL section, paste the Spreadsheet URL.

#JSON #Google Sheet #API

Specify the origin into Google Spreadsheet. Step 5: Tap on the Connect option, and there you go! A brand new REST API in the Connection URL. Step 6: Copy the API or Connection URL into your clipboard. Step 7: Paste it on the browser, and experience the spreadsheet data in JSON format!

Solution 2:^[2]

you can use this https://opensheet.vercel.app/id/range

From this https://sheets.googleapis.com/v4/spreadsheets/1vW-N2WRchAmxd8-isEx4oPpJourPsIIavqfvT_Nbtzw/values/Master?key=AIzaSyAwaXGDfOOdUX-YaGIn0S8Q7ae-ONC_cLs you can get the ID and range.
copy this range
copy your sheet id 1vW-N2WRchAmxd8-isEx4oPpJourPsIIavqfvT_Nbtzw
And then combine all https://opensheet.vercel.app/1vW-N2WRchAmxd8-isEx4oPpJourPsIIavqfvT_Nbtzw/Master!A1:Z14989
the results will be like this link

Solution 3:^[3]

Try

function table2json() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Master');
  var [headers, ...rows] = sheet.getDataRange().getValues();
  var data = {}
  var items = []
  rows.forEach(function(r) {
    var obj={}
    r.forEach(function (c, j) {
      obj[headers[j]] = c
    })
    items.push(obj)
  })
  data['values'] = items
  Logger.log(JSON.stringify(data))
}

you will get a json that represents your datarange

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1
Solution 2	AdityaDees
Solution 3	Mike Steelson

'How to retrieve google sheet api v4 data into JSON format

Sample JSON format that i want to achieve:

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]

Solution 3:^[3]