0
143
ViewsHow can I exclude null elements from data array?
I have been using Yuri's answer to a question, but found myself in a new situation and I haven't quite understood each step of this method here:
function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet();
var header = sheet.getDataRange().getValues()[0];
var data = [
["ID","Variação","Nome Site","Obs"],
[11602,185,"Camisa Teste","Teste da Observação"]
]
var indexes = header.map(x => data[0].indexOf(x));
data = data.map(x => indexes.map(i => x.slice()[0] = x[i]));
sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}
Here's what the result looks like:
let result = [
["ID","Variação","Nome Site","Obs", "", ""],
[11602,185,"Camisa Teste","Teste da Observação", "", ""]
]
Now, this is returning data with nulls. How can I make it return only mapped array elements?
So, instead of heaving an array length of 6, that'd be only 4.
Expected Result:
let expectedResult = [
["ID","Variação","Nome Site","Obs"],
[11602,185,"Camisa Teste","Teste da Observação"]
]
Thank you!
3 answers
Answer question0
One possible reason why you are having null elements in your array is that the cells next to your header have " " or space character in it. Since you are using getDataRange() it will also include those " " characters in your array.
Example:
Here, I added space character to cells E1 and F1:
Here are the values of data after using map function:
If I remove the values of cells E1 and F1 the output of data is:
Null values can also occur if the values below the header overlap the number of columns in your header.
Example:
Output:
Solution:
Instead of using getDataRange(), explicitly define the range you are using for the header. You can use either getRange(row, column, numRows, numColumns) or getRange(a1Notation).
Reference:
0
One way is to filter the inner values:
let result = [
["ID", "Variação", "Nome Site", "Obs", "", ""],
[11602, 185, "Camisa Teste", "Teste da Observação", "", ""]
]
result = result.map(x => x.filter(y => y))
console.log(result)0
Remove nulls
function test() {
Logger.log(JSON.stringify([1,2,3,null,4,null,5,''].filter(e => e)))
}
Execution log
7:48:37 PM Notice Execution started
7:48:37 PM Info [1,2,3,4,5]
7:48:38 PM Notice Execution completed