Power Automate Run Script in Excel

BCS · April 17, 2023, 8:39am

Hi folks,

I am trying to create a flow that will run an office script in an Excel file held in SharePoint, the script is a simple refresh all data connections and works if i open the file and manually refresh the script.

I have tries various slightly different flows but although they all say successful, when i manually open the file there is no data.

Flows tried:

Run script
Get files (properties only), filter array, check out file, run script, delay (1min), check in file
Run script, delay (1min), no check out options

All the above flows say successful but none of them have any data when i manually open the file.

The data source is a small sql view with around 100 rows. I realise that there is a SQL connector but this is a premium service and i don’t want to pay for this given that the data is such a small list of data so this is my workaround which isn’t working.

Any help advice would be appreciated.
Thanks

ankit · April 21, 2023, 6:15am

Hi @henry.habib - Please check and help if possible.

Melissa · April 21, 2023, 7:39am

Hi @BCS,

Seems we’re both out of luck

Workbook.refreshAllDataConnections() is not supported in Power Automate.

.
Found a useful reference.

github.com

OfficeDev/office-scripts-docs/blob/main/docs/testing/power-automate-troubleshooting.md

---
title: Troubleshoot Office Scripts running in Power Automate
description: Tips, platform information, and known issues with the integration between Office Scripts and Power Automate.
ms.topic: troubleshooting-general
ms.date: 11/29/2023
ms.localizationpriority: medium
---

# Troubleshoot Office Scripts running in Power Automate

Power Automate runs scripts on your behalf in independent Excel sessions. This causes some behavioral changes that may create issues with certain scripts or scenarios. There are also limitations and behaviors from the Power Automate platform script writers should know. Be sure to read the articles [Troubleshoot Office Scripts](troubleshooting.md) and [Platform limits and requirements with Office Scripts](platform-limits.md), as much of that information also applies to scripts in flows.

> [!TIP]
> If you're just starting to use Office Scripts with Power Automate, please start with [Run Office Scripts with Power Automate](../develop/power-automate-integration.md) to learn about the platforms.

[!INCLUDE [Power Automate needs a business license](../includes/power-automate-needs-business.md)]

## Avoid relative references

Power Automate runs your script in the chosen Excel workbook on your behalf. The workbook might be closed when this happens. Any API that relies on the user's current state, such as `Workbook.getActiveWorksheet`, may behave differently in Power Automate. This is because the APIs are based on a relative position of the user's view or cursor and that reference doesn't exist in a Power Automate flow.

This file has been truncated. show original

EnterpriseDNA · April 21, 2023, 11:29am

Hello @BCS

Did the responses above help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION

Thank you

EnterpriseDNA · April 24, 2023, 12:31pm

Hi @BCS

Due to inactivity, a response on this post has been tagged as “Solution”.

If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey,.

We hope you’ll give your insights on how we can further improve the Support forum. Thanks!