Optimize Workflow: Mastering Power Automate App Scripts

 

Exploring the Versatility and Limitations of App Scripts in Automation

App Scripts offer a compelling solution for enhancing automation capabilities beyond the Microsoft ecosystem. They allow developers to create and deploy custom APIs quickly, leveraging a simple setup process accessible through a Google account. Unlike Office Scripts, App Scripts can perform a wide range of tasks across Google services like Sheets, Gmail, and Drive, providing a standalone code editor for development. They support JavaScript, facilitating integration with existing projects.

However, integrating App Scripts within Power Automate and Power Apps introduces unique considerations, such as handling 302 redirect responses, which may complicate their use in certain scenarios. Despite these challenges, App Scripts present significant opportunities for realizing complex automation workflows, especially when coupled with custom connectors and additional code to streamline data handling.

While App Scripts unlock remarkable possibilities for both personal and small-scale projects, they carry intrinsic trade-offs that may deter their adoption in enterprise environments. Security concerns, the requirement for Google accounts, and potential policy violations represent noteworthy considerations. Moreover, reliance on an external platform like Google may introduce support and longevity risks. Nonetheless, for proof of concepts, hobbyist projects, or low-risk solutions, App Scripts emerge as a valuable tool in the automation toolbox, offering a glimpse into the future of cross-platform integrations.

 

Read the full article Power Automate - Scripts with App Scripts

 

Power Automate - Scripts with App Scripts The one thing I really wish Power Automate had was a script action like Logic Apps does. Luckily there is Office Scripts which is close, it is incredibly powerful but has some limitations and trade-offs. But now I have found App Scripts, and they are what I wish Office Scripts were.

If you are not familiar with App Scripts, they are Google Sheets version of VBA. So it kind of went, Google copied VBA to create App Scripts, Microsoft copied App Scripts to create Office Scripts.

So what's the main differences, well

• It's JavaScript not TypeScript (but that's no big deal),
• It interacts with Google Sheets, Gmail, and Drive instead of Excel, Outlook, and OneDrive
• It has a standalone code editor not part of Sheets/Excel
• It can load libraries (kind of)
• It has out of the box connectors
• It can be called as an API

and the last one is the really cool bit, it means I can create my own custom api 😎 and run any script I like.

Damien Bird did a great demo on how you can use Logic Apps to create your own api and run code. And that solution is definitely better, as you can do everything under Microsoft umbrella. But it's not that simple, as you need to have Azure resource groups, spn's, and there is a cost, whereas App Scripts are 100% free and require zero effort to set up.

In the blog there's a couple of things I want to show you:

• How to get App Script setup
• How to create an API
• How to call them in Power Automate/Power Apps
• Cool Demos
• Trade-offs / negatives

All you need is a Google account and to go to script.google.com/home. Here you can manage all of your scripts and create new ones. When you create a new script you are shown a simple code editor, it has intellisense and is easy to use. You can also add libraries and services, which act like connectors allowing you to interact with Google services.

To call the API you need to create either a GET or POST function. Now here's the fun bit, for use to return data as a JSON App Scripts throws us a curved ball, it doesn't do a straight return but a 302 redirect return. But this isn't easy to scale, luckily you can do it in a custom connector. And that's it, the rest of it is just standard custom connector stuff.

In theory, you can do almost anything, the only limits you have is 6 min timeout (vs Office Scripts 2 min), and a few specific ones here. I've got 2 demos to show, a GET and POST. Both connectors are available to download but I can't promise the API will be up for long.

So what's the downside with this free API, well there are a few. The first is it's outside of the Power Platform, so it adds complexity. Second and this is a big one, it blows a hole in your DLP policy. Third is it's free and from Google. Finally, and this is the big one, it's not secured. So for me sadly it isn't an enterprise solution for me, but I think it definitely still has some use cases like PoC's, Just for fun projects, Small low risk solutions.

More About Power Automate and Custom Scripts

Utilizing custom scripts in automation platforms like Power Automate enriches the possibilities for automations, extending functionality beyond pre-built features. App Scripts and similar platforms offer creative avenues for developers to craft bespoke solutions tailored to specific workflow needs. This flexibility notably enhances data handling between different services, such as Google Sheets and Excel, or integrating unique API functionalities. However, enthusiasts and professionals should weigh the advantages, such as cost savings and ease of setup, against potential drawbacks including increased complexity and security considerations. The balance between leveraging these custom scripts and adhering to enterprise-level solutions rests on factors like project scope, security requirements, and long-term maintenance considerations. Ultimately, custom scripts in automation tools provide a powerful means to solve unique problems, augmenting the capabilities of platforms such as Power Automate while encouraging innovation within the domain of automated workflows.

 

 

People also ask

Can you run a script from Power Automate?

To initiate a script through Power Automate, navigate to the main Power Automate page and select 'My flows'. From there, choose 'My tutorial flow' listed under the 'My flows' tab to view its details — this flow was set up earlier. To execute it, click on 'Run'.

What is the difference between Power Automate and script?

Power Automate facilitates the transfer of data snippets across different flow actions, enabling scripts to process the information required and deliver the desired outcomes back to your flow. It is possible to pass data to these scripts in the form of static values, expressions, or dynamic content.

How do you integrate Power Automate with power apps?

To integrate Power Automate within Power Apps, start by opening an app (new or existing) in Power Apps Studio. Proceed by selecting 'Settings' at the top, followed by 'Upcoming features'. In the 'Retired' tab, find and activate the 'Enable Classic Power Automate pane' by switching the toggle to 'On'.

Can Power Automate trigger a PowerShell script?

It is indeed possible to trigger PowerShell scripts within Power Automate by utilizing the "Execute a script" action. To achieve this, simply create a new flow in Power Automate and incorporate the "Execute a script" action into your workflow.

 

Keywords

Power Automate Scripts, App Scripts Automation, Automating with Power Automate, Power Automate App Integration, Scripting in Power Automate, App Script Workflows, Power Automate Custom Scripts, Power Automate Scripting Techniques