Skip to main content

Notifications

Power Automate - General Discussion
Unanswered

Using Excel script and getting error

(0) ShareShare
ReportReport
Posted on by 10

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.

 

Baptiste_Med_0-1687533029191.png

 

  1. First a message bad gateway at the top : should be a temporary error from what I read on this forum
  2. 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)
  3. 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.  

Baptiste_Med_1-1687533090476.png

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

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

 

Thank you.

 

  • Baptiste_Med Profile Picture
    Baptiste_Med 10 on at
    Re: Using Excel script and getting error

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

     

    Baptiste_Med_1-1692949166571.png

     

    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.

  • Baptiste_Med Profile Picture
    Baptiste_Med 10 on at
    Re: Using Excel script and getting error

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

     

    Baptiste_Med_0-1687876521578.png

    The hyperlink lead to a blank page 😅

    So painful...

     

  • Baptiste_Med Profile Picture
    Baptiste_Med 10 on at
    Re: Using Excel script and getting error

    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.

  • abowen Profile Picture
    abowen 222 on at
    Re: Using Excel script and getting error

    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. 

  • Baptiste_Med Profile Picture
    Baptiste_Med 10 on at
    Re: Using Excel script and getting error

    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

  • abowen Profile Picture
    abowen 222 on at
    Re: Using Excel script and getting error

    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!

  • Baptiste_Med Profile Picture
    Baptiste_Med 10 on at
    Re: Using Excel script and getting error

    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

     

    Baptiste_Med_0-1687766662769.png

     

  • abowen Profile Picture
    abowen 222 on at
    Re: Using Excel script and getting error

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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,567

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,907

Leaderboard