Preventing Simultaneous Execution of Scripts in Excel Online

Question

How can I ensure that only one script runs at a time in Excel Online?

I'm in an office with 9 other people that use my Excel Online workbook to track time on projects.

I have a workbook with 10 sheets, 9 of the sheets has a button containing a script. The 10th sheet retrieves information from the other 9 sheets when their respective buttons are clicked. Currently, when a script is running, another user on another sheet can start their script if their button is pressed, which can prevent one of the scripts from running or finishing. Each script takes about 15-20 seconds to run.

How do I disable other buttons or scripts from executing until the current one is finished to prevent these crashes?

I also want to gray-out all 9 buttons to indicate that a script is running and prevent accidental clicking until the script is complete, and all 9 buttons to return to Red when its finished running.

I have added a warning message to each sheet below their button to indicate that a script is running, but this does not prevent users from clicking their button. Thank you

Answer 1

Hello there,

Preventing simultaneous execution of scripts in Excel Online can be challenging since Excel Online does not provide native locking or concurrency control mechanisms. However, you can employ some workarounds to minimize the likelihood of simultaneous script execution and potential conflicts. Here are a few options:

User notification and collaboration: If multiple users are accessing the same Excel Online workbook, you can implement a communication mechanism to notify other users when a script is being executed. This can be done through chat, comments, or a shared document to ensure users are aware and avoid simultaneous execution.

Shared lock mechanism: Implement a shared lock mechanism using an external data source or file. For example, you could create a separate file in OneDrive or SharePoint that acts as a lock file. Before executing a script, a user checks if the lock file exists or is currently held. If it is, the user waits until the lock is released. Once the script completes, the lock is released for other users to access.

Script scheduling: If the script execution is not time-sensitive, consider implementing a scheduling system. Users can submit their scripts to a queue, and a central service or script can pick up and execute them one at a time. This ensures sequential execution and minimizes conflicts.

Splitting workbooks: If possible, split the workbook into multiple smaller workbooks, each with its own set of scripts. This approach limits the chances of conflicts arising from simultaneous execution in the same workbook.

I used AI provided by ChatGPT to formulate part of this response. I have verified that the information is accurate before sharing it with you.