Some ExcelScript functions do not work starting from June

Yseimur 20 Reputation points
2023-06-05T04:49:00.23+00:00

Hi,

I have some Excel Scripts worked fine for few months, but suddenly not working starting from June.

The functions I found not working are:

  • AutoFilter.clearCriteria (starting from 1/Jun)
  • Range.insert (starting from 4/Jun)

I did a few tests, seems they did not work in desktop app and MS Flow, but they are working if I open the file in browser...

For example, if I create a new spreadsheet and record a new script like below (just adding a row in a new spreadsheet)

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

Previously it worked in desktop app, but it does not work now...

The same for:

	selectedSheet.getAutoFilter().clearCriteria();

Can anyone share your idea? Thank you very much.

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,687 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,715 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Yseimur 20 Reputation points
    2023-06-06T04:15:34.2433333+00:00

    I got a suggestion when I added my comment on another post, and it works.

    Which is just adding an unprotection before taking action like insert row or removing filter criteria.

    Seems it is now needed even there is no protection in the whole workbook...

    	selectedSheet.getProtection().unprotect();
    
    2 people found this answer helpful.

  2. Tanay Prasad 2,120 Reputation points
    2023-06-05T05:50:31.03+00:00

    Hi,

    Are you facing any errors? If yes, it'd be helpful if you share it with us.

    Apart from that, here are a few possible reasons and solutions:

    1. Excel version or update: It's possible that an update or version change in Excel could have affected the behavior of certain script functions. Make sure you are using the latest version of Excel and have installed any available updates.
    2. Compatibility issues: Check if the affected functions are compatible with the version of Excel you are using. Some script functions may have specific requirements or limitations based on the Excel version or platform (desktop app vs. browser).
    3. Script configuration: Double-check the configuration of your script and verify that it is correctly referencing the target worksheets or ranges. Ensure that the necessary permissions are set for the script to perform the desired actions.
    4. Script execution environment: Consider the environment in which the script is being executed. If the script was working previously on the desktop app but is not working now, check if any changes have been made to your system, such as security settings or conflicting software.

    I hope this helps.

    Best Regards.


  3. ryo.tamai 0 Reputation points
    2023-06-07T06:59:29.61+00:00
    selectedSheet.getProtection().unprotect();
    

    We were able to confirm that a simple program works by the above method. However, there were some things that did not work with my program. I think there is a separate flag from the normal sheet protection, which is cleared by the above code. However, this flag is standing due to some other factor. Since I don't know what the factor is, I can only call the above code just before insert()...

    0 comments No comments