Move rows across tables additional filters about office-scripts-docs

[AlexJerabek]

Hi @JohnHotham,

Thank you for the questions. I'm not quite sure I understand the scenario you're trying to accomplish, but I'll try my best.

First, you can't call a script from another script. You can have your script call multiple functions, like this:

 function main(workbook: ExcelScript.Workbook) {
    const selectedSheet = workbook.getActiveWorksheet();
    for (let i = 0; i < 10; i++) {
        for (let j = 0; j <= i; j++) {
            highlightCell(selectedSheet.getRangeByIndexes(i, j, 1, 1));
        }
    }
}

function highlightCell(cell: ExcelScript.Range) {
    cell.getFormat().getFill().setColor("yellow");
}

You could also call multiple scripts from Power Automate. This scenario shows a flow that calls multiple scripts on the same workbook (granted, there are intermediate steps in the sample).

As for the filter question, if you want to apply a value-based filter, you'll applyValuesFilter on the Filter object of the TableColumn. In this example, the filter will only show rows when the value in the Description Rating is "Good" or "Fine".

function main(workbook: ExcelScript.Workbook) {
    const coverageWorksheet = workbook.getWorksheet("Coverage");
    const coverageTable = coverageWorksheet.getTable("CoverageTable");
    const descriptionColumn = coverageTable.getColumnByName("Description Rating");
    const descriptionSet = columnToSet(descriptionColumn);
    descriptionColumn.getFilter().applyValuesFilter(["Good", "Fine"]);
}

If you want to make a filter that keeps everything except a certain value, you'll need to add every other value to that filter array. In this example, we'll keep every row that doesn't have a description rating of "Poor". We'll use a helper function that converts all the values in a column to a set (effectively turning a 2D-array with duplicates into a 1D-array with unique values).

function main(workbook: ExcelScript.Workbook) {
    const coverageWorksheet = workbook.getWorksheet("Coverage");
    const coverageTable = coverageWorksheet.getTable("CoverageTable");
    const descriptionColumn = coverageTable.getColumnByName("Description Rating");
    const descriptionSet = columnToSet(descriptionColumn);
    descriptionColumn.getFilter().applyValuesFilter(descriptionSet.filter((value) => {
        return value !== "Poor";
    }));
}

/**
 * Convert a column into a set so it only contains unique values.
 */
function columnToSet(column: ExcelScript.TableColumn): string[] {
    const range = column.getRangeBetweenHeaderAndTotal().getValues() as string[][];
    const columnSet: string[] = [];
    range.forEach((value) => {
        if (!columnSet.includes(value[0])) {
            columnSet.push(value[0]);
        }
    });

    return columnSet;
}

I hope that helps. Please let me know if I missed the mark.

[JohnHotham]

Hi @AlexJerabek
Thanks for the assist, it is appreciated, I'll look at integrating them one at a time and see which works best. However, just looking at them, I'm reckoning the second one is a much closer fit than the others. Bear with me.

[AlexJerabek]

Sounds good. Please let me know if that helps. If so, I'll work on creating a sample so others can benefit.