代码之家 › 专栏 › 技术社区 › Kyle Pennell

如何将Google Sheets v4 api输出转换为普通的JavaScript对象数组?

google-spreadsheet-api google-sheets reactjs

Kyle Pennell · 技术社区 · 6 年前

我试图找到这个问题的答案,但一切似乎都与其他事情或之前版本的api有关。

我正在尝试使用google电子表格作为基本的json后端。我的电子表格如下所示:

Google sheets api v4有一个名为 batchGet 。电话( https://sheets.googleapis.com/v4/spreadsheets/1kEhCvvbHt-9BZgKKp-eQsy7l0AkFtZ9_7cIhvyVGLZ8/values:batchGet?ranges=data&majorDimension=ROWS )将我的电子表格输出如下:

{
"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"
],
[

我需要的是这样的东西(为了让它在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"
},

如何将输出数据转换为一个漂亮的json对象数组?

1 回复 | 直到 6 年前

Diego 6 年前

您需要循环输出并创建自己的对象。

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;
}

注:

我硬编码了来自 batchGet() 您提供的
此函数返回一个数组。如果希望它返回JSON,那么最后一行应该是 return JSON.stringify(rows); 。