Office Scripts - Loops

Question

I have a workbook that is only accessed via Excel Online, by several people. It has worksheets named after about 20 or so people (think username: JSmith, JDoe, etc.) and on those worksheets are tables with the same names (JSmith). There are some fairly complex formulas within the tables, and for some reason we are finding that the formulas will randomly change to refer to someone else's table. Since the formulas are only supposed to refer to their own table, there really should not be any reference to someone's username in any formula (e.g. =sumif(JSmith[column name]...) vs. sumif([column name]).

I want to write a script that I'll run each day via PowerAutomate that will do a find/replace all on each sheet for each person's username. I have a list of all of the usernames on a sheet named Ref, so I would need to loop through each sheet, then loop through each username. I have a rough idea of how I would do this in VBA but I'm at a loss in Office Scripts so any help would be greatly appreciated! I can upload a sample file if needed.

I've tried doing a record to see what the code might look like but it came out extremely manual and without including the loops, which are the key piece I'm missing and unsure how to put in.

Answer 1

• Assuming the list of names is the only column on the sheet named "Ref."

function main(workbook: ExcelScript.Workbook) {
    let refSheet = workbook.getWorksheet("Ref");
    // get name list on Ref sheet
    let nameList = refSheet.getUsedRange().getValues();
    // loop through name list
    nameList.forEach( cell => {
        let personName = cell[0].toString();
        console.log(personName)
        // get the sheet named as person name
        let personSheet = workbook.getWorksheet(personName);
        if(refSheet){
            // get the table (ListObject)
            let personTab = personSheet.getTable(personName);
            if(personTab){
                // replace the formulas
                personTab.getRange().replaceAll(personName, "", { completeMatch: false, matchCase: false });
            }
        }
    }
    )
}