Script run with error (Range delete)

[dasa7]

Hello,

I have been running the same script for a few weeks, but since today it is running with the following error:

Line 4: Range delete: You cannot perform the requested operation.

When I try to run in Excel online, there is no issue with the script at all. Any advices?

Thank you.

There is the script:

function main(workbook: ExcelScript.Workbook) {

  let selectedSheet = workbook.getActiveWorksheet();

  // Delete range B:B on selectedSheet

  selectedSheet.getRange("B:B").delete(ExcelScript.DeleteShiftDirection.left);

  // Delete range F:F on selectedSheet

  selectedSheet.getRange("F:F").delete(ExcelScript.DeleteShiftDirection.left);

  // Delete range G:G on selectedSheet

  selectedSheet.getRange("G:G").delete(ExcelScript.DeleteShiftDirection.left);

  // Auto fit the columns of range B:B on selectedSheet

  selectedSheet.getRange("B:B").getFormat().autofitColumns();

  // Auto fit the columns of range F:F on selectedSheet

  selectedSheet.getRange("F:F").getFormat().autofitColumns();

  // Auto fit the columns of range G:G on selectedSheet

  selectedSheet.getRange("G:G").getFormat().autofitColumns();

  // Set number format for range C:C on selectedSheet

  selectedSheet.getRange("C:C").setNumberFormatLocal("0");

  // Set horizontal alignment to ExcelScript.HorizontalAlignment.general for all cells on selectedSheet

  selectedSheet.getRange().getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.general);

  selectedSheet.getRange().getFormat().setIndentLevel(0);

  // Set vertical alignment to ExcelScript.VerticalAlignment.center for all cells on selectedSheet

  selectedSheet.getRange().getFormat().setVerticalAlignment(ExcelScript.VerticalAlignment.center);

  selectedSheet.getRange().getFormat().setIndentLevel(0);

  // Set wrap text to false for all cells on selectedSheet

  selectedSheet.getRange().getFormat().setWrapText(false);

  // Set text orientation to 0 for all cells on selectedSheet

  selectedSheet.getRange().getFormat().setTextOrientation(0);

  // Indent set to 0 for all cells on selectedSheet

  selectedSheet.getRange().getFormat().setIndentLevel(0);

  // Set horizontal alignment to ExcelScript.HorizontalAlignment.center for all cells on selectedSheet

  selectedSheet.getRange().getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);

  selectedSheet.getRange().getFormat().setIndentLevel(0);

  // Set vertical alignment to ExcelScript.VerticalAlignment.center for all cells on selectedSheet

  selectedSheet.getRange().getFormat().setVerticalAlignment(ExcelScript.VerticalAlignment.center);

  selectedSheet.getRange().getFormat().setIndentLevel(0);

  // Set wrap text to false for all cells on selectedSheet

  selectedSheet.getRange().getFormat().setWrapText(false);

  // Set text orientation to 0 for all cells on selectedSheet

  selectedSheet.getRange().getFormat().setTextOrientation(0);

  // Indent set to 0 for all cells on selectedSheet

  selectedSheet.getRange().getFormat().setIndentLevel(0);

  // Set height of row(s) at all cells on selectedSheet to 20

  selectedSheet.getRange().getFormat().setRowHeight(20);

  // Set width of column(s) at range E:E on selectedSheet to 12.57

  selectedSheet.getRange("E:E").getFormat().setColumnWidth(12.57);

  // Set border for all cells on selectedSheet

  selectedSheet.getRange().getFormat().getRangeBorder(ExcelScript.BorderIndex.diagonalDown).setStyle(ExcelScript.BorderLineStyle.none);

  // Set border for all cells on selectedSheet

  selectedSheet.getRange().getFormat().getRangeBorder(ExcelScript.BorderIndex.diagonalUp).setStyle(ExcelScript.BorderLineStyle.none);

  // Set border for all cells on selectedSheet

  selectedSheet.getRange().getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeLeft).setStyle(ExcelScript.BorderLineStyle.continuous);

  selectedSheet.getRange().getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeLeft).setWeight(ExcelScript.BorderWeight.thin);

  // Set border for all cells on selectedSheet

  selectedSheet.getRange().getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeTop).setStyle(ExcelScript.BorderLineStyle.continuous);

  selectedSheet.getRange().getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeTop).setWeight(ExcelScript.BorderWeight.thin);

  // Set border for all cells on selectedSheet

  selectedSheet.getRange().getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeBottom).setStyle(ExcelScript.BorderLineStyle.continuous);

  selectedSheet.getRange().getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeBottom).setWeight(ExcelScript.BorderWeight.thin);

  // Set border for all cells on selectedSheet

  selectedSheet.getRange().getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeRight).setStyle(ExcelScript.BorderLineStyle.continuous);

  selectedSheet.getRange().getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeRight).setWeight(ExcelScript.BorderWeight.thin);

  // Set border for all cells on selectedSheet

  selectedSheet.getRange().getFormat().getRangeBorder(ExcelScript.BorderIndex.insideVertical).setStyle(ExcelScript.BorderLineStyle.continuous);

  selectedSheet.getRange().getFormat().getRangeBorder(ExcelScript.BorderIndex.insideVertical).setWeight(ExcelScript.BorderWeight.thin);

  // Set border for all cells on selectedSheet

  selectedSheet.getRange().getFormat().getRangeBorder(ExcelScript.BorderIndex.insideHorizontal).setStyle(ExcelScript.BorderLineStyle.continuous);

  selectedSheet.getRange().getFormat().getRangeBorder(ExcelScript.BorderIndex.insideHorizontal).setWeight(ExcelScript.BorderWeight.thin);

  // Auto fit the columns of range D:D on selectedSheet

  selectedSheet.getRange("C:C").getFormat().autofitColumns();

  // Auto fit the columns of range F:F on selectedSheet

  selectedSheet.getRange("E:E").getFormat().autofitColumns();

  // Clear ExcelScript.ClearApplyTo.contents from range E:E on selectedSheet

  selectedSheet.getRange("E:E").clear(ExcelScript.ClearApplyTo.contents);

  // Set range E1 on selectedSheet

  selectedSheet.getRange("E1").setValue("FNSKU");

  // Set width of column(s) at range E:E on selectedSheet to 14

  selectedSheet.getRange("E:E").getFormat().setColumnWidth(100);

  // Set range f1 on selectedSheet

  selectedSheet.getRange("f1").setValue("BBE");

  // Set width of column(s) at range F:F on selectedSheet to 14

  selectedSheet.getRange("F:F").getFormat().setColumnWidth(100);

}

[#2 · Accepted solution]

The error "Range delete: You cannot perform the requested operation" in line 4 indicates that the delete operation is not allowed on the specified range. This error can occur due to several reasons:

Protection: Check if the worksheet or specific range you are trying to delete is protected. If it is protected, you need to unprotect it before performing the delete operation. You can use the worksheet.getProtection().unprotect() method to remove the protection.

Merged cells: If the range you are trying to delete contains merged cells, deleting the range may not be allowed. In such cases, you can try unmerging the cells first using the range.unmerge() method and then perform the delete operation.

Invalid range: Verify that the range you are specifying ("B:B", "F:F", "G:G", etc.) is correct and valid. Make sure it does not include any extra characters or spaces.

Compatibility: It's possible that the delete operation you are trying to perform is not supported in Excel 2016. Check the documentation or specific limitations of the Excel version you are using to see if there are any restrictions on deleting ranges.

You can try modifying your code to handle these scenarios and ensure that the range you are trying to delete is valid and allowed for deletion.

[#3]

Thank you, method for unprotection worked!