Flatten each action from the Facebook API into its own column using appscript-CodePudding

The Facebook API response combines all actions into a single field, which is usually inconvenient for reporting. I would like to flatten each action into its own column as shown in the picture below.

"data": [
      {
        "actions": [             
          {
            "action_type": "comment",
            "value": "3"
          },
          {
            "action_type": "like",
            "value": "33"
          },
          {
            "action_type": "link_click",
            "value": "1531"
          },
          {
            "action_type": "mobile_app_install",
            "value": "1049"
          }
        ],
        "spend": "8621.03",
        "date_start": "2017-10-28",
        "date_stop": "2017-11-26"
      }
    ]

const sheet = SpreadsheetApp.getActiveSheet();
const headers = Object.keys(data[0]);
var values = data.map(o => headers.map(h => o[h]));
sheet.getRange(sheet.getLastRow()   1, 1, values.length, values[0].length).setValues(values);

//Current Result from the existing Code - Console Log

["actions", "spend", "date_start", "date_stop"]
[[[{
  action_type: "comment",
  value: "3"
}, {
  action_type: "like",
  value: "33"
}, {
  action_type: "link_click",
  value: "1531"
}, {
  action_type: "mobile_app_install",
  value: "1049"
}], "8621.03", "2017-10-28", "2017-11-26"]]

CodePudding user response：

Assuming that the data you get back from the Facebook API has a fixed structure, i.e. the number of actions is always the same.

function myFunction() {
 var data = [
  {
    "actions": [             
      {
        "action_type": "comment",
        "value": "3"
      },
      {
        "action_type": "like",
        "value": "33"
      },
      {
        "action_type": "link_click",
        "value": "1531"
      },
      {
        "action_type": "mobile_app_install",
        "value": "1049"
      }
    ],
    "spend": "8621.03",
    "date_start": "2017-10-28",
    "date_stop": "2017-11-26"
  }
]

const sheet = SpreadsheetApp.getActiveSheet();

//flatten the objects
var actionObjects = data.map(returnAction)

//get the headers
var headers = Object.keys(actionObjects[0])

//create a 2D array for rows
var actionRows = actionObjects.map(a => headers.map(h => a[h])) 

//write the headers
sheet.getRange(sheet.getLastRow()   1, 1, 1, headers[0].length).setValues([headers]);

//write the rows
sheet.getRange(sheet.getLastRow()   1, 1, actionRows.length, actionRows[0].length).setValues(actionRows);
}

function returnAction(data){
  let action = {}
  data.actions.forEach(a => action[a.action_type] = a.value)
  action ['spend'] = data.spend
  action ['date_start'] = data.date_start
  action ['date_stop'] = data.date_stop
  return action
}