top of page

PowerAutomate & Office Script 4

Apr 17
3 min read

In a world where VBA is prohibited, we tried every trick in the book—but the conclusion we reached was that it was "unrealistic."

  

🧭 Introduction

 

One day, VBA was suddenly banned.

The alternatives that emerged were Power Automate and Office Script.

The objective is clear:

 

"Paste the SAP VA05 report into Excel, format the content, and send it by email"—that's all.

However, this "simple" automation required more detours than anticipated and the decision to "give up" on certain things.

 

🔧 Verification Flow (Ideal)

 

Step

Operation details

automation

remarks

1

Copy data from SAP

❌

Only manual operation is possible

2

Paste into Excel (cloud)

❌

Manual (Requires opening Excel in OneDrive)

3

Formatting with Office Script

✅

Get UsedRange → convert to HTML

4

Sending emails with Outlook

✅

However, as will be explained later, it failed.

⚠️ Points of difficulty and unexpected pitfalls

❌ 1. Script reference error


Attempting to access the output using outputs('HTMLconv')?['body'] results in an invalid expression.

The actual cause was a mismatch between the action name and the internal label name, as well as the reference in the expression.


📌 *Nobody told me that I needed to change my name.

❌ 2. Dates are displayed as “numbers”.


The `.getValues()` method returned by Office Script is designed to return the internal Excel value, which is the serial date, directly.

For example, "2025/06/20" becomes "45563".

Without realizing this, I was left wondering, "What are these numbers?"

 

→ I converted only the date to a string using .getCell(i, j).getValueAsJson() and was finally able to format it.

 

❌ 3. Sudden changes to Power Automate specifications

⚠️ Problem: "HTML email body pasting no longer works"

 

  • Initially, the Create HTML Table → Send an email (V2) action allowed users to paste HTML format into the email body.

  • However, a specification change in the spring of 2025 caused a phenomenon where HTML was ignored and the output was converted to plain text.

  • Microsoft hadn't released any clear release notes, and by the time we realized it, the "body HTML structure" had already broken down.

 

 

📉 Result: The practice of pasting tables into the main text has become unfeasible.

 

📎 The shift to attachment-based email, and the new challenges that have emerged.

 

comparison

VBA

This configuration

Required operations

Just press a button in Excel

Open Excel (Cloud), Power Automate, and Outlook all at once.

File operations

Completed within a single file

Spanning local storage, OneDrive, and the cloud.

Email structure

Flexible support for both the main text and attachments.

HTML is not supported. Only attachment-based operation is possible.

operational load

Minor (1 click)

Multiple stages, frequent screen switching, 2-3 times more effort


🧨 Conclusion:

"All I wanted was automation, but it became unrealistic."

  

  • What was possible with VBA—a "single-screen operation"—has transformed into **"operation spanning multiple systems"** in the cloud era.

  • It increases the amount of work and the number of errors, creating a paradox where the desire to "make things more convenient" ends up "making the process more complicated."

  • Power Automate itself can change its behavior due to specification changes, so its stable operation in the future remains uncertain.

 

✍ Finally

 

Even if a system has usable features, that doesn't necessarily mean it's easy to use.

Power Automate and Office Script may indeed be "the automation technologies of the future."

However, if you can't even reproduce what you were able to do in the past, you can't build trust in the field.

 

The greatest achievement of this series was "understanding the new system and identifying its limitations." The desire to "do everything automatically" is not a sign of weakness, but rather "the voice of someone on the ground who is seriously considering efficiency."

If we don't have an environment where those voices can be heard, wouldn't the very meaning of automation become obscured?

  

🔄 Plans for the next project (undecided)

 

  • The document will be sent as a PDF attachment, not in table format.

  • Alternatively, you can use PowerApps integration, which can be completed entirely within Excel.

 

If you're interested, please check back again.

 

 
 
 

Comments


bottom of page