Forum Discussion

StefanMogyorosi's avatar
StefanMogyorosi
Copper Contributor
Jun 01, 2023
Solved

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.

16 Replies

  • RobertoMMelo's avatar
    RobertoMMelo
    Copper 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.

  • This should be fixed in CC and will be fixed soon for MEC and SAC as well. Thank you for your patience!
  • MarcoANAM's avatar
    MarcoANAM
    Copper Contributor
    The scripts are working again! today I was able to confirm that with a script on excel for desktop
  • 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.
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor

      MichelleRanMSFT 

      I can't give concrete samples right now, but that  happens from time to time. It will be great if you sync behaviour.

  • KingsleyOdeb's avatar
    KingsleyOdeb
    Copper 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.

     

    • StefanMogyorosi's avatar
      StefanMogyorosi
      Copper 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.

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor

        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.

Resources