CODEWITHSUNDEEP
javascriptjsongoogle-apps-scriptgoogle-sheets
Liron Paryente
Liron Paryente

Reputation: 169

Build an array of JSON objects from a spreadsheet table

I'm looking for a reference for how to create an array from a table using Google app script. not sure how I'm looping through a 3 rows table with 3 columns and save the data into [] var.

like:

   var users = [
   {
       firstname: '', lastname: '', phone: ''
     },
     {
       firstname: '', lastname: '', phone: ''
     }
   ];

Upvotes: 1

Views: 1145

Answers (1)

Marios
Marios

Reputation: 27350

From spreadsheet to 2D array of json objects:

Explanation:

I found this very useful code snippet on github which you can use to achieve your goal.

  • It assumes that the first row of the data is the headers.

Code snippet:

function myFunction() {

 // get the data from the sheet
 const ss = SpreadsheetApp.getActive();
 const sh = ss.getSheetByName("Sheet1"); // change to the name of your sheet
 const arr = sh.getRange('A1:C'+sh.getLastRow()).getValues();
 
 //create JSON object from 2 dimensional Array

    //assuming header
    var keys = arr[0];
    //vacate keys from main array
    var newArr = arr.slice(1, arr.length);

    var formatted = [],
    data = newArr,
    cols = keys,
    l = cols.length;
    for (var i=0; i<data.length; i++) {
            var d = data[i],
                    o = {};
            for (var j=0; j<l; j++)
                    o[cols[j]] = d[j];
            formatted.push(o);
    }
 
 // formatted is the desired result
 console.log(formatted) // 2D array of json objects
    
}

Output (formatted) based on data of the sheet used in the next section:

example1

From 2D array of json objects to spreadsheet:

Explanation:

You can iterate over the keys and values of the object elements of the users array.

Code snippet:

function myFunction() {

 // the raw data
 const users = [
   {
       firstname: 'George', lastname: 'Brown', phone: '12345'
     },
     {
       firstname: 'Nick', lastname: 'Yellow', phone: '67890'
     }
   ];
 
 // get headers and rows from users
 const data = [Object.keys(users[0])]
 users.forEach(js=>{
  data.push(Object.values(js));
 });
   
 // paste data back to the sheet
 const ss = SpreadsheetApp.getActive();
 const sh = ss.getSheetByName("Sheet1"); // change to the name of your sheet
 sh.getRange(sh.getLastRow()+1,1,data.length,data[0].length).setValues(data);
    
}

Example sheet used for the code snippet:

example2

Upvotes: 2

Related Questions