I want to extract object keys into a single array and values in a different array so that I can paste the headers and values into a google sheet.

I want to achieve a dynamic code so that if more fields are pulled in from the API, it can map headers with values.

//API Response Sample.
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"
      }
    ]

So far the below code is fixed not dynamic.

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
}

CodePudding user response：

Object keys into array:

const keys = Object.keys(obj);

Object values into array:

const values = Object.values(obj);

Or both in one go ...

const keys = [];
const values = [];
for (const [key,value] of Object.entries(obj)) {
 keys.push(key);
 values.push(value);
}

If the structure of your object does not change... maybe something like this?

  const action = {};
  data.forEach(obj => {
    for (const [key,value] of Object.entries(obj)) {
      if (Array.isArray(value)) {
        for (const o of value) {
          const a = Object.values(o);
          action[a[0]] = a[1];
        }
      } else action[key] = value;
    }
  })

Try this:

function setResult() {
  const sheet = SpreadsheetApp.getActiveSheet();
  class getResults {
    constructor(arr) {
      this.headers = {};
      this.results = [];
      for (const obj of arr) {
        const actions = {};
        for (const [header,value] of Object.entries(obj)) {
          if (Array.isArray(value)) {
            for (const action of value) {
              const values = Object.values(action);
              actions[values[0]] = values[1];
              this.headers[values[0]] = values[0]
            }
          } else {
            actions[header] = value;
            this.headers[header] = header;
          }
        }
        this.results.push(actions);
      }
    }
    get array() {
      const headers = Object.keys(this.headers);
      const results = [headers];
      for (const action of this.results) {
        results.push(headers.map(header => !!action[header] ? action[header] : ''));
      }
      return results;
    }
  }
  const values = new getResults(data).array;
  sheet.getRange(sheet.getLastRow()   1, 1, values.length, values[0].length).setValues(values);
}

This is a whole function which takes in the 'data' array-object and split it out onto your spreadesheet.