Office script in Excel instead of VBA

[ausswe]

Hi, I'm trying to get my head around Office Scripts in Excel in order to use them instead of VBA in a workbook that is shared with a large number of colleagues who don't have Office 365 Desktop - they access the files in Teams/SharePoint Web instead (hence why VBA isn't an option).

I have created an Excel file and saved it in SharePoint and then tried to use Copilot to create an Office Script that opens the workbook as read-only (except for some users) and also closing the file without offering to save the changes.

I added the script to the workbook and saved it in the same folder in SharePoint, but it doesn't seem to work so I'm wondering what I'm doing wrong.

Anyone who might have some insight?

This is the script:

function main(workbook: ExcelScript.Workbook) {
    // List of users who are exempt from the script
    const exemptUsers = ["user1@email.com", "user2@email.com"];

    // Get the current user's email
    const currentUser = workbook.getApplication().getUserEmail();

    // Check if the current user is exempt
    if (!exemptUsers.includes(currentUser)) {
        // Set the workbook to read-only mode
        workbook.getApplication().setWorkbookReadOnly(true);

        // Close the workbook without saving
        workbook.close(ExcelScript.CloseBehavior.discard);
    }
}

 

[6StringJazzer]

What is causing your code to execute?

I do not have access to a version of Excel that supports Office Script, but my understanding is that it does not support event handling. That is, there is no way to write code that will execute when the file is opened. I have a feeling that you are the code you have written to run when the file opens, but it will only run if you explicitly run function main.

 

[ausswe]

What is causing your code to execute?

I do not have access to a version of Excel that supports Office Script, but my understanding is that it does not support event handling. That is, there is no way to write code that will execute when the file is opened. I have a feeling that you are the code you have written to run when the file opens, but it will only run if you explicitly run function main.

I think you are correct! After some searching I think the only way to potentially achieve it would be to also involve Power Automate or similar.

Thanks for the input!

 

[6StringJazzer]

I missed a word but I think you got it anyway.

I have a feeling that you are expecting the code you have written to run when the file opens

I have not used Power Automate but I think that's the kind of thing it's meant for.