Script run with error (Range delete)

Question

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.&nbsp;When I try to run in Excel online, there is no issue with the script at all. Any advices?&nbsp;Thank you.&nbsp;&nbsp;There is the script:function&nbsp;main(workbook:&nbsp;ExcelScript.Workbook)&nbsp;{&nbsp;&nbsp;let&nbsp;selectedSheet&nbsp;=&nbsp;workbook.getActiveWorksheet();&nbsp;&nbsp;//&nbsp;Delete&nbsp;range&nbsp;B:B&nbsp;on&nbsp;selectedSheet&nbsp;&nbsp;selectedSheet.getRange("B:B").delete(ExcelScript.DeleteShiftDirection.left);&nbsp;&nbsp;//&nbsp;Delete&nbsp;range&nbsp;F:F&nbsp;on&nbsp;selectedSheet&nbsp;&nbsp;selectedSheet.getRange("F:F").delete(ExcelScript.DeleteShiftDirection.left);&nbsp;&nbsp;//&nbsp;Delete&nbsp;range&nbsp;G:G&nbsp;on&nbsp;selectedSheet&nbsp;&nbsp;selectedSheet.getRange("G:G").delete(ExcelScript.DeleteShiftDirection.left);&nbsp;&nbsp;//&nbsp;Auto&nbsp;fit&nbsp;the&nbsp;columns&nbsp;of&nbsp;range&nbsp;B:B&nbsp;on&nbsp;selectedSheet&nbsp;&nbsp;selectedSheet.getRange("B:B").getFormat().autofitColumns();&nbsp;&nbsp;//&nbsp;Auto&nbsp;fit&nbsp;the&nbsp;columns&nbsp;of&nbsp;range&nbsp;F:F&nbsp;on&nbsp;selectedSheet&nbsp;&nbsp;selectedSheet.getRange("F:F").getFormat().autofitColumns();&nbsp;&nbsp;//&nbsp;Auto&nbsp;fit&nbsp;the&nbsp;columns&nbsp;of&nbsp;range&nbsp;G:G&nbsp;on&nbsp;selectedSheet&nbsp;&nbsp;selectedSheet.getRange("G:G").getFormat().autofitColumns();&nbsp;&nbsp;//&nbsp;Set&nbsp;number&nbsp;format&nbsp;for&nbsp;range&nbsp;C:C&nbsp;on&nbsp;selectedSheet&nbsp;&nbsp;selectedSheet.getRange("C:C").setNumberFormatLocal("0");&nbsp;&nbsp;//&nbsp;Set&nbsp;horizontal&nbsp;alignment&nbsp;to&nbsp;ExcelScript.HorizontalAlignment.general&nbsp;for&nbsp;all&nbsp;cells&nbsp;on&nbsp;selectedSheet&nbsp;&nbsp;selectedSheet.getRange().getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.general);&nbsp;&nbsp;selectedSheet.getRange().getFormat().setIndentLevel(0);&nbsp;&nbsp;//&nbsp;Set&nbsp;vertical&nbsp;alignment&nbsp;to&nbsp;ExcelScript.VerticalAlignment.center&nbsp;for&nbsp;all&nbsp;cells&nbsp;on&nbsp;selectedSheet&nbsp;&nbsp;selectedSheet.getRange().getFormat().setVerticalAlignment(ExcelScript.VerticalAlignment.center);&nbsp;&nbsp;selectedSheet.getRange().getFormat().setIndentLevel(0);&nbsp;&nbsp;//&nbsp;Set&nbsp;wrap&nbsp;text&nbsp;to&nbsp;false&nbsp;for&nbsp;all&nbsp;cells&nbsp;on&nbsp;selectedSheet&nbsp;&nbsp;selectedSheet.getRange().getFormat().setWrapText(false);&nbsp;&nbsp;//&nbsp;Set&nbsp;text&nbsp;orientation&nbsp;to&nbsp;0&nbsp;for&nbsp;all&nbsp;cells&nbsp;on&nbsp;selectedSheet&nbsp;&nbsp;selectedSheet.getRange().getFormat().setTextOrientation(0);&nbsp;&nbsp;//&nbsp;Indent&nbsp;set&nbsp;to&nbsp;0&nbsp;for&nbsp;all&nbsp;cells&nbsp;on&nbsp;selectedSheet&nbsp;&nbsp;selectedSheet.getRange().getFormat().setIndentLevel(0);&nbsp;&nbsp;//&nbsp;Set&nbsp;horizontal&nbsp;alignment&nbsp;to&nbsp;ExcelScript.HorizontalAlignment.center&nbsp;for&nbsp;all&nbsp;cells&nbsp;on&nbsp;selectedSheet&nbsp;&nbsp;selectedSheet.getRange().getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);&nbsp;&nbsp;selectedSheet.getRange().getFormat().setIndentLevel(0);&nbsp;&nbsp;//&nbsp;Set&nbsp;vertical&nbsp;alignment&nbsp;to&nbsp;ExcelScript.VerticalAlignment.center&nbsp;for&nbsp;all&nbsp;cells&nbsp;on&nbsp;selectedSheet&nbsp;&nbsp;selectedSheet.getRange().getFormat().setVerticalAlignment(ExcelScript.VerticalAlignment.center);&nbsp;&nbsp;selectedSheet.getRange().getFormat().setIndentLevel(0);&nbsp;&nbsp;//&nbsp;Set&nbsp;wrap&nbsp;text&nbsp;to&nbsp;false&nbsp;for&nbsp;all&nbsp;cells&nbsp;on&nbsp;selectedSheet&nbsp;&nbsp;selectedSheet.getRange().getFormat().setWrapText(false);&nbsp;&nbsp;//&nbsp;Set&nbsp;text&nbsp;orientation&nbsp;to&nbsp;0&nbsp;for&nbsp;all&nbsp;cells&nbsp;on&nbsp;selectedSheet&nbsp;&nbsp;selectedSheet.getRange().getFormat().setTextOrientation(0);&nbsp;&nbsp;//&nbsp;Indent&nbsp;set&nbsp;to&nbsp;0&nbsp;for&nbsp;all&nbsp;cells&nbsp;on&nbsp;selectedSheet&nbsp;&nbsp;selectedSheet.getRange().getFormat().setIndentLevel(0);&nbsp;&nbsp;//&nbsp;Set&nbsp;height&nbsp;of&nbsp;row(s)&nbsp;at&nbsp;all&nbsp;cells&nbsp;on&nbsp;selectedSheet&nbsp;to&nbsp;20&nbsp;&nbsp;selectedSheet.getRange().getFormat().setRowHeight(20);&nbsp;&nbsp;//&nbsp;Set&nbsp;width&nbsp;of&nbsp;column(s)&nbsp;at&nbsp;range&nbsp;E:E&nbsp;on&nbsp;selectedSheet&nbsp;to&nbsp;12.57&nbsp;&nbsp;selectedSheet.getRange("E:E").getFormat().setColumnWidth(12.57);&nbsp;&nbsp;//&nbsp;Set&nbsp;border&nbsp;for&nbsp;all&nbsp;cells&nbsp;on&nbsp;selectedSheet&nbsp;&nbsp;selectedSheet.getRange().getFormat().getRangeBorder(ExcelScript.BorderIndex.diagonalDown).setStyle(ExcelScript.BorderLineStyle.none);&nbsp;&nbsp;//&nbsp;Set&nbsp;border&nbsp;for&nbsp;all&nbsp;cells&nbsp;on&nbsp;selectedSheet&nbsp;&nbsp;selectedSheet.getRange().getFormat().getRangeBorder(ExcelScript.BorderIndex.diagonalUp).setStyle(ExcelScript.BorderLineStyle.none);&nbsp;&nbsp;//&nbsp;Set&nbsp;border&nbsp;for&nbsp;all&nbsp;cells&nbsp;on&nbsp;selectedSheet&nbsp;&nbsp;selectedSheet.getRange().getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeLeft).setStyle(ExcelScript.BorderLineStyle.continuous);&nbsp;&nbsp;selectedSheet.getRange().getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeLeft).setWeight(ExcelScript.BorderWeight.thin);&nbsp;&nbsp;//&nbsp;Set&nbsp;border&nbsp;for&nbsp;all&nbsp;cells&nbsp;on&nbsp;selectedSheet&nbsp;&nbsp;selectedSheet.getRange().getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeTop).setStyle(ExcelScript.BorderLineStyle.continuous);&nbsp;&nbsp;selectedSheet.getRange().getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeTop).setWeight(ExcelScript.BorderWeight.thin);&nbsp;&nbsp;//&nbsp;Set&nbsp;border&nbsp;for&nbsp;all&nbsp;cells&nbsp;on&nbsp;selectedSheet&nbsp;&nbsp;selectedSheet.getRange().getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeBottom).setStyle(ExcelScript.BorderLineStyle.continuous);&nbsp;&nbsp;selectedSheet.getRange().getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeBottom).setWeight(ExcelScript.BorderWeight.thin);&nbsp;&nbsp;//&nbsp;Set&nbsp;border&nbsp;for&nbsp;all&nbsp;cells&nbsp;on&nbsp;selectedSheet&nbsp;&nbsp;selectedSheet.getRange().getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeRight).setStyle(ExcelScript.BorderLineStyle.continuous);&nbsp;&nbsp;selectedSheet.getRange().getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeRight).setWeight(ExcelScript.BorderWeight.thin);&nbsp;&nbsp;//&nbsp;Set&nbsp;border&nbsp;for&nbsp;all&nbsp;cells&nbsp;on&nbsp;selectedSheet&nbsp;&nbsp;selectedSheet.getRange().getFormat().getRangeBorder(ExcelScript.BorderIndex.insideVertical).setStyle(ExcelScript.BorderLineStyle.continuous);&nbsp;&nbsp;selectedSheet.getRange().getFormat().getRangeBorder(ExcelScript.BorderIndex.insideVertical).setWeight(ExcelScript.BorderWeight.thin);&nbsp;&nbsp;//&nbsp;Set&nbsp;border&nbsp;for&nbsp;all&nbsp;cells&nbsp;on&nbsp;selectedSheet&nbsp;&nbsp;selectedSheet.getRange().getFormat().getRangeBorder(ExcelScript.BorderIndex.insideHorizontal).setStyle(ExcelScript.BorderLineStyle.continuous);&nbsp;&nbsp;selectedSheet.getRange().getFormat().getRangeBorder(ExcelScript.BorderIndex.insideHorizontal).setWeight(ExcelScript.BorderWeight.thin);&nbsp;&nbsp;//&nbsp;Auto&nbsp;fit&nbsp;the&nbsp;columns&nbsp;of&nbsp;range&nbsp;D:D&nbsp;on&nbsp;selectedSheet&nbsp;&nbsp;selectedSheet.getRange("C:C").getFormat().autofitColumns();&nbsp;&nbsp;//&nbsp;Auto&nbsp;fit&nbsp;the&nbsp;columns&nbsp;of&nbsp;range&nbsp;F:F&nbsp;on&nbsp;selectedSheet&nbsp;&nbsp;selectedSheet.getRange("E:E").getFormat().autofitColumns();&nbsp;&nbsp;//&nbsp;Clear&nbsp;ExcelScript.ClearApplyTo.contents&nbsp;from&nbsp;range&nbsp;E:E&nbsp;on&nbsp;selectedSheet&nbsp;&nbsp;selectedSheet.getRange("E:E").clear(ExcelScript.ClearApplyTo.contents);&nbsp;&nbsp;//&nbsp;Set&nbsp;range&nbsp;E1&nbsp;on&nbsp;selectedSheet&nbsp;&nbsp;selectedSheet.getRange("E1").setValue("FNSKU");&nbsp;&nbsp;//&nbsp;Set&nbsp;width&nbsp;of&nbsp;column(s)&nbsp;at&nbsp;range&nbsp;E:E&nbsp;on&nbsp;selectedSheet&nbsp;to&nbsp;14&nbsp;&nbsp;selectedSheet.getRange("E:E").getFormat().setColumnWidth(100);&nbsp;&nbsp;//&nbsp;Set&nbsp;range&nbsp;f1&nbsp;on&nbsp;selectedSheet&nbsp;&nbsp;selectedSheet.getRange("f1").setValue("BBE");&nbsp;&nbsp;//&nbsp;Set&nbsp;width&nbsp;of&nbsp;column(s)&nbsp;at&nbsp;range&nbsp;F:F&nbsp;on&nbsp;selectedSheet&nbsp;to&nbsp;14&nbsp;&nbsp;selectedSheet.getRange("F:F").getFormat().setColumnWidth(100);}&nbsp;

nikolinode · Accepted Answer

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.

Forum Discussion

Script run with error (Range delete)

2 Replies

Resources