CODEWITHSUNDEEP
jsongoogle-sheetsgoogle-sheets-api
Shihab365
Shihab365

Reputation: 139

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

Upvotes: 0

Views: 3394

Answers (3)

Mike Steelson
Mike Steelson

Reputation: 15328

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

Upvotes: 1

AdityaDees
AdityaDees

Reputation: 1047

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

  1. 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.
  2. copy this range range
  3. copy your sheet id 1vW-N2WRchAmxd8-isEx4oPpJourPsIIavqfvT_Nbtzw
  4. And then combine all https://opensheet.vercel.app/1vW-N2WRchAmxd8-isEx4oPpJourPsIIavqfvT_Nbtzw/Master!A1:Z14989
  5. the results will be like this link enter image description here

Upvotes: 4

Fatema Tus Zohura
Fatema Tus Zohura

Reputation: 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!

Upvotes: 0

Related Questions