Forum Discussion
Solved
Using Record script in Excel to filter rows in excel across multiple tabs
Hello,
Trying to filter multiple sheets based on column A using the record script in the automate tab in Excel. I get the error below. Same error if I test on basic test of a 3x3 table. Also include the generated code below
Line 6: AutoFilter apply: The argument is invalid or missing or has an incorrect format.
Doing this I have to do this every month for ~30 tabs. Apologies for my lack of knowledge in this field! Any help greatly appreciated!
function main(workbook: ExcelScript.Workbook) {
let sheet1 = workbook.getWorksheet("Sheet1");
// Toggle auto filter on sheet2
sheet1.getAutoFilter().apply(sheet1.getRange("A:A"));
// Apply values filter on sheet1
sheet1.getAutoFilter().apply(sheet1.getAutoFilter().getRange(), 1, { filterOn: ExcelScript.FilterOn.values, values: ["OK"] });
let sheet2 = workbook.getWorksheet("Sheet2");
// Toggle auto filter on sheet2
sheet2.getAutoFilter().apply(sheet2.getRange("A:A"));
// Apply values filter on sheet2
sheet2.getAutoFilter().apply(sheet2.getAutoFilter().getRange(), 1, { filterOn: ExcelScript.FilterOn.values, values: ["OK"] });
let sheet3 = workbook.getWorksheet("Sheet3");
// Toggle auto filter on sheet3
sheet3.getAutoFilter().apply(sheet3.getRange("A:A"));
// Apply values filter on sheet3
sheet3.getAutoFilter().apply(sheet3.getAutoFilter().getRange(), 1, { filterOn: ExcelScript.FilterOn.values, values: ["OK"] });
}
You toggle autofilter on column A, i.e. on the range which has only one column. Next you apply filter to column 1. No such in the range, columns are numbered starting from 0.
Try here to change 1 on 0
sheet1.getAutoFilter().apply(sheet1.getAutoFilter().getRange(), 0, { filterOn: ExcelScript.FilterOn.values, values: ["OK"] });
You may iterate the sheets if they have the same structure. Let assume each worksheet has data like
We would like to filter on OK in first column keeping second row as headers for all sheets from second to 29. Don't forget numbering is always from zero, thus first worksheet has index 0, second one - 1, etc. Script could be like
function main(workbook: ExcelScript.Workbook) { const sheets = workbook .getWorksheets() .slice(1,29) for (let sheet of sheets) { let range = sheet.getRange("A2:A1000") let filter = sheet.getAutoFilter() let criteria: ExcelScript.FilterCriteria = { filterOn: ExcelScript.FilterOn.values, values: ["OK"] }; filter.apply(range, 0, criteria) } }You toggle autofilter on column A, i.e. on the range which has only one column. Next you apply filter to column 1. No such in the range, columns are numbered starting from 0.
Try here to change 1 on 0
sheet1.getAutoFilter().apply(sheet1.getAutoFilter().getRange(), 0, { filterOn: ExcelScript.FilterOn.values, values: ["OK"] });
- thank you this works and thank you for the follow up explanation Sergei. Really useful!
AndresAlfT , you are welcome
