Filter a 2D array using another 1D array

Question

I am trying to use arrays to clean up a list of package tracking updates. The data is organised in 3 columns:

| Tracking No | Status | Detail |

The scripts first reads all the data from the sheet, then derives a list of tracking numbers which have been delivered. It then needs to use this array to remove all instances of this tracking number from the original array to create a new array. Finally, both arrays are written to 2 different sheet.

The code ran perfectly a few times, but now is failing now. Any help to find the fault is appreciated. The 1st two arrays are created without any issues, but the 3rd array is not created as expected, the tracking numbers which exist in the 2nd array are not being removed.

I am new to scripting, and most of this code is copied from the internet, so any other tips are welcome as well! I know it can be done by looping, but the dataset is large (15000 lines), and arrays seem to work faster than looping.

function identifydelivered2() {
  spreadsheet = SpreadsheetApp.getActive();
  sheet = spreadsheet.getSheetByName("Sheet1");
  targetsheet = spreadsheet.getSheetByName('Delivered')

  //find last row in delivered sheet
  var targlrow = targetsheet.getLastRow() + 1;

  //find last row in Sheet1
  var lastrow = sheet.getLastRow();

  var capturerange = [[]];

  //read all values from Sheet1
  capturerange = sheet.getRange(2, 1, lastrow, 3).getValues();

  //stores number of rows captured
  var clearlen = capturerange.length

  //creates a filtered 2D array of all the delivered rows
  let delivered = capturerange.filter(dataRow => dataRow[1] === 'Delivered' || dataRow[1] === 'delivered');

  //creates a 1D matrix of all tracking numbers which have been delivered
  let deliverednos = delivered.map(x => x[0]);

  //creates a filtered 2D array of numbers not in deliverednos array
  let intransit = capturerange.filter(dataRow => dataRow[0] != deliverednos);

 try{ 
   //write delivered rows to new sheet
   targetsheet.getRange(targlrow,1,delivered.length,3).setValues(delivered);

   //clear contents of main sheet
   sheet.getRange(2,1,clearlen,3).clearContent();

   //write in-transit rows to sheet1
   sheet.getRange(2,1,intransit.length,3).setValues(intransit);
   }
 catch(e){
   Browser.msgBox('No delivered packages found!')
   }

}

TheMaster · Accepted Answer · 2020-05-01 07:05:05Z

1

Use Set to create a set of deliverednos.

const deliverednos = new Set(delivered.map(x => x[0]));

//creates a filtered 2D array of numbers not in deliverednos array
const intransit = capturerange.filter(dataRow => !deliverednos.has(dataRow[0]));

answered May 1, 2020 at 7:05

TheMaster

51.6k7 gold badges76 silver badges102 bronze badges

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

Collectives™ on Stack Overflow

Filter a 2D array using another 1D array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related