Feedback: ExcelRange.getDataValidation error when called from Power Autoamte

[raphaelzaneti]

Type of issue

Other (describe below)

Feedback

Hi there,

I'm a contributor at the Power Platform Community Forum. A user recently reported an issue when running the ExcelRange.getDataValidation method from a Run Script action in Power Automate. Any script containing the method doesn't work when initialized from Power Automate, although it works fine when ran directly from Excel.

According the docs, a few methods do not work (entirely or partially) when called form Power Automate, but getDataValidation is not one of them.

A few additional notes here about the script behavior when containing the getDataValidation method:

• If you remove this specific piece of code from the Script, it works fine from Power Automate
• In Power Automate, the Run script action returns a timeout error
• The script doesn't perform any action at all at the workbook: any steps set before the getDataValidation won't be taken when running it from Power Automate.

Let me know if I can help with any additional info.

Sample code that works when called from Excel, but not from PA:
`function main(workbook: ExcelScript.Workbook) {
const ws = workbook.getWorksheet('Sheet1')

const cell = ws.getRange('B2')
const validationRng = ws.getRange("I3:I5")

cell.getDataValidation().setRule({ list: { inCellDropDown: true, source: validationRng}})

}`

Community thread: https://community.powerplatform.com/forums/thread/details/?threadid=6e0de7fa-5be5-ef11-a730-7c1e52465fa1

Page URL

https://learn.microsoft.com/en-us/office/dev/scripts/testing/power-automate-troubleshooting

Content source URL

https://github.com/OfficeDev/office-scripts-docs/blob/main/docs/testing/power-automate-troubleshooting.md

Author

Raphael Haus Zaneti (@raphaelzaneti)

Document Id

44a99c22-2609-dbb5-4a5d-cadd43915d37

[michelleranmsft]

Hi Raphael Haus Zaneti (@raphaelzaneti),

Thanks for reporting this issue! Based on my testing, it appears that this issue only occurs when trying to create specific types of data validation rules, e.g., a dropdown from a source range. I've logged a bug for our team to investigate (internal tracking: 9832646) and will circle back when we have more information to share. In the meantime, as a workaround, you can convert the range values to a comma-delimited string:

function main(workbook: ExcelScript.Workbook) {
    const ws = workbook.getWorksheet('Sheet1');

    const cell = ws.getRange('B2');
    const validationRng = ws.getRange("I3:I5");
    const values = [].concat.apply([], validationRng.getTexts()).join(',');
    cell.getDataValidation().setRule({ list: { inCellDropDown: true, source: values} });
}

Hope that helps! Let me know if you have any questions.