Forum Discussion
StefanMogyorosi
Jun 01, 2023Copper Contributor
Excel script run with error since today (Range Delete)
I have a script that manipulates an excel table by deleting columns and adding new ones. The script that yesterday worked correctly today stops working and i get these errors:
Range delete: You cannot perform the requested operation.
Range insert: You cannot perform the requested operation.
To be sure I tried the example script from the manual:
/**
* This sample creates a sample range, then deletes
* "A1" using different DeleteShiftDirection values.
*/
function main(workbook: ExcelScript.Workbook) {
// Add sample data to better visualize the delete changes.
const currentSheet = workbook.getActiveWorksheet();
currentSheet.getRange("A1:D4").setValues([
[1,2,3,4],
[5,6,7,8],
[9,10,11,12],
[13,14,15,16]]);
// Delete A1 and shift the cells from the right to fill the space.
// The value being deleted is 1.
currentSheet.getRange("A1").delete(ExcelScript.DeleteShiftDirection.left);
// Delete A1 and shift the cells from the bottom to fill the space.
// The value being deleted is 2.
currentSheet.getRange("A1").delete(ExcelScript.DeleteShiftDirection.up);
// Log the sample range. The values should be:
/*
5, 3, 4, "",
9, 6, 7, 8,
13, 10, 11, 12,
"", 14, 15, 16
*/
console.log(currentSheet.getRange("A1:D4").getValues());
}
And I got the same error. Maybe someone knows what happened from yesterday and how to solve it.
I guess that's this sample ExcelScript.DeleteShiftDirection enum - Office Scripts | Microsoft Learn
"delete" doesn't work in Excel Desktop. If you run exactly the same script in Excel for web it shall work.
- RobertoMMeloCopper Contributor
StefanMogyorosiSame problem here... even on a completely blank worksheet, even using the [script recorder] to ensure it doesn't contain any typing errors, the insert and delete methods throws this error. Another interesting fact is that the same operation using the old VBA works.
The same script worked flawlesly weeks ago. Today, this insert operation is not working at all in Excel Script, just in VBA.
- MichelleRanMSFT
Microsoft
This should be fixed in CC and will be fixed soon for MEC and SAC as well. Thank you for your patience!Thank you for the update!
- MarcoANAMCopper ContributorThe scripts are working again! today I was able to confirm that with a script on excel for desktop
- StefanMogyorosiCopper ContributorThanks MarcoANAM
Great to know, thank you for sharing.
- MichelleRanMSFT
Microsoft
Thanks for reporting this issue and sorry for the inconvenience! It does seem like certain scripts have stopped working on desktop. We're looking into this now; I'll be back in touch once I have more information.I can't give concrete samples right now, but that happens from time to time. It will be great if you sync behaviour.
- KingsleyOdebCopper Contributor
StefanMogyorosi I have the same issue with table sort was working yesterday, but today giving me error
TableSort apply: You cannot perform the requested operation.
- MarcoANAMCopper Contributorsame issue here
I guess that's this sample ExcelScript.DeleteShiftDirection enum - Office Scripts | Microsoft Learn
"delete" doesn't work in Excel Desktop. If you run exactly the same script in Excel for web it shall work.
- StefanMogyorosiCopper Contributor
SergeiBaklanthanks a lot, indeed on Excel web is working.
The strange thing is that until yesterday it worked for me also on Excel Desktop. I suppose an update was released for Excel Desktop that block these operations.
StefanMogyorosi , you are welcome.
Yes, that's quite seldom but some script functionality which works on Excel for web doesn't work in Excel Desktop. Not sure why so, the point is to check everything on web version first.
- aduce23Copper Contributor
StefanMogyorosi same issue here