I am working on a script in Google Sheets to import JSON data via API and need to be able to parse the data into columns in a spreadsheet. The API is returning a multi-dimensional array with a key that is a randomly generated ID.

{
   "log":{
      "5DUA3NAuET1O7TDhIvmC":{
         "log":4440,
         "title":"Trade money outgoing",
         "timestamp":1649773788,
         "category":"Trades",
         "data":{
            "user":282048,
            "trade_id":"[<a href = \"/trade.php#step=view&ID=6942373\"target = \"_self\">view</a>]",
            "money":39562944
         },
         "params":{
            
         }
      }
   }
}

How can I

1. Parse the data into a multidimensional object
2. Access the values like title, data.cost, etc to be able to import this data to a sheet?

CodePudding user response：

Try

var result = []
function test(){
  jsonString = `{
   "log":{
      "5DUA3NAuET1O7TDhIvmC":{
         "log":4440,
         "title":"Trade money outgoing",
         "timestamp":1649773788,
         "category":"Trades",
         "data":{
            "user":282048,
            "trade_id":"[<a href = \\"/trade.php#step=view&ID=6942373\\"target = \\"_self\\">view</a>]",
            "money":39562944
         },
         "params":{
            
         }
      }
   }
}`
  let json= JSON.parse(jsonString.replace(/(\r\n|\n|\r|\t|  )/gm,""))
  getMyData(json)
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test').getRange(1,1,result.length,result[0].length).setValues(result)
}
function getMyData(obj) {
  for (let p in obj) {
    if (obj[p]!=null){
      if (typeof obj[p] != 'object' && typeof obj[p] != 'function'){
        result.push([p, obj[p]]);
      }
      if (typeof obj[p] == 'object') {
        result.push([p, '']);
        getMyData( obj[p] );
      }
    }
  }
}

CodePudding user response：

var response = {
    "log": {
        "5DUA3NAuET1O7TDhIvmC": {
            "log": 4440,
            "title": "Trade money outgoing",
            "timestamp": 1649773788,
            "category": "Trades",
            "data": {
                "user": 282048,
                "trade_id": "[<a href = \"/trade.php#step=view&ID=6942373\"target = \"_self\">view</a>]",
                "money": 39562944
            },
            "params": { }
        }
    }
}

var key = Object.keys(response.log)[0];
console.log(key);

var obj = response.log[key];
console.log(obj.title);
console.log(obj.data.user);