How to convert Google Sheets v4 api output to a normal JavaScript Array of Objects?

Question

I tried to find answers for this but everything seems to pertain to other things or the previous versions of the api.

I'm trying to use a google spreadsheet as a basic json backend. My spreadsheets looks something like this:

Google sheets api v4 has a method called batchGet. The call (https://sheets.googleapis.com/v4/spreadsheets/1kEhCvvbHt-9BZgKKp-eQsy7l0AkFtZ9_7cIhvyVGLZ8/values:batchGet?ranges=data&majorDimension=ROWS) outputs my spreadsheet to be something like this:

{
"spreadsheetId": "1kEhCvvbHt-9BZgKKp-eQsy7l0AkFtZ9_7cIhvyVGLZ8",
"valueRanges": [
{
"range": "data!A1:O1000",
"majorDimension": "ROWS",
"values": [
[
"id",
"name",
"location",
"Description",
"website",
"image",
"Address",
"lat",
"lng",
"color",
"Products",
"Cartridges",
"High-CBD Tincture",
"THCa Crystalline"
],
[
"1",
"Erba",
"West Los Angeles",
"Erba’s philosophy is to carry only the most top-tier products, and never sacrifice quality by all means necessary. You’ll feel the difference once you step foot inside their impressive shop.",
"http://erbacollective.com/",
"https://static.wixstatic.com/media/e4d816_49fd88e6e519486b8be81fd955a52b35~mv2.png/v1/fill/w_173,h_173,al_c,usm_0.66_1.00_0.01/e4d816_49fd88e6e519486b8be81fd955a52b35~mv2.png",
"12320 W Pico Blvd, Los Angeles, CA 90064\n",
"34.028126",
"-118.452295",
"#7CB711",
"Full-spectrum vape carts, THCa crystalline and tincture.",
"TRUE",
"FALSE",
"TRUE"
],
[
"2",
"99 High Tide",
"Malibu",
"99 High Tide is Malibu's only medical marijuana dispensary. They are a visionary collective of Cannabis experts, artists, DJs, growers, healers & shamans.",
"http://99hightide.com/",
"https://static.wixstatic.com/media/e4d816_a576375fe1104c1399cc0249b7c3818e~mv2.png/v1/fill/w_173,h_173,al_c,usm_0.66_1.00_0.01/e4d816_a576375fe1104c1399cc0249b7c3818e~mv2.png",
"22775 Pacific Coast Hwy, Malibu, CA 90265\n",
"34.039466",
"-118.671939",
"#3A8ABD",
"Full-spectrum vape carts and THCa crystalline.",
"TRUE",
"FALSE",
"TRUE"
],
[

What I need is something like this (in order for it to play nicely in React):

"rows": [
{
"id": 1,
"name": "Erba",
"location": "West Los Angeles",
"description": "Erba’s philosophy is to carry only the most top-tier products, and never sacrifice quality by all means necessary. You’ll feel the difference once you step foot inside their impressive shop.",
"website": "http://erbacollective.com/",
"image": "https://static.wixstatic.com/media/e4d816_49fd88e6e519486b8be81fd955a52b35~mv2.png/v1/fill/w_173,h_173,al_c,usm_0.66_1.00_0.01/e4d816_49fd88e6e519486b8be81fd955a52b35~mv2.png",
"address": "12320 W Pico Blvd, Los Angeles, CA 90064\n",
"lat": 34.028126,
"lng": -118.452295,
"color": "#7CB711",
"products": "Full-spectrum vape carts, THCa crystalline and tincture.",
"cartridges": "TRUE",
"high-cbdtincture": "FALSE",
"thcacrystalline": "TRUE"
},
{
"id": 2,
"name": "99 High Tide",
"location": "Malibu",
"description": "99 High Tide is Malibu's only medical marijuana dispensary. They are a visionary collective of Cannabis experts, artists, DJs, growers, healers & shamans.",
"website": "http://99hightide.com/",
"image": "https://static.wixstatic.com/media/e4d816_a576375fe1104c1399cc0249b7c3818e~mv2.png/v1/fill/w_173,h_173,al_c,usm_0.66_1.00_0.01/e4d816_a576375fe1104c1399cc0249b7c3818e~mv2.png",
"address": "22775 Pacific Coast Hwy, Malibu, CA 90265\n",
"lat": 34.039466,
"lng": -118.671939,
"color": "#3A8ABD",
"products": "Full-spectrum vape carts and THCa crystalline.",
"cartridges": "TRUE",
"high-cbdtincture": "FALSE",
"thcacrystalline": "TRUE"
},
{
"id": 3,
"name": "The Higher Path",
"location": "Sherman Oaks",
"description": "Named the Best Dispensary in Los Angeles 2016, The Higher Path Collective serves both medical patients and adult use recreational consumers! Hailing from Northern California, The Higher Path’s owner Jerred Kiloh brings a very different mentality of medicine with him to Southern California—one that puts the patient before everything else. Since opening in 2013, it’s been The Higher Path’s focus and passion to help provide patients with the knowledge and medicine they need.",
"website": "https://www.thehigherpath.com/",
"image": "https://static.wixstatic.com/media/e4d816_8304a48460884d9ea5cac420164fe9cb~mv2.png/v1/fill/w_173,h_173,al_c,usm_0.66_1.00_0.01/e4d816_8304a48460884d9ea5cac420164fe9cb~mv2.png",
"address": "14080 Ventura Blvd, Sherman Oaks, CA 91423\n",
"lat": 34.149202,
"lng": -118.439813,
"color": "#9050A4",
"products": "Full-spectrum vape carts.",
"cartridges": "TRUE",
"high-cbdtincture": "FALSE",
"thcacrystalline": "FALSE"
},
{
"id": 4,
"name": "Sespe Creek",
"location": "Ojai",
"description": "Sespe aims to bring the best of our local region’s strains, concentrates, drinks, edibles and topicals to patients with all budgets, health backgrounds and tastes.",
"website": "http://sespe.org/",
"image": "https://static.wixstatic.com/media/e4d816_5fe69dd77579484a8980aeffc8beacb3~mv2.png/v1/fill/w_173,h_173,al_c,usm_0.66_1.00_0.01/e4d816_5fe69dd77579484a8980aeffc8beacb3~mv2.png",
"address": "408 Bryant Cir Unit C, Ojai, CA 93023\n",
"lat": 34.443072,
"lng": -119.237926,
"color": "#DC3267",
"products": "Entire lineup of full-spectrum extracts.",
"cartridges": "FALSE",
"high-cbdtincture": "FALSE",
"thcacrystalline": "FALSE"
},

How do I convert the output data into a nice json array of objects?

Diego Diego · Accepted Answer · 2018-04-03T18:40:59

You need to loop through the output and create your own Object.

function makeObject() {
  var batchResponse; // This is the response from batchGet()
  batchResponse = "{\"spreadsheetId\": \"1kEhCvvbHt-9BZgKKp-eQsy7l0AkFtZ9_7cIhvyVGLZ8\",\"valueRanges\": [{\"range\": \"data!A1:O1000\",\"majorDimension\": \"ROWS\",\"values\": [[\"id\",\"name\",\"location\",\"Description\",\"website\",\"image\",\"Address\",\"lat\",\"lng\",\"color\",\"Products\",\"Cartridges\",\"High-CBD Tincture\",\"THCa Crystalline\"],[\"1\",\"Erba\",\"West Los Angeles\",\"Erba’s philosophy is to carry only the most top-tier products, and never sacrifice quality by all means necessary. You’ll feel the difference once you step foot inside their impressive shop.\",\"http:\/\/erbacollective.com\/\",\"https:\/\/static.wixstatic.com\/media\/e4d816_49fd88e6e519486b8be81fd955a52b35~mv2.png\/v1\/fill\/w_173,h_173,al_c,usm_0.66_1.00_0.01\/e4d816_49fd88e6e519486b8be81fd955a52b35~mv2.png\",\"12320 W Pico Blvd, Los Angeles, CA 90064\\n\",\"34.028126\",\"-118.452295\",\"#7CB711\",\"Full-spectrum vape carts, THCa crystalline and tincture.\",\"TRUE\",\"FALSE\",\"TRUE\"],[\"2\",\"99 High Tide\",\"Malibu\",\"99 High Tide is Malibu's only medical marijuana dispensary. They are a visionary collective of Cannabis experts, artists, DJs, growers, healers & shamans.\",\"http:\/\/99hightide.com\/\",\"https:\/\/static.wixstatic.com\/media\/e4d816_a576375fe1104c1399cc0249b7c3818e~mv2.png\/v1\/fill\/w_173,h_173,al_c,usm_0.66_1.00_0.01\/e4d816_a576375fe1104c1399cc0249b7c3818e~mv2.png\",\"22775 Pacific Coast Hwy, Malibu, CA 90265\\n\",\"34.039466\",\"-118.671939\",\"#3A8ABD\",\"Full-spectrum vape carts and THCa crystalline.\",\"TRUE\",\"FALSE\",\"TRUE\"]]}]\n}";
  batchResponse = JSON.parse(batchResponse); // Convert from JSON to Object
  var batchRowValues = batchResponse["valueRanges"][0]["values"];
  var rows = [];
  for (var i=1; i<batchRowValues.length; i++) {
    var rowObject = {};
    for (var j=0; j<batchRowValues[i].length; j++) {
      rowObject[batchRowValues[0][j]] = batchRowValues[i][j];
    }
    rows.push(rowObject);
  }
  return rows;
}

Note:

I hard-coded the example JSON output from batchGet() you provided
This function returns an Array. If you want it to return JSON, then the final line should read return JSON.stringify(rows);.

How to convert Google Sheets v4 api output to a normal JavaScript Array of Objects?

1 Answers