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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (2,715)