Home / Questions / 97960
Accepted
Max Sandoval
Asked: 2020-08-29 08:22:52 +0800 CST 2020-08-29 08:22:52 +0800 CST

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

  • 772

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

I get an object that in turn is full of empty values, I already tried multiple ways to remove them from my object because I don't need them but I can't succeed.

I understand that Google AppScript runs on top of Rhino so I can't run arrow functions available in ES6.

This is a part of the object obtained in 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"
   ],
   [  
      "",
      "",
      "",
      "",
      "",
      "",
      "",
      "",
      "",
      "",
      "",
      "",
      "",
      "",
      "",
      ""
   ]
]

I tried passing a function to validate the data inside it 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

1 Answers

  1. Best Answer

    Short answer

    Use the function filter.

    Explanation

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

    Below is an example using forfor quick demo 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's code would look like this

    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);

}
    • 2