CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Emmanu
Emmanu

Reputation: 53

How to transform Google sheet filter function in apps script

I have a function filter in a cell, how can I transform it in an apps script function with parameters like range, condition1, condition2?

This is the filter function:

=FILTER(Master!B:C; month(Master!B:B) = 1; Master!A:A = true)

Upvotes: 1

Views: 91

Answers (1)

Tanaike
Tanaike

Reputation: 201378

I believe your goal is as follows.

  • You want to convert the formula of =FILTER(Master!B:C; month(Master!B:B) = 1; Master!A:A = true) to Google Apps Script.

In this case, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet, and save the script. And, please set the source and destination sheet names.

const filterValues = v => v.filter(([a, b]) => a === true && b.getMonth() == 0).map(([, ...v]) => v);

// Please run this script.
function myFunction() {
  // Retrieve values from the source sheet and filter values.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = ss.getSheetByName("Master"); // Please set source sheet name.
  const values = srcSheet.getRange("A1:C" + srcSheet.getLastRow()).getValues();
  const result = filterValues(values);

  console.log(result); // You can see the filtered values in the log.

  // Put filtered values to the destination sheet.
  const dstSheet = ss.getSheetByName("Sheet1"); // Please set destination sheet name.
  dstSheet.getRange(1, 1, result.length, result[0].length).setValues(result);
}

  • When this script is run, the same values with the above formula are retrieved and put into the destination sheet.

  • In this sample, you can also use filterValues as a custom function like =filterValues(Master!A1:C).

Note:

  • About putting the filtered values to the destination sheet, this is a sample script. So, please modify this for your actual situation.

References:

Upvotes: 1

Related Questions