Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Excel script doesn't run well whan activated from power automate

Hello the community.


I created an excel script to clean some line from an excel file.

When I run the script in excel online, it's working perfectly well.

When I try to run it from power automate, it's not working.


My script :

function main(workbook: ExcelScript.Workbook) {
  let selectedSheet = workbook.getActiveWorksheet();
  // Set range J1:K2 on selectedSheet
  let b0 = selectedSheet.getRange("I1")
  let b2= selectedSheet.getRange("I2")
  let table = workbook.getTable("Tableau1");
  let tableDataRange = table.getRangeBetweenHeaderAndTotal();
  let dataValues = tableDataRange.getValues();
  let range = selectedSheet.getUsedRange();
  let rangeValues = range.getValues();


  let removed = 0;
  // Important that you go from bottom to top to account for removing correct rows. 
  for (let i = dataValues.length-1; i >=0 ; i--) {
    if (rangeValues[i+1][8] === 0) {
     // tableDataRange.getRow(i).getFormat().getFill().setColor('Yellow');      
  console.log(`Removed ${removed} rows.`)


This is what I get when I run it directly from excel online : i have the line colored in yellow.

2021-01-18 09-04-57_acuity_poste_de_garde.xlsx - Google Chrome.png


But when I run it from power automate, it's only creating the filtering column on the right.


Thanks for your help.

Frequent Visitor

So I have been able to go one step further.

The problem is that when we launch the script from power automate, it's not calculating the formula result correctly.

I get #VALUE! instead of the result.


You should avoid using 'relative' referencing in Office Scripts for running Power Automate 


This line will not work in PA 

let selectedSheet = workbook.getActiveWorksheet();

Instead you can use 

let selectedSheet = workbook.getWorksheet('NAME-OF-YOUR-SHEET');


This doc explains it -

Frequent Visitor

This was not the issue. But I will modify, we never know.

I finally found the problems. There was 2.

First : my excel time is still PST. It should be UTC+1 as this is what I set in my Microsoft profile. Or UTC if I read article. So I had to deal with time zone conversion with formulas. Very funny.

Second : the script runs in English for date and time format (even if I set my excel as french). So it was not translating the date correctly. I had to translate, convert with formulas.


If you're smart, not an expert and want to be effective, use Zapier. It took me 5 minutes instead of days to do what I intend to do with Zapier (getting a list of appointment from Acuity and put it in a sharepont list). I had no choice but to use Flow due to company policy but Microsoft flow and Excel Script is just for experts.

Helpful resources

MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (2,073)