How to filter pivot table using office scripts?

Question

How to filter a pivot table using office script?

• Date Filter
• Name filter

Answer 1

This is not the most best answer but it worked way.

Answer 2

get all the items not required from the pivot table using the cells or hierarchies.

uncheck all and check the required items.

(or)

get all the list of not_required items and remove them.

function not_required_items(workbook: ExcelScript.Workbook, Company: string[], PName: string) {
let sheet = workbook.getActiveWorksheet();
let PT = sheet.getPivotTable(PName);

//console.log(myString.replace(/(.*?)/g, ""));

/for (let i = 0; i < ; i++) {
console.log(PT.getRowHierarchy("Company").getPivotField("Company").getPivotItem(Company(i)).setVisible(false));
}/
//console.log(PT.getRowHierarchy("Company").getPivotField("Company").getPivotItem("AllianzGI").setVisible(true));
;

sheet.getRange("A1").select;
var PivotCount = sheet.getUsedRange().getRowCount();

var company_list = [" "];
var not_req_company = [""];
for (var i = 2; i < PivotCount; i++) {
var items = workbook.getActiveWorksheet().getRange("A" + i).getValue()

company_list.push(items.toString())

for (var j = 0; j < Company.length; j++) {
  if (items.toString() === Company[j].toString()) {
    company_list.pop();
    //console.log(items);
    //continue;
  }
  else {

  }
}
//console.log(items)

}
company_list.shift();

return company_list;
}

Answer 3

I tried but no luck!