cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
davidk1
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")
  b0.setValues("Test_date");
  let b2= selectedSheet.getRange("I2")
  b2.setFormula("=if(weekday(today(),2)<5,if(datevalue(D2)-today()=1,1,0),if(datevalue(d2)-today()-2=1,1,0))");
  
  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');      
      table.deleteRowsAt(i);
      removed++;
    }
  }
  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.

3 REPLIES 3
davidk1
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.

sumurthy
Microsoft
Microsoft

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 - 

https://docs.microsoft.com/en-us/office/dev/scripts/testing/power-automate-troubleshooting#avoid-usi...

davidk1
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

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