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

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Users online (3,120)