Remove null or empty values from a JSON Object in Google AppScript

Question

Remove null or empty values from a JSON Object in Google AppScript

Navigation

#1 by (2 votes)

3

I am analyzing information within a Google Sheet through Google App Script, through the active total range of the Google Sheet I obtain a javascript object of type multidimensional I transform this into a JSON object with the function JSON.stringify(miObjetoMultidimensional) .

I get an object that in turn is full of empty values, and I try multiple ways to eliminate them from my object because I do not need them but I can not succeed.

I understand that Google AppScript runs on Rhino so I can not execute functions Arrows available in ES6.

This is a part of the object obtained at the output of the function.

[  
 [  
      "asm-tree.jar",
      "",
      "",
      "commons-beanutils.jar",
      "",
      "",
      "commons-collections.jar",
      "",
      "",
      "util-java.jar",
      "",
      "",
      "bcprov-jdk14-1.38.jar",
      "",
      "",
      ""
   ],
   [  
      "",
      "",
      "",
      "",
      "",
      "",
      "",
      "",
      "",
      "",
      "",
      "",
      "",
      "",
      "",
      ""
   ],
   [  
      "\nDependencies",
      "",
      "",
      "\nDependencies",
      "",
      "",
      "\nDependencies",
      "",
      "",
      "\nDependencies",
      "",
      "",
      "\nDependencies",
      "",
      "",
      "\nDependencies"
   ],
   [  
      "",
      "",
      "",
      "",
      "",
      "",
      "",
      "",
      "",
      "",
      "",
      "",
      "",
      "",
      "",
      ""
   ]
]

Try passing a function to validate the data within the same JSON.stringify() but the fields kept appearing empty.

I add my current function.

function listValues() {
  var activeSheet = SpreadsheetApp.getActiveSheet();
  var dataRange = activeSheet.getDataRange();
  var data = dataRange.getValues();

  var json = JSON.stringify(data,function(key, value) {
    if (value === '') { 
      return null; 
    } 
    return value; 
  });

  SpreadsheetApp.getUi().alert(json);

}

javascript json google-apps-script

asked by Max Sandoval 28.08.2017 в 18:22

source

1 answer

Do double split in Java and get two sets of values How to group a repeating record, showing all the different records associated with it?

score 2 · Accepted Answer

Short answer

Use the filter function.

Explanation

The filter function returns the elements that meet a condition. In the case described, the object is an "array" of "arrays" so you should use some type of loop to run through each of the "arrays".

Below is an example using for for rapid demonstration purposes, using Stacksnippet:

var unObjeto = [
  [
    "asm-tree.jar",
    "",
    "",
    "commons-beanutils.jar",
    "",
    "",
    "commons-collections.jar",
    "",
    "",
    "util-java.jar",
    "",
    "",
    "bcprov-jdk14-1.38.jar",
    "",
    "",
    ""
  ],
  [
    "",
    "",
    "",
    "",
    "",
    "",
    "",
    "",
    "",
    "",
    "",
    "",
    "",
    "",
    "",
    ""
  ],
  [
    "\nDependencies",
    "",
    "",
    "\nDependencies",
    "",
    "",
    "\nDependencies",
    "",
    "",
    "\nDependencies",
    "",
    "",
    "\nDependencies",
    "",
    "",
    "\nDependencies"
  ],
  [
    "",
    "",
    "",
    "",
    "",
    "",
    "",
    "",
    "",
    "",
    "",
    "",
    "",
    "",
    "",
    ""
  ]
]

for(var i = 0; i< unObjeto.length;i++){
  unObjeto[i] = unObjeto[i].filter(function(n){ return n != "" }); 
}
console.log(unObjeto)

The OP code would be as follows

function listValues() {
  var activeSheet = SpreadsheetApp.getActiveSheet();
  var dataRange = activeSheet.getDataRange();
  var data = dataRange.getValues();

  for(var i = 0; i< data.length;i++){
    data[i] = data[i].filter(function(n){ return n != "" }); 
  }

  var json = JSON.stringify(data,function(key, value) {
    if (value === '') { 
      return null; 
    } 
    return value; 
  });


  SpreadsheetApp.getUi().alert(json);

}