ExcelScript fails on webapp, succeeds on desktop

Question

Last year I wrote a simple script to keep a locked sheet protected but allow users to insert new rows into a table when they press the designated button. It always succeeded with few errors, normally these would be caused if the API call failed or if the user provided incorrect parameters. Now it will consistently say "ERROR Line ##: Worksheet getProtection: Write Operation is not supported for Office when a modal dialog is open." and it only does so for the webapp/browser environment, with the desktop app never running into the issue.

Do I need to redo how I declare the variables in the function or is there some method I missed that is required now? I don't understand why it would disallow writing to the file just because a dialog box it calls is asking for parameters.

I tried modifying my code to use different worksheetProtection methods, namely pauseProtection and resumeProtection, and it didn't work. I also tried nesting the unprotect/pauseProtection in other methods with no improvement.

Here is my original code that I confirmed still runs fine on desktop minutes before this post was made:

function main(workbook: ExcelScript.Workbook, starting_row: number, quantity_newRows: number) {
    let selectedSheet = workbook.getWorksheet("Schedule");
    console.log(quantity_newRows + starting_row);
    // Unprotect sheet Schedule
    selectedSheet.getProtection().unprotect("1234");
    let schedule = workbook.getTable("Schedule");
    let rowAdd = starting_row - workbook.getTable("Schedule").getHeaderRowRange().getRowIndex() - 2;

    // Insert row at index 10 into table schedule
    for (let counter = 0; counter < quantity_newRows; counter++) {
        schedule.addRow(rowAdd);
    }

    selectedSheet.getRange().getFormat().autofitRows();
    selectedSheet.getProtection().protect({ allowAutoFilter: true, allowDeleteColumns: false, allowDeleteRows: false,
        allowEditObjects: false, allowEditScenarios: false, allowFormatCells: false,
        allowFormatColumns: true, allowFormatRows: true, allowInsertColumns: false,
        allowInsertHyperlinks: false, allowInsertRows: true, allowPivotTables: false,
        allowSort: false, selectionMode: ExcelScript.ProtectionSelectionMode.normal }, "1234");
}

Hi Daniel - thanks for reporting this! I've been able to reproduce the issue - based on other reports, it does seem like a recent change. I've flagged this for our team to look into (internal tracking: #9762200). In the meantime, as suggested below, setting a delay is the best workaround. Sorry for the inconvenience! — Michelle Ran
– Michelle Ran, Commented Jan 31 at 22:53

Michal · Accepted Answer · 2025-01-28 02:23:10Z

0

It looks like the online version of Excel tries to get ahead of itself and tries to execute the line of code with unprotect, but it is too slow and hasn't closed the dialog box yet. Pausing the script for a second or two solves the problem:

    function main(workbook: ExcelScript.Workbook, starting_row: number, quantity: number) {
        pause(1);
        insertRows(workbook, starting_row, quantity);
    }
    
    function insertRows(workbook: ExcelScript.Workbook, starting_row: number, quantity_newRows: number) {
        let selectedSheet = workbook.getWorksheet("Schedule");
        // Unprotect sheet Schedule
        selectedSheet.getProtection().unprotect("1234");
        let schedule = workbook.getTable("Schedule");
        let rowAdd = starting_row - workbook.getTable("Schedule").getHeaderRowRange().getRowIndex() - 2;
    
        // Insert row at index 10 into table schedule
        for (let counter = 0; counter < quantity_newRows; counter++) {
            schedule.addRow(rowAdd);
        }
    
        selectedSheet.getRange().getFormat().autofitRows();
        selectedSheet.getProtection().protect({
            allowAutoFilter: true, allowDeleteColumns: false, allowDeleteRows: false,
            allowEditObjects: false, allowEditScenarios: false, allowFormatCells: false,
            allowFormatColumns: true, allowFormatRows: true, allowInsertColumns: false,
            allowInsertHyperlinks: false, allowInsertRows: true, allowPivotTables: false,
            allowSort: false, selectionMode: ExcelScript.ProtectionSelectionMode.normal
        }, "1234");
    }
    
    function pause(seconds: number) {
        const waitUntil = new Date().getTime() + seconds * 1000;
        while (new Date().getTime() < waitUntil) {
        }
    }

edited Jan 28 at 2:23

answered Jan 27 at 23:20

Michal

7,6744 gold badges30 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Daniel Rodriguez Jan 28 at 13:18

Thank you! I had been suspecting that was the problem but could not structure my script properly to execute subfunctions. I can't believe something so simple was so broken after nearly a year of working. I guess I really need to learn how to think like a programmer.

Collectives™ on Stack Overflow

ExcelScript fails on webapp, succeeds on desktop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related