CODEWITHSUNDEEP
javascriptarraysobjectgoogle-apps-scriptgoogle-sheets
zummon
zummon

Reputation: 986

How to convert table data to an object and have headers as keys

Table data which I get from spreadsheet would be like

Name      Position  Office  Age   ...  << headers
Thor      Developer A       33    ...  << body
Quinn     Support   C       44    ...     .
Jennifer  Junior    Wide    22    ...     .
...       ...       ...     ...   ...     .

How to convert them to an object and return like this result

{
  Name     : ["Thor"      ,"Quinn"   ,"Jennifer" ,...]
  Position : ["Developer" ,"Support" ,"Junior"   ,...]
  Office   : ["A"         ,"C"       ,"Wide"     ,...]
  Age      : ["33"        ,"44"      ,"22"       ,...]
  ...      : [...         ,...       ,...        ,...]
}

Is this possible to make?

on code side I can get the keys correctly but with null data

function tableToObject() {
  var values = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getDataRange().getValues()

  var headers = values[0]
  var body = values.slice(1)

  var keys = {}

  headers.forEach(function(c){
    keys[c] = body.map(r => r[c]) // stuck with this part
  })
  Logger.log(keys)
}

Sample sheet https://docs.google.com/spreadsheets/d/1n8WBKOGfV7q9cIKnTxFmy4xSjSZTCx-3tsMrrXj28xU/edit?usp=sharing

Thank you in advance

Upvotes: 0

Views: 1165

Answers (3)

Aerials
Aerials

Reputation: 4419

Sometimes it helps to comment on the code to break down a function into logical steps.

You get the desired result by using the following code:

function tableToObject() {
// Get the table values
  var values = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getDataRange().getValues(); 
// Extract headers
  var headers = values[0];
// Extract the body
  var body = values.slice(1);
// Initialize keys obj
  var keys = {};
// You need an index for looping through the body rows
  var i = 0;
// For each header
  headers.forEach(function(c){
  // Create an empty list for the values
    keys[c] = [];
  // For every row in the body assign the values to their keys 
    body.map(r => keys[c].push(r[i]));
    i++;
  })
  Logger.log(keys);
}

Upvotes: 3

DiSaSteR
DiSaSteR

Reputation: 636

I would recommend using some methods of object to manipulate the data. Here is all the help you need: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Object

https://www.digitalocean.com/community/tutorials/how-to-use-object-methods-in-javascript

Make yourself a friend with all the available methods and remember them for future optimal solutions.

Upvotes: 0

EspressoBeans
EspressoBeans

Reputation: 2015

I quickly came up with this. It's not super advanced coding, but hopefully it gives you some ideas.

//a sample input
const spreadsheet = [
  ["Name", "Age", "Gender", "Eyes"],
  ["Jerry", "23", "M", "Blue"],
  ["George", "34", "M", "Green"],
  ["Elaine", "27", "F", "Brown"],
  ["Kramer", "27", "M", "Hazel"]
]

let output = {};    //your main output
let header = true;  //this is a flag
let columns;        //these are your column headers
let i;
for (i = 0; i < spreadsheet.length; i++) {
  let row = spreadsheet[i];
  let k; //this is column count

  //on the first iteration create the keys of the object using header values
  if (header) {
    let keys = row;
    for (k = 0; k < keys.length; k++) {
      output[keys[k]] = [];
    }
    header = false;
    columns = Object.keys(output);
    //console.log(Object.keys(output));
  } else {
    //here we add values to each array of the corresponding object key
  	let v; 
  	for (v = 0; v < columns.length; v++){
    	output[columns[v]].push(row[v]);
      //console.log(columns[v]);
    } 	
  }
}
console.log(output);

Upvotes: 2

Related Questions