Office script, filter color filled rows

[jsmith2094]

Hi All,



I'm trying to make an office script to use with power automate to filter any rows that have a fill color of yellow and then copy those filtered rows to a table on a different workbook.



when I use the record function in excel it records the filter but it doesn't seem to recognise to be able to filter on yellow fill.



Here is my sample code I'm trying to make:

function main(workbook: ExcelScript.Workbook) {
let sheet = workbook.getWorksheet('Sheet1');



let range = sheet.getRange("A1:A" + sheet.getUsedRange().getRowCount());



let interiorColors = range.getFormat().fill.load(["color"]).values;



let yellowColor = ExcelScript.Color.getColorByRgb(255, 255, 0);



let rowsToDelete: number[] = [];



for (let i = 0; i < interiorColors.length; i++) {
let cellColor = interiorColors[i].color;

if (cellColor !== yellowColor) {
rowsToDelete.push(i + 1);
}
}



if (rowsToDelete.length > 0) {
sheet.getRange("A" + rowsToDelete[0]).getResizedRange(rowsToDelete.length, 1).delete(ExcelScript.DeleteShiftDirection.up);
}
}

any help would be great

 

[ExcelToDAX]

Is this a table or is just a range in an Excel sheet?