Tips to Filter Array of Arrays

[Reza Azmi]

Hi,

I am trying to filter an array of arrays with another and I can't seem to see where I am going wrong. 

The first array "resultsarry" - looks like this: 

[[70.0, Chan Bon Siong (WAGS-70 ), Plot ID-151, , 0.809, 149.0, Air Kuning CC - TJC - Collection Center (with ramp) - OPWA-3, , 0.6], etc etc ]

The second array is the "filterarry" which is a shortlist:

[[139.0], [139.0], [95.0], [188.0], [2209.0], [1357.0], [1376.0], [1376.0], [76.0], [79.0], [80.0], [182.0], [182.0], [142.0], [58.0], etc etc ]

What I am trying to do is to filter the resultsarry to only show those members (numbers) that are in the filter array. 

What I do get is this ...

[[], 139.0, 139.0, 139.0, etc etc ] 

The code I have looks like this: 

// Filter the Arrays

let filteredData =[[]];

for(var i = 0; i < resultArray.length; i++){

for(var j = 0; j < filterArray.length; j=i++){

resultArray.forEach(myFunction);

function myFunction(item) {

if(resultArray[i][0] === filterArray[j][0])

{

// Add the row to the filtered data

filteredData.push(resultArray[i][0]);

}

}

}

}

Can anyone spot where I am getting the logic wrong? or maybe there is a more elegant way of doing this?

Thanks,

Reza

[cbmserv...@gmail.com]

I believe you want the whole record and not just one entry kept.

 

So in your code, change it to this:

 

let filteredData =[[]];

 

for(var i = 0; i < resultArray.length; i++){

for(var j = 0; j < filterArray.length; j=i++){

resultArray.forEach(myFunction);

function myFunction(item) {

if(resultArray[i][0] === filterArray[j][0])

{

// Add the row to the filtered data

filteredData.push(resultArray[i]);

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/503f7d9a-2b90-4361-a398-4d21dc513e52n%40googlegroups.com.

[Reza Azmi]

Thanks - correct. 

The only issue is it seems to be finding one match and repeats it over and over again. So something seems off on the For loop which i cant see what. 

The result is now ... 

Logging output too large. Truncating output. [[], [139.0, Neoh Ah Seng (WAGS-139 ), , 2.434, , , Air Kuning CC - TJC - Collection Center (with ramp) - OPWA-3, , 2.5], [139.0, Neoh Ah Seng (WAGS-139 ), , 2.434, , , Air Kuning CC - TJC - Collection Center (with ramp) - OPWA-3, , 2.5], [139.0, Neoh Ah Seng (WAGS-139 ), , 2.434, , , Air Kuning CC - TJC - Collection Center (with ramp) - OPWA-3, , 2.5], [139.0, Neoh Ah Seng (WAGS-139 ), , 2.434, , , Air Kuning CC - TJC - Collection Center (with ramp) - OPWA-3, , 2.5],

So its kind of there, but not quite. 

Reza

[CBMServices Web]

Yeah you have one too many loops in the code. Just reduce it to 2 loops and you should be fine.

Try this:

  let filteredData =[[]];

  for(var i = 0; i < resultArray.length; i++){

    for(var j = 0; j < filterArray.length; j=i++){

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/f39ed19f-06a4-4c27-82ff-abe8eb671a6cn%40googlegroups.com.

[Reza Azmi]

So far all good ....seems to work.

[[], [139.0, Neoh Ah Seng (WAGS-139 ), , 2.434, , , Air Kuning CC - TJC - Collection Center (with ramp) - OPWA-3, , 2.5], [139.0, Neoh Ah Seng (WAGS-139 ), , 2.434, , , Air Kuning CC - TJC - Collection Center (with ramp) - OPWA-3, , 3.08], [139.0, Neoh Ah Seng (WAGS-139 ), , 2.434, , , Air Kuning CC - TJC - Collection Center (with ramp) - OPWA-3, , 4.46], [139.0, Neoh Ah Seng (WAGS-139 ), etc 

The array is filtered.

But then, I have this one last round of code to print the result.

let resultLastRow = filteredData.length;

let resultLastCollumn = filteredData[2].length;

Logger.log(resultLastCollumn);

and the result is - 0.0

This triggers and error. 

0:10:42 PM Info 9.0

10:10:42 PM Error Exception: The number of columns in the data does not match the number of columns in the range. The data has 0 but the range has 9.

Trying to understand what went wrong here. 

Thanks

[CBMServices Web]

I would need to see your test data and rest of the code to tell you why it is not working. Can you put that in a spreadsheet and share?

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/4017ef26-ac05-4c66-854a-fcf221475dben%40googlegroups.com.

[Reza Azmi]

Oh cool - thanks. 

https://docs.google.com/spreadsheets/d/1xvBaQvFfF9tGkrliO5T3DMNVBr4BE9-EqvGYjYdr-bw/edit?usp=sharing

My full code:

// FILTER ARRAYS and RETURN RESULT

function filterArrayToDestination () {

// Provide the Source

const sourceID = "1xvBaQvFfF9tGkrliO5T3DMNVBr4BE9-EqvGYjYdr-bw";

const sourceTab = "MG_FFB";

// Open source

let sourceSheet = SpreadsheetApp.openById(sourceID).getSheetByName(sourceTab);

// Get Range

let sourceStartRow = 2;

let sourceStartCollumn = 1;

let sourceLastRow = sourceSheet.getLastRow();

let sourceLastCollumn = sourceSheet.getLastColumn();

let sourceValues = sourceSheet.getRange(sourceStartRow,sourceStartCollumn,sourceLastRow,sourceLastCollumn).getValues();

//Logger.log(sourceValues);

// Create the destination unpivot data

// Array location Member name = 1, buyer = 3, plot ID 7, combo area 9, 10 farm area, 12 stand

let resultArray = [];

let startPivotCollumnfromSourceValue = 14;

//loop through the source values and build the result array

for (var i = 1; i < sourceValues.length; i++){

for (var j = startPivotCollumnfromSourceValue; j < sourceValues[0].length; j++){

if(sourceValues[i][j] != ""){

resultArray.push([sourceValues[i][0],sourceValues[i][1],sourceValues[i][7],sourceValues[i][9],sourceValues[i][10],sourceValues[i][12],sourceValues[i][3],sourceValues[0][j],sourceValues[i][j]]);

//Logger.log(resultArray);

//

}

}

}

Logger.log(resultArray);

// Provide the Filter Array

// Get Filter Source

const filterID = "1xvBaQvFfF9tGkrliO5T3DMNVBr4BE9-EqvGYjYdr-bw";

const filterTab = "BIO_Register";

// Open source

let filterSheet = SpreadsheetApp.openById(filterID).getSheetByName(filterTab);

// Get Filter Array

let filterArray = filterSheet.getRange("C2:C178").getValues();

//let filterArray = [[1993.0], [10.0]];

// Filter the Arrays

let filteredData =[[]];

for(var i = 0; i < resultArray.length; i++){

for(var j = 0; j < filterArray.length; j=i++){

if(resultArray[i][0] == filterArray[j][0])

{

// Add the row to the filtered data

filteredData.push(resultArray[i]);

}

}

}

Logger.log(filteredData);

debugger;

// Array to hold the filtered data

//let filteredData = [];

//for(var i = 0; i < resultArray.length; i++){

// for (var j = 0; j < filterArray[0].length; j++){

//if(resultArray[i][0] == filterArray[j][0]) {

// Add the row to the filtered data

filteredData.push(resultArray[i]);

// };

//};

//};

//Logger.log(filteredData);

debugger;

if(filteredData.length !== 0){

//Logger.log(filteredData);

debugger;

// Paste into Destination sheet

// Connect to the Sheet

const destinationID = "1xvBaQvFfF9tGkrliO5T3DMNVBr4BE9-EqvGYjYdr-bw";

const destinationTab = "Filtered_Unpivot";

let destinationSheet = SpreadsheetApp.openById(destinationID).getSheetByName(destinationTab);

let destinationStartRow = 2;

let destinationCollumn = 1;

let resultLastRow = filteredData.length;

let resultLastCollumn = filteredData[2].length;

Logger.log(resultLastCollumn);

// Clear contents

destinationSheet.clearContents();

// Set header values to destination range

// Array location Member name = 1, buyer = 3, plot ID 7, combo area 9, 10 farm area, 12 stand

let headersArray = [["Member ID","Member Name","Plot ID","Combo Area","Farm Area","Stand/ha","Buyer","Date","FFB/month"]];

let headearStartRow = 1;

let headearStartCollumn = 1;

destinationSheet.getRange(headearStartRow, headearStartCollumn, headersArray.length, headersArray[0].length ).setValues(headersArray);

// Set Results values to destination range

destinationSheet.getRange(destinationStartRow, destinationCollumn,resultLastRow,resultLastCollumn).setValues(filteredData);

}

}

[CBMServices Web]

You would need to provide access to the spreadsheet for us to be able to see it. Read only access should be sufficient.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/d1d9d529-f9e9-48f8-b619-530a034b2efcn%40googlegroups.com.

[Reza Azmi]

Cool

Shared

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/tvsXCCeUMbk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFX%2Bz3V_5ecXoSq%2BhSmv8HMe5j6L5FyP1c%2BZ2JHKPQOCXc%3DE3Q%40mail.gmail.com.

--

Dr Reza Azmi
Executive Director & Founder

Connect to my professional network
LinkedIN Profile

Wild Asia: Promoting Change. Inspiring People. Engaging Businesses.
Wild Asia ORG | WA @ Facebook

Oil Palm | Build for Tomorrow | Academy

What's New from Wild Asia

• Latest Sustainable Palm Oil News from Wild Asia
• Listen about our work on this BBC Radio Podcast
• RSPO Credits now available from WAGS - Group Scheme for Small Producer
• Learn more about the "Small Producer Inclusivity and Resilience Alliance" (SPIRAL)