Filter a table - copy the filtered results

[JohnR471]

Type of issue

Code doesn't work

Feedback

On Page https://learn.microsoft.com/en-us/office/dev/scripts/resources/samples/table-samples, under the heading "Filter a table" there is a "Tip" box which offers the following tip "Copy the filtered information across the workbook by using Range.copyFrom. Add the following line to the end of the script to create a new worksheet with the filtered data." (my emphasis) with the following Typescript code workbook.addWorksheet().getRange("A1").copyFrom(table.getRange()); . This code, however, does not copy only the filtered set of table data, rather it copies the entire (un-filtered) table data. It would be good if the code were corrected to achieve the stated objective.

Page URL

https://learn.microsoft.com/en-us/office/dev/scripts/resources/samples/table-samples

Content source URL

https://github.com/OfficeDev/office-scripts-docs/blob/main/docs/resources/samples/table-samples.md

Author

@o365devx

Document Id

b772ec99-a2e5-eb1c-8b87-2179f24664f4

[alison-mk]

Hi @JohnR471, thank you for bringing this issue to our attention! I'm working on a solution. I'll report back here once I have a solution.

Thanks,
Alison

[alison-mk]

Hi @JohnR471,

To copy the filtered table data to a new worksheet, you can use the following code:

    const tableData = table.getRangeBetweenHeaderAndTotal();

    let x: ExcelScript.Range[] = [];
    const stationData = stationColumn.getRangeBetweenHeaderAndTotal();
    const stationRowCount = stationData.getRowCount();

    for (let i = 0; i < stationRowCount; i++) {
      if (!stationData.getRow(i).getRowHidden()) {
        x.push(tableData.getRow(i));
      }
    }

    x.unshift(table.getHeaderRowRange());

    const newSheet = workbook.addWorksheet();
    let rowNumber = 0;

    x.forEach((row) => {
      newSheet.getRangeByIndexes(rowNumber, 0, 1, row.getColumnCount()).copyFrom(row);
      rowNumber++;
    });

I'm still working on an update for the article, so I'll leave this issue open in the interim.