Skip to main content

Notifications

Power Automate - Building Flows
Unanswered

Office script - get cell address by value

Posted on 27 Nov 2024 06:49:32 by 76
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!

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November 2024 Newsletter…

November 2024 Community Newsletter…

Community Update Oct 28…

Power Platform Community Update…

Tuesday Tip #7 Community Profile Tips…

Welcome to a brand new series, Tuesday Tips…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 143,129

#2
RandyHayes Profile Picture

RandyHayes 76,308

#3
Pstork1 Profile Picture

Pstork1 63,797

Leaderboard