Office script to change sheet tab color based on cell value

Question

I need an office script to work in a power automate flow for an excel workbook.

Workbook has multiple worksheets. Master sheet has 2 tables. There is a corresponding worksheet for each table row. The name of each worksheet will be in column A of each table. Want the script to find the worksheet that corresponds to that row and update its tab color to match the background colour of the cell in column G. It must do this for each row of both tables.

Have not tried anything as am still unfamiliar with office script

Answer 1

Please try.

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    let myTables = selectedSheet.getTables();
    myTables.forEach(tab => {
        let ColA = tab.getColumnById(1).getRangeBetweenHeaderAndTotal();
        let rowCount = ColA.getRowCount();
        for (let i = 0; i < rowCount; i++) {
            let shtName: String = ColA.getCell(i, 0).getText();
            // console.log(shtName)
            let dataSheet = workbook.getWorksheet(shtName.toString());
            if (dataSheet) {
                let tabColor = ColA.getCell(i, 6).getFormat().getFill().getColor();
                dataSheet.setTabColor(tabColor);
            }
        }
    })
}

Microsoft documentation:

setTabColor(tabColor)