Using Excel script and getting error

(0) Share

Report

Posted on by Baptiste_Med

Hello everyone,

I receive a Excel file every day and has to modify it. For this task, I use an Excel script.

My script works, I would like to automate this task using Power Automate.

The idea is pretty simple, each time I create an Excel file in a Sharepoint folder, the script apply to the Excel file.

Unfortunately the task Run script fail every time, it shows many things i don't know.

First a message bad gateway at the top : should be a temporary error from what I read on this forum
Then a code 502 error at the bottom : should be a authorization problem (i dont know why my Power Automate account is divided on 2 different user)
And a error message "The response is not in a JSON format." : definitely not saying what part of the response should be a JSON format.

I'm not sure i'm facing only one problem.

Could any one help me, any help would be so much appreciated.

Thank you.

Categories:

General topics

I have the same question (0)

All responses (8)

Answers (0)

abowen 222 on at

Like (0)

Report
Copy link

Link copied!

Could you please post a screenshot of your flow with inputs?

Was this reply helpful? Yes No
Baptiste_Med 10 on at

Like (0)

Report
Copy link

Link copied!

Sure, the input is "when a file is created on sharepoint"
both variables are useless and were part of previous version
Then apply an Excel script to the file created in step 1

Was this reply helpful? Yes No
abowen 222 on at

Like (0)

Report
Copy link

Link copied!

Perhaps try passing only the dynamic content (that begins with "x-ms-file-name") to the File parameter in your 'Run script' action? I'm assuming that is the parameter that expects only JSON so it throws an error when you pass it the raw text before the dynamic content. However, I do see the directory browser button to the right of that parameter box, so it must expect a path of some sort. Regardless, I would give that a shot as the rest of the parameters obviously expect text, except maybe Script. I have not yet used 'Run script', so I hope my comments are more helpful than not.

Let me know!

Was this reply helpful? Yes No
Baptiste_Med 10 on at

Like (0)

Report
Copy link

Link copied!

Thanks for your help.

I tried 3 different alternatives today
First the dynamic "file name" alone : it gives an different error "Graph Item not found, was it unshared or deleted?"
Then the dynamic "path" & file name" : the path included the file name so thez file name was doubled
To end with the dynamic "path" : it gives the same mistake as before (bad gateway, error 502 and the response is not in a JSON format.

I found on the link below that setting a variable should help so I will let you know 🙂
https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-automate-paginated-excel-sharepoint-list

Was this reply helpful? Yes No
abowen 222 on at

Like (0)

Report
Copy link

Link copied!

I hope that should work for you! To be honest after my previous response I was unsure that it would do anything because I cannot figure out anything going wrong with your inputs. Do let me know! Would help expand my knowledge as well.

Was this reply helpful? Yes No
Baptiste_Med 10 on at

Like (0)

Report
Copy link

Link copied!

Hi,

The variable didn't work, the issue is still the same, error 502 + badgateway + the response isn't a Jason format.

I will let you know if I found new alternative.

Thanks for you help.

Was this reply helpful? Yes No
Baptiste_Med 10 on at

Like (0)

Report
Copy link

Link copied!

Still workin on it, i have found the graal : what to do when you get a error 502 badgateway

The hyperlink lead to a blank page 😅
So painful...

Was this reply helpful? Yes No
Baptiste_Med 10 on at

Like (0)

Report
Copy link

Link copied!

Dear reader,

You will find some news about this issue. In june I had still some leads to explore about this issue.

First a user credential issue : last year IT migrates the Microsoft tenant. So my first tenant was : myadresseemail@onmicrosoft.com then the IT supplier changed to myadresseemail@mycompany.com
But every flow I did under the first tenant were using the first email adress.

Actually the first email address still appears as a conection on the flow page (see below screenshot).

So the IT supplier suggest we remove the "onmicrosoft" email adress that is used as an alias for my current email address.

Now the old adress is deleted. And the error 502 badgateway doesn't appears.
The old email adress is still displayed on the flow page as a connection

BUT

There is no error 502 bad gateway error !

BUT

I still have error but the flow clearly show what line of the script is not well played.
So it's a little better.

FYI

The message clearly display : We were unable to run the script. Please try again.
Runtime error: Line 4: Cannot read properties of undefined (reading 'delete')
clientRequestId: 8952e9f7-62c3-4d3d-8da4-71fcd43e6a53

I guess the command below is not well written,
The line 4 is in orange.

function main(workbook: ExcelScript.Workbook) {
    let etude_des_Missions = workbook.getWorksheet("Etude des Missions");
    // Delete worksheet etude_des_Missions
    etude_des_Missions.delete();
    let etude_des_Feuilles_de_Travaille = workbook.getWorksheet("Etude des Feuilles de Travaille");

I will keep you informed.
See you later.

Was this reply helpful? Yes No