0

I've created a custom function to compile in one array data from different sheets in Google Sheets.

Here is how it goes:

function COMPILER(){
  var i;
  var c;
  var display = [];
  var resultado = [];
  for (i = 0; i < arguments.length; i++) {
    display.push(arguments[i]);
  };
  for (c = 0; c < display.length; c++) {
    resultado = resultado.concat(display[c]);
  };
  return resultado;
};

The code runs just fine, but I've ran into an unexpected issue. Since there are hidden formulas in between the data sources, my final array is compiled with several null values. The final matrix looks a little like this:

resultado [[3, f, 2, 6, 0, r], [2, 2, t, 5, 6, 8], **[, , , , , ]**, **[, , , , , ]**, **[, , , , , ]**, [4, y, y, 7, 8, 0], **[, , , , , ]**...]

I have no clue how to correctly (and selectively) remove those empty values from within the matrix. I would assume I'd have to read and test the whole matrix and remove each selected value, everything within a loop, but I couldn't do on my own.

Improve this question
1
  • 2
    Please add a minimal reproducible example (what are you passing as arguments to your custom function?, how are hidden the formulas? Are you using filters?) Commented Dec 6, 2019 at 21:39

2 Answers 2

Reset to default
2

This code will help you with your issue, it takes every array within the main array and then checks if there are null values to put them away and build a new clean array.

function testArray(){
  var arrTest = [[3, 'f', 2, 6, 0, 'r'], [2, 2, 't', 5, 6, 8],[, , , , , ],[, , , , , ], [, , , , , ], [4, 'y', 'y', 7, 8, 0], [, , , , , ]];
  // Initiate an empty array to fill it with the non empty data from your Array
  var cleanArr = [];
  // Check every array inside your main array
  arrTest.forEach(function(el){
    // If there are null values, this will clean them
    var filteredArr = el.filter(function(e){ return e != null;});
    // If the array ends up empty, don't push it into your clean array
    if(filteredArr.length) cleanArr.push(filteredArr);
  });
  Logger.log(cleanArr);
}

Docs

Due to the fact you are handling a lot of operations with arrays, I would recommend you to check this:

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Typo in your code: if(filteredArr.length) cleanArr.push(filteredArr);
FYI: Using a 24 inner array the forEach method was slightly faster than the Some method.
1

I modified your function to get a range.getValues() Object[][] on a sheet that I had removed one of the rows out from the middle of the page. I used the Array.some method to removed the empty row.

Below I have included the code and the Logger.log outputs

function function101(){
  var result = [];
  Logger.log(JSON.stringify(arguments));
  for(var i=0;i<arguments[0].length;i++) {
    Logger.log(arguments[0][i]);
    if(arguments[0][i].some(function(e){return e;})) { 
      result.push(arguments[0][i]);
    }
  }
  Logger.log(result);
  return result;
}

//I ran this function
function testfunction101() {
  var vA=SpreadsheetApp.getActiveSheet().getDataRange().getValues();
  function101(vA);
}

/*
[19-12-06 17:24:49:234 MST] {"0":[["HDR1","HDR2","HDR3","HDR4","HDR5","HDR6","HDR7","HDR8","HDR9","HDR10"],[6,9,5,16,7,8,3,6,17,18],[14,19,12,17,10,13,0,2,19,16],[7,3,13,11,15,14,5,17,9,6],[11,10,11,11,4,5,18,9,11,9],[12,6,2,6,5,12,1,2,4,9],[3,16,11,19,16,12,0,19,8,1],[3,0,5,8,8,12,16,6,4,18],[8,0,3,4,11,2,3,7,14,18],[11,15,16,8,7,4,15,16,16,0],[15,5,11,18,8,15,8,2,8,16],[12,8,1,12,3,1,12,11,12,19],[10,11,9,4,12,11,19,17,4,0],["","","","","","","","","",""],[2,4,1,5,14,3,14,15,13,11],[3,7,0,0,18,15,16,11,8,7],[19,7,7,17,17,9,15,14,15,5],[13,2,19,19,8,15,16,13,16,2],[16,3,16,13,13,15,9,1,17,15],[9,1,4,8,3,1,9,8,19,5],[7,13,17,9,6,2,18,1,3,16]]}
[19-12-06 17:24:49:235 MST] [HDR1, HDR2, HDR3, HDR4, HDR5, HDR6, HDR7, HDR8, HDR9, HDR10]
[19-12-06 17:24:49:236 MST] [6.0, 9.0, 5.0, 16.0, 7.0, 8.0, 3.0, 6.0, 17.0, 18.0]
[19-12-06 17:24:49:237 MST] [14.0, 19.0, 12.0, 17.0, 10.0, 13.0, 0.0, 2.0, 19.0, 16.0]
[19-12-06 17:24:49:238 MST] [7.0, 3.0, 13.0, 11.0, 15.0, 14.0, 5.0, 17.0, 9.0, 6.0]
[19-12-06 17:24:49:239 MST] [11.0, 10.0, 11.0, 11.0, 4.0, 5.0, 18.0, 9.0, 11.0, 9.0]
[19-12-06 17:24:49:239 MST] [12.0, 6.0, 2.0, 6.0, 5.0, 12.0, 1.0, 2.0, 4.0, 9.0]
[19-12-06 17:24:49:240 MST] [3.0, 16.0, 11.0, 19.0, 16.0, 12.0, 0.0, 19.0, 8.0, 1.0]
[19-12-06 17:24:49:241 MST] [3.0, 0.0, 5.0, 8.0, 8.0, 12.0, 16.0, 6.0, 4.0, 18.0]
[19-12-06 17:24:49:242 MST] [8.0, 0.0, 3.0, 4.0, 11.0, 2.0, 3.0, 7.0, 14.0, 18.0]
[19-12-06 17:24:49:242 MST] [11.0, 15.0, 16.0, 8.0, 7.0, 4.0, 15.0, 16.0, 16.0, 0.0]
[19-12-06 17:24:49:243 MST] [15.0, 5.0, 11.0, 18.0, 8.0, 15.0, 8.0, 2.0, 8.0, 16.0]
[19-12-06 17:24:49:243 MST] [12.0, 8.0, 1.0, 12.0, 3.0, 1.0, 12.0, 11.0, 12.0, 19.0]
[19-12-06 17:24:49:244 MST] [10.0, 11.0, 9.0, 4.0, 12.0, 11.0, 19.0, 17.0, 4.0, 0.0]
[19-12-06 17:24:49:244 MST] [, , , , , , , , , ]
[19-12-06 17:24:49:245 MST] [2.0, 4.0, 1.0, 5.0, 14.0, 3.0, 14.0, 15.0, 13.0, 11.0]
[19-12-06 17:24:49:246 MST] [3.0, 7.0, 0.0, 0.0, 18.0, 15.0, 16.0, 11.0, 8.0, 7.0]
[19-12-06 17:24:49:246 MST] [19.0, 7.0, 7.0, 17.0, 17.0, 9.0, 15.0, 14.0, 15.0, 5.0]
[19-12-06 17:24:49:247 MST] [13.0, 2.0, 19.0, 19.0, 8.0, 15.0, 16.0, 13.0, 16.0, 2.0]
[19-12-06 17:24:49:248 MST] [16.0, 3.0, 16.0, 13.0, 13.0, 15.0, 9.0, 1.0, 17.0, 15.0]
[19-12-06 17:24:49:249 MST] [9.0, 1.0, 4.0, 8.0, 3.0, 1.0, 9.0, 8.0, 19.0, 5.0]
[19-12-06 17:24:49:249 MST] [7.0, 13.0, 17.0, 9.0, 6.0, 2.0, 18.0, 1.0, 3.0, 16.0]
[19-12-06 17:24:49:250 MST] [[HDR1, HDR2, HDR3, HDR4, HDR5, HDR6, HDR7, HDR8, HDR9, HDR10], [6.0, 9.0, 5.0, 16.0, 7.0, 8.0, 3.0, 6.0, 17.0, 18.0], [14.0, 19.0, 12.0, 17.0, 10.0, 13.0, 0.0, 2.0, 19.0, 16.0], [7.0, 3.0, 13.0, 11.0, 15.0, 14.0, 5.0, 17.0, 9.0, 6.0], [11.0, 10.0, 11.0, 11.0, 4.0, 5.0, 18.0, 9.0, 11.0, 9.0], [12.0, 6.0, 2.0, 6.0, 5.0, 12.0, 1.0, 2.0, 4.0, 9.0], [3.0, 16.0, 11.0, 19.0, 16.0, 12.0, 0.0, 19.0, 8.0, 1.0], [3.0, 0.0, 5.0, 8.0, 8.0, 12.0, 16.0, 6.0, 4.0, 18.0], [8.0, 0.0, 3.0, 4.0, 11.0, 2.0, 3.0, 7.0, 14.0, 18.0], [11.0, 15.0, 16.0, 8.0, 7.0, 4.0, 15.0, 16.0, 16.0, 0.0], [15.0, 5.0, 11.0, 18.0, 8.0, 15.0, 8.0, 2.0, 8.0, 16.0], [12.0, 8.0, 1.0, 12.0, 3.0, 1.0, 12.0, 11.0, 12.0, 19.0], [10.0, 11.0, 9.0, 4.0, 12.0, 11.0, 19.0, 17.0, 4.0, 0.0], [2.0, 4.0, 1.0, 5.0, 14.0, 3.0, 14.0, 15.0, 13.0, 11.0], [3.0, 7.0, 0.0, 0.0, 18.0, 15.0, 16.0, 11.0, 8.0, 7.0], [19.0, 7.0, 7.0, 17.0, 17.0, 9.0, 15.0, 14.0, 15.0, 5.0], [13.0, 2.0, 19.0, 19.0, 8.0, 15.0, 16.0, 13.0, 16.0, 2.0], [16.0, 3.0, 16.0, 13.0, 13.0, 15.0, 9.0, 1.0, 17.0, 15.0], [9.0, 1.0, 4.0, 8.0, 3.0, 1.0, 9.0, 8.0, 19.0, 5.0], [7.0, 13.0, 17.0, 9.0, 6.0, 2.0, 18.0, 1.0, 3.0, 16.0]]

I'm not sure if this was your problem but you said I've created a custom function to compile in one array data from different sheets in Google Sheets. and my input data was gathered from a sheet.getDataRange() method.

Improve this answer

1 Comment

Could not run your code as written. Made minor changes and it works. code const bigArray = [ [10.0, 11.0, 9.0, 4.0, 12.0, 11.0, 19.0, 17.0, 4.0, 0.0], [, , , , , , , , , ], [2.0, 4.0, 1.0, 5.0, 14.0, 3.0, 14.0, 15.0, 13.0, 11.0] ]; var cleanArr = []; for ( i=0 ; i < bigArray.length ; i++ ) { if(bigArray[i].some( // not completely empty function(e) { return e; } )) cleanArr.push(bigArray[i]); } Logger.log(JSON.stringify(cleanArr)); code

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.