Range delete: You cannot perform the requested operation. Error

Question

Range delete: You cannot perform the requested operation. Error

Andriii 25

Hi, I have an Office Script that was working fine for several month. From 1 Jun it stopped working giving me an error.

Range delete: You cannot perform the requested operation.

The script was deleting all rows after a certain one.

There was the same error at another part of the script, but I've fixed it with

  workbook.getProtection().unprotect()

but that fix does not work here:

let balanceSheetRow = cell.getRowIndex();

  
  for (let i = rowCount - 1; i >= balanceSheetRow + 1; i--) {

    usedRange.getCell(i, 0).getEntireRow().delete(ExcelScript.DeleteShiftDirection.up)

  }

Garold Anders 5 Reputation points

2023-06-02T18:25:04.68+00:00

Im having the same issue with my script

Andriii 25

I've solved that error by unprotecting the range with


 for (let i = rowCount - 1; i >= balanceSheetRow + 1; i--) {


    let rangeToDelete = usedRange.getCell(i, 0).getEntireRow();
    rangeToDelete.getWorksheet().getProtection().unprotect(); // Unprotect the worksheet
    rangeToDelete.delete(ExcelScript.DeleteShiftDirection.up);

  }

but got more errors:

You cannot perform the requested operation.

Yseimur 20 Reputation points

2023-06-05T03:43:01.9566667+00:00
Similar here. My scripts worked fine for few months, but suddenly not working.

The first problem I got is with the method getAutoFilter().clearCriteria(). (Starting from 1/Jun)

Then last Saturday (4/Jun), anther method range.insert() also return the same error "cannot perform the request operation".

I tried create a new file to make sure it is clean, record a new script and run it, and still got the same error...

function main(workbook: ExcelScript.Workbook) { let selectedSheet = workbook.getActiveWorksheet(); // Insert at range 2:2 on selectedSheet, move existing cells down selectedSheet.getRange("2:2").insert(ExcelScript.InsertShiftDirection.down); }

The same for code like

selectedSheet.getAutoFilter().clearCriteria();

Can anyone share your idea? Thank you.

2 answers

Your answer

Garold Anders 5 Reputation points

2023-06-02T18:25:04.68+00:00

Im having the same issue with my script
Andriii 25 Reputation points

2023-06-02T19:02:44.0166667+00:00

I've solved that error by unprotecting the range with

for (let i = rowCount - 1; i >= balanceSheetRow + 1; i--) { let rangeToDelete = usedRange.getCell(i, 0).getEntireRow(); rangeToDelete.getWorksheet().getProtection().unprotect(); // Unprotect the worksheet rangeToDelete.delete(ExcelScript.DeleteShiftDirection.up); }

but got more errors:

You cannot perform the requested operation.
Yseimur 20 Reputation points

2023-06-05T03:43:01.9566667+00:00

Similar here. My scripts worked fine for few months, but suddenly not working.

The first problem I got is with the method getAutoFilter().clearCriteria(). (Starting from 1/Jun)

Then last Saturday (4/Jun), anther method range.insert() also return the same error "cannot perform the request operation".

I tried create a new file to make sure it is clean, record a new script and run it, and still got the same error...

function main(workbook: ExcelScript.Workbook) { let selectedSheet = workbook.getActiveWorksheet(); // Insert at range 2:2 on selectedSheet, move existing cells down selectedSheet.getRange("2:2").insert(ExcelScript.InsertShiftDirection.down); }

The same for code like

selectedSheet.getAutoFilter().clearCriteria();

Can anyone share your idea? Thank you.

Answer 1

Andriii 25

try this:

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    
    // Unprotect range A1:B10 on selectedSheet
    let rangeToUnprotect = selectedSheet.getRange("A1:B10");
    rangeToUnprotect.unprotect();
    
    // Insert at range 2:2 on selectedSheet, move existing cells down
    selectedSheet.getRange("2:2").insert(ExcelScript.InsertShiftDirection.down);
    
    if (selectedSheet.hasAutoFilter()) {
        selectedSheet.getAutoFilter().remove();
    }
}

Yseimur 20 Reputation points

2023-06-06T04:11:16.3633333+00:00
Hi Andrill,

Thanks for reply. You are right! Although there is no protection on the spreadsheet, when I add the code:

selectedSheet.getProtection().unprotect();

It is possible to insert new row!

It also work for function clearCriteria...

So now Microsoft require extra "unprotect" before perform some actions, even though it is not protected at all.

Anyway. Thank you again!
Garold Anders 5 Reputation points

2023-06-06T16:58:21.39+00:00

I wish this worked for me all I want to do is add a table but the unprotect wont work

Answer 2

Tyler Kortmeyer 5

https://learn.microsoft.com/en-us/answers/questions/1295289/broken-excel-script-please-help-me-troubleshoot

This other question posted seems relevant to the issues discussed here. According to a Microsoft employee who responded, the issue has been identified today and a fix will be coming in the next few days. I would suggest following that question for further updates.

Share via

Range delete: You cannot perform the requested operation. Error

2 answers

Your answer