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);
}
}
}
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; }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
.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.