Some ExcelScript functions do not work starting from June

Question

Some ExcelScript functions do not work starting from June

Yseimur 20

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.

3 answers

Your answer

Answer 1

Yseimur 20

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();

Tanay Prasad 2,250 Reputation points

2023-06-06T06:36:38.47+00:00

okay.

So your query is solved now? If not, please let me know where you are in terms of the progress once.

If your query is solved and my answer helped you, i'd appreciate if you could mark my answer as Accept Answer.
Yseimur 20 Reputation points

2023-06-06T06:49:24.89+00:00

Hi Tanay,

I thought I got a solution from another post. But when I tried again, I found that it is not really that straight forward.

My "answer to myself" above may work for a very simple (or brand new) spreadsheet, but it does not work for my existing one. To be honest I am afraid it is some bug from Microsoft in the recent update. Which is something not "fixable" by end user.

I am afraid your answer above does not quite fit my question. Anyway, good try and thank you very much for sharing your opinion.

Answer 2

Tanay Prasad 2,250

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:

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.
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).
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.
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.

Yseimur 20 Reputation points

2023-06-05T07:53:09.46+00:00
Hi Tanya,

Thanks for your reply. Sorry that my post is not detail enough. Below are few more notes:

Yes, I will get an error message.

Taking the simple script in my post above, the message I got is:

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

Taking below script as another example. I just create a new spreadsheet and run below script

function main(workbook: ExcelScript.Workbook) { let selectedSheet = workbook.getActiveWorksheet(); console.log(selectedSheet.getAutoFilter().getIsDataFiltered()); selectedSheet.getAutoFilter().clearCriteria(); }

The result I got from console will be

false Line 5: AutoFilter clearCriteria: You cannot perform the requested operation.

Which means the function getIsDataFiltered works correctly, but clearCriteria does not work.

I am using 365 and have updated my Excel to latest version. The same problem exists.
I think it is unlikely that it is related to something like compatibility. As I believe inserting a new row should be a very simple function?

Is there any further suggestion about "script configuration" or "script execution environment"? Maybe any example setting may affect the running of script.

I am quite sure that I had not change my configuration. Anyway, I am not a system admin so maybe will there be some possible change that done by an admin that can affect the running of script like above? (i.e. some functions work, but some don't)

Both scripts mentioned above worked in desktop app before June, but not now. On the other hand, they have no problem running on Excel online (browser version).

Thank you very much.

Answer 3

ryo.tamai 0

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()...

Share via

Some ExcelScript functions do not work starting from June

3 answers

Your answer