Power Platform Community Forum Thread Details

Office script - get cell address by value

I'm new to Office Scripts and I'm working on integrating Power Automate, SharePoint, and Excel using Office Scripts. I'm hoping you can lend me some guidance.

My goal is this: when a user changes the status of a SharePoint list item to "Completed," a Power Automate flow will trigger, passing the item ID to an Office Script. The Office Script should then search for that item ID within a specific Excel table and return the cell address (e.g., "A4" for ID 71) where the ID is located.

1. I've tried the find() function but it can only search for string. Is there any function can find number (integer)?

function main(workbook: ExcelScript.Workbook) {
    let ws1 = workbook.getActiveWorksheet();
    let range1 = ws1.getUsedRange();

    range1.find(80, {
        completeMatch: true, 
        matchCase: false,
    })
}

2. I also gave a thought of getting index from table array and turn it into address. But not sure how to write the script?

function main(workbook: ExcelScript.Workbook) {
    let ws1 = workbook.getActiveWorksheet();
    let table1 = ws1.getTable("Completed");

    const table1address = table1.getRangeBetweenHeaderAndTotal().getColumn(0).getValues();

    console.log(table1address.flat())

    console.log(table1address.findIndex(e => e[1] == 70))
}

Thank you very much for your help!

Categories:

Business Process Flows

Quick Links