"You cannot perform the requested operation.", code: "AccessDenied", method: "addTable", line: 9}

[BradynHin]

The following is my code in excel under the automate tab which when ran in a workbook with a sheet called "STU Chassis" regardless of the data contained gives this error {message: "You cannot perform the requested operation.", code: "AccessDenied", type: "Workbook", method: "addTable", line: 9}

How can I fix this?

function main(workbook: ExcelScript.Workbook) {

  try {

    //Sets the Active Worksheet to the chassis worksheet in the workbook

    let stuChassis = workbook.getWorksheet("STU Chassis");

    //Get used range of previously mentioned worksheet 

    let stuChassisUsed = stuChassis.getUsedRange()

    console.log(stuChassisUsed)

    // Add a new table

    let stuchassisNew = workbook.addTable(stuChassisUsed, true);

    stuchassisNew.setName("STUChassisTable")

  } catch (error) {

    console.log("There are already tables present in STU chassis");

    console.log(error);

  }

return;

}

[#2]

@BradynHin 

Can't reproduce such error, even on protected sheet. If you run on new workbook with such named sheet, do you have an error?

Just in case, slightly modified the script

function main(workbook: ExcelScript.Workbook) {

try {
  const stuChassis = workbook
    .getWorksheet("STU Chassis")

  const stuChassisUsed = stuChassis
    .getUsedRange()

  stuChassis
    .addTable(stuChassisUsed, true)
    .setName("STUChassisTable")

}

  catch (error) {
    console.log("There are already tables present in STU chassis");
    console.log(error);
  }

  return

}

[#3]

@Sergei Baklan Yes, even in a new workbook and using your revised code, I get the same error. Could it be a permissions error?

[#4]

@BradynHin 

That's usually workbook/sheet protection, but in that case I have another error

If not protection have no idea what it could be.

[#5]

UPDATE: This appears to be a permission or software issue. I ran the sample program "Create, Sort, and Format a Table" and get the same error even on untouched workbooks. I have even tried with different accounts and different machines on my network with the same result. The strange thing is this all worked last week before Friday when I started getting the error.

[#6]

@BradynHin did you work out a way around it?

My problem comes into play where half my script will work but it will have a "You cannot perform the requested operation." for a setValue operation on a single excel cell but only through power automate. Originally I thought it was something to do with something parsing through with my string variable but it doesn't seem to be that.

[#7]

@DanDunne 

I figured it out. For some reason workbook.getProtection().unprotect(); wasn't working (to unprotect the whole workbook for editing).

As a workaround  I had to put in workbook.getWorksheet("<your sheet here>").getProtection().unprotect(); for each section I wanted to edit. Hopefully this gets addressed by Microsoft at some point in the future. This might also only be a problem for power automate to Officescripts as opposed to inherent to officescripts (which was why it was hard to track down the error).

This was also on an .xlsm file so things may still be okay on the .xlsx if doing a run script from power automate.

Anyway I hope I save people from the headache it caused me!