时间戳

首页 > 解决方案 > 如何将谷歌表格数据转换为json

javascript - 如何将谷歌表格数据转换为json

问题描述

在此处输入图像描述我目前正在尝试使用一个脚本，以便将谷歌工作表数据转换为可以包含相邻和嵌套对象的 json。目前我拥有的是一个脚本，它可以允许工作表数据转换为允许嵌套对象的 json，但它不允许结束一个对象并开始一个新对象，因此不能有任何相邻对象，而是有一个包含子对象的父对象，这不是我想要的。我希望我只是在当前脚本中遗漏了一些东西，以便能够结束和启动新对象，所以我将在下面添加脚本，感谢您对这个问题的任何贡献！

function formJSON() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");
  var data = sheet.getDataRange().getValues();
  var currentObject = {};
  var output = currentObject;                    
  for (var i = 0; i < data.length; i++) {
    if (data[i][1]) {
      currentObject[data[i][0]] = data[i][1];
    }
    else {
      var newObject = {};
      currentObject[data[i][0]] = newObject;
      currentObject = newObject;
    }
  }
  Logger.log(JSON.stringify(output));
}

编辑：在这里，我将提供当前结果与我所追求的结果。第一个结果来自我作为图像添加的工作表。

当前结果：

{
  "": {
    "asset": {
      "button": {
        "viewPDF": "View PDF",
        "viewSurvey": "View Survey",
        "viewPPT": "View PPT",
        "viewLink": "View Link",
        "rejoinMeeting": "Rejoing Meeting",
        "labels": {
          "associatedWith": "Associated Content",
          "attendees": "Attendees in this session",
          "filesAndDocs": "Files and Documents",
          "location": "Location",
          "messages": {
            "errorRetrieving": "There was an error retrieving the session details",
            "noAttendees": "Nobody is watching this session currently",
            "browser": {
              "messages": {
                "notSupported": "Your browser is not supported",
                "update": "Please update"
              }
            }
          }
        }
      }
    }
  }
}

期望的结果：

"asset": {
    "buttons": {
      "viewPDF": "View PDF",
      "viewSurvey": "View Web Page",
      "viewPPT": "View Presentation",
      "viewLink": "View Link",
      "rejoinMeeting": "Rejoin Meeting"
    },
    "labels": {
      "associatedWith": "Associated Content",
      "attendees": "Attendees in this Session",
      "filesAndDocs": "Files and Documents",
      "location": "Location",
      "notStarted": "This session hasn't started yet.",
      "playlist": "Session Playlist",
      "premiumSponsors": "Premium Sponsors",
    },
    "messages": {
      "errorRetrieving": "There was an error retrieving the session details.",
      "noAttendees": "Nobody is watching this session currently",
      "pointsForDocument": "viewing a document",
      "pointsForRatingAsset": "rating this asset",
      "pointsForVideo": "watching a video",
      "problemSaving": "There was a problem saving your rating. Please try again."
    }
  },
  "browser": {
    "messages": {
      "notSupported": "Your Browser Is Not Supported",
      "update": "Please download the most up-to date version of one of the following and try again"
    }
  },

标签： javascriptjsongoogle-apps-scriptgoogle-sheetsdata-conversion

解决方案

请查看以下内容：

  var data = sheet.getDataRange().getValues();
  var currentObject = {};
  var title ='';
  var newObject = {};
  for (var i = 0; i < data.length; i++) {
    if (data[i][1]) {
      newObject[data[i][0]] = data[i][1];
    }
    else {
      if(data[i][0] !="" && data[i][0] !=" "){
        if(title != ""){
          currentObject[title] = newObject;
        }
        title = data[i][0];
        newObject = {};
      }
    }
  }
  Logger.log(JSON.stringify(currentObject));

虽然这不是一个完整的解决方案，但我认为它应该为您指明正确的方向。

  • 这个想法是你应该有一些变量（在这种情况下title），它在语句中定义/覆盖，它是在下一个条件else期间将分配嵌套对象的键if
  • 再次else输入条件后，title将被下一个嵌套对象键覆盖。

推荐阅读