• Ed G.

Translate Excel Data Using Power Automate / Flow

This quick build was the result of Microsoft MVP Luise Freese posting a clip of someone translating languages on the fly from within Google Sheets. She wanted Excel to be able to do the same thing, and while I couldn't get it to be as 'native' as the Google example, I did have some fun and wanted to share.

The Trigger

For some reason, there aren't many triggers for Excel in Power Automate. In fact, there is only one, "For a Selected Row" which you can trigger manually on a row in a spreadsheet as long as it's in a table, and as long as the file is saved in OneDrive for Business. If you need to do more from within Excel, check out my article "When Power Automate / Flow Isn't Enough" for some workarounds.

For now, we'll start with the lonely trigger and use a demo table that has an ID column (we'll find out more about that later, a place for the text we want to translate, a language selector, and the translated text. I also added a column each to translate into English and German, as well as a column that notes the detected language of the origin text.

The Language

After that, I added an action from Microsoft Translator to Detect the Language. Check the docs for important notes on the connector, but the part that concerns us is that you don't need to enter a key:

  • If no subscription key is provided, the shared API key will be used, which comes with built-in throttling limits based on the number of characters that are being translated, specifically 55,000 characters per day and 100 requests per minute.

Then, I added a step to translate the original text into the chosen language. To do this, you'll want to select "Custom Value" for the Target Language, and then use the dynamic value from the spreadsheet's table. In the spreadsheet itself, you could make this column a dropdown from a picklist to avoid errors, but I was building a quick Proof-of-Concept and didn't do that.

I then added a couple of similar steps, but this time translating into static choices of English and German.

The Update

This is where the RowID comes in. In every other Excel action I've worked with, a 'hidden' unique ID was sent with the row contents...but not here. Rather than spend time troubleshooting that, I added a column for my own unique identifier to help Power Automate know which row in the table to update.

Here, I update the Origin Language with the detected language from the first action, then the various translations in their appropriate fields.

In the video below you can see the table in action. While not as cool as the Google version, I did have fun learning more about the Microsoft Translator action, and will probably be incorporating that into some actual business cases.

Here's the video:

If you're just getting started in Microsoft Power Automate and are looking for a quick guided-learning session, find my Micro-Job on Collab365. We can chat about what is getting in the way of your flow, and I can point you in the right direction.

Try. Fail. Learn. Repeat.

336 views0 comments

©2018 by The Flying Polymath.