How to get unique data of google sheets to json with appscript?

Question

I'm getting values of the sheet in JSON format. My Sheet I've added this code in appscript to get JSON:

var ss = SpreadsheetApp.openByUrl("Spreadsheet URL");

var sheet = ss.getSheetByName('Sheet1'); 

function doGet(e){

var action = e.parameter.action;

  if(action == 'getItems'){
    return getItems(e);

  }
  
  }

function getItems(e){
  
  var records={};
 
  var rows = sheet.getRange(2, 1, sheet.getLastRow() - 1,sheet.getLastColumn()).getValues();
      data = [];

  for (var r = 0, l = rows.length; r < l; r++) {
    var row     = rows[r],
        record  = {};
    record['customerName'] = row[0];
    record['docketNo']=row[1];
    record['docketDate']=row[2];
    record['destination']=row[3];
    record['weight']=row[4];
    
    data.push(record);
   }
  records = data;
  var result=JSON.stringify(records);
  return ContentService.createTextOutput(result).setMimeType(ContentService.MimeType.JSON);
}

Current JSON data:

[
   {
      "customerName":"cash",
      "docketNo":"d87976489",
      "docketDate":"2021-08-14T18:30:00.000Z",
      "destination":"kanpur",
      "weight":1
   },
   {
      "customerName":"cash",
      "docketNo":"d87976480",
      "docketDate":"2021-08-12T18:30:00.000Z",
      "destination":"kanpur",
      "weight":1
   },
   {
      "customerName":"abc",
      "docketNo":"d87976482",
      "docketDate":"2021-09-12T18:30:00.000Z",
      "destination":"mumbai",
      "weight":2
   }
]

I want this JSON data:

[
   {
      "customerName":"cash",
      "docketNo":"d87976489","d87976480",
      "docketDate":"2021-08-14T18:30:00.000Z","2021-08-12T18:30:00.000Z",
      "destination":"kanpur","kanpur",
      "weight":1,1
   },
   {
      "customerName":"abc",
      "docketNo":"d87976482",
      "docketDate":"2021-09-12T18:30:00.000Z",
      "destination":"mumbai",
      "weight":2
   }
]

I have the same customer names therefore, I want unique data. I've searched many websites & videos but I didn't get one. Please give me appscript code to get this data!

Are you saying that docketNo,docketDate,destination and weight are arrays? If so, you didn't populate them as arrays so where does the addtional data come from? Do those cells need to be split? Are they delimited somehow? — Cooper, Aug 17 '21 at 05:47
Oh I finally looked at your image. You need to do a pivot table for you data. You can just take it one line at a time — Cooper, Aug 17 '21 at 05:52
Here's an example with a pivot table and a reverse pivot table: https://stackoverflow.com/a/59539949/7215091 — Cooper, Aug 17 '21 at 05:54
The json format you want will cause an error when parsing! What I recommend is: first arrange your spreadsheet to put both dates in the same cell, and afterwards apply the pseudo-standard script instead of creating too specific one! — Mike Steelson, Aug 17 '21 at 06:43
You can certainly do it with JSON that's not a problem but when I speak of a pivot table I simply talking about a software modification to collect data using an object which allows you to build your final records from multiple lines of your data. I'm not talking about pivot tables like you can create on Google Sheets manually and as far as the final output is concern it can be anything you want but a JSON format is particularly easy when you're dealing with a Javascript like language like Google Apps script. — Cooper, Aug 17 '21 at 12:56

score 0 · Answer 1 · answered Aug 17 '21 at 07:47

You can do it with some Javascript acrobatics

Sample:

function makeJsonUnique() {
  var json = [
    {
      "customerName":"cash",
      "docketNo":"d87976489",
      "docketDate":"2021-08-14T18:30:00.000Z",
      "destination":"kanpur",
      "weight":1
    },
    {
      "customerName":"cash",
      "docketNo":"d87976480",
      "docketDate":"2021-08-12T18:30:00.000Z",
      "destination":"kanpur",
      "weight":1
    },
    {
      "customerName":"abc",
      "docketNo":"d87976482",
      "docketDate":"2021-09-12T18:30:00.000Z",
      "destination":"mumbai",
      "weight":2
    }
  ]
  
  var customerNames = json.map(e=>e.customerName)
  var uniqueCustomerNames = [...new Set(customerNames)]
  var newJSON =[]
  
  uniqueCustomerNames.forEach(function(name){
    var tempObj ={}
    tempObj.customerName = name
    var jsonSubsets = json.filter(function(obj){return obj.customerName == name})
    tempObj.docketNo = jsonSubsets.map(obj=>obj.docketNo).join(",")
    tempObj.docketDate = jsonSubsets.map(obj=>obj.docketDate).join(",")
    tempObj.destination = jsonSubsets.map(obj=>obj.destination).join(",")
    tempObj.weight = jsonSubsets.map(obj=>obj.weight).join(",")
    console.log(JSON.stringify(tempObj))
    newJSON.push(tempObj)
  })
  console.log(newJSON)
}

Used methods:

How to get unique data of google sheets to json with appscript?

1 Answers1

You can do it with some Javascript acrobatics