In the following example code, how can I filter out only those rows of "Sell" in the "Status" element and more than 30% in the "Profit" element from the "data" variable, using the "criteria" variable? Can anyone help me out finish the code? I used to use Google Sheets filter functions, SpreadsheetApp.new filter criteria().whenNumber..., but it took so long (5 minutes) to refresh my Google Sheets, as my actual application has 3000 rows and 140 columns to filter and required so many calls. Rather I learn how to do filtering operations within the JavaScript level, expecting that it would be much faster. Then I can send the filtered data back to Google Sheets. Thank you for any help!

function test() {

  var data = [['Product', 'Status', 'Price', 'Profit'],
  ['Apple', 'Hold', 10, '10%'],
  ['Mango', 'Sell', 20, '20%'],
  ['Orange', 'Buy', 30, '30%'],
  ['Juice', 'Sell', 40, '40%'],
  ['Coffee', 'Sell', 50, '50%']];
  data.splice(0, 1);  // Remove header

  var criteria = ['', 'Sell', '', '>30%'];
  var filteredData = data;

  for (var i = 0; i < criteria.length;   i) {
    if (criteria[i]) {
      filteredData = filteredData.filter(???);
      console.log(filteredData);
    }
  }
}

CodePudding user response：

You could iterate all criteria along with their column values and check the value or calculate the percent value.

const
    criteria = ['', 'Sell', '', '>30%'],
    data = [['Product', 'Status', 'Price', 'Profit'], ['Apple', 'Hold', 10, '10%'], ['Mango', 'Sell', 20, '20%'], ['Orange', 'Buy', 30, '30%'], ['Juice', 'Sell', 40, '40%'], ['Coffee', 'Sell', 50, '50%']],
    result = data
        .slice(1)
        .filter(a => criteria.every((c, i) => {
            if (!c || c === a[i]) return true;
            if (c.slice(-1) === '%') return eval(a[i].slice(0, -1)   c.slice(0, -1));
        }));
        
console.log(result);

.as-console-wrapper { max-height: 100% !important; top: 0; }

CodePudding user response：

function test() {
  var data = [['Product', 'Status', 'Price', 'Profit'],
  ['Apple', 'Hold', 10, '10%'],
  ['Mango', 'Sell', 20, '20%'],
  ['Orange', 'Buy', 30, '30%'],
  ['Juice', 'Sell', 40, '40%'],
  ['Coffee', 'Sell', 50, '50%']];
  data.shift();
  let fdata = data.filter(r => r[1] == 'Sell' && parseInt(r[3]) > 30);
  Logger.log(fdata.join('\n'));
}

1:45:07 PM  Notice  Execution started
1:45:08 PM  Info    Juice,Sell,40,40%
Coffee,Sell,50,50%
1:45:08 PM  Notice  Execution completed

CodePudding user response：

.filter, .map, .reduce, .forEach and other similar functions,
have some options regarding the parameters you can pass, you can read more about it here.

For this use-case, you actually don't need criteria at all,
you can simply do:

function test() {

  var data = [['Product', 'Status', 'Price', 'Profit'],
  ['Apple', 'Hold', 10, '10%'],
  ['Mango', 'Sell', 20, '20%'],
  ['Orange', 'Buy', 30, '30%'],
  ['Juice', 'Sell', 40, '40%'],
  ['Coffee', 'Sell', 50, '50%']];
  data.splice(0, 1);  // Remove header

  var filteredData = data;
  filteredData = filteredData.filter(currentData => {
      if (currentData[1] === 'Sell' && parseInt(currentData[3]) > 30) {
         return currentData;
      }
     });
  }

After this is done, filteredData gets filled with 'Juice' and 'Coffee' arrays in this case, and then you can do whatever you need with it.