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

[davidk1]

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.

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

Thanks for your help.

[davidk1]

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]

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]

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.