Skip to main content
Post

#PowerPlatformTip 150: 'JSON & HTML to Markdown with Office Scripts'

#PowerPlatformTip 150: 'JSON & HTML to Markdown with Office Scripts'

TL;DR: Convert JSON arrays and HTML into clean Markdown inside a flow with two deterministic Office Scripts, pure code, no AI and no premium capacity.

You get structured JSON from an API or SharePoint, or raw HTML from an email or web source, and you need clean Markdown for documentation, a Teams post or a wiki page. No need for AI Builder or Copilot: two small Office Scripts convert both formats deterministically, every single time. Power Automate stays the orchestrator; the scripts do the formatting.

💡 Challenge

Building Markdown inside a flow usually means nested Apply to each loops and fragile string concatenation, for JSON tables and even more so for HTML, where you’d otherwise reach for an AI action that is non-deterministic and can cost premium capacity. You want reusable steps that take JSON or HTML in and return valid Markdown out, with predictable results.

✅ Solution

Use Office Scripts as pure formatting engines. One script turns a JSON array into a Markdown table; a second turns HTML into Markdown using deterministic string processing. Power Automate passes the input via the Run script action and receives the Markdown as the dynamic content named result, no AI involved.

🔧 How It’s Done

1) Create Script A, JSON to Markdown table

  • In Excel on the web, open Automate > New Script, paste the code below and save it as JsonToMarkdown. 🔸 The object[] parameter makes the JSON show up as an input in Power Automate.

🔸 The : string return type surfaces the Markdown as the result dynamic content.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
   function main(workbook: ExcelScript.Workbook, jsonData: object[]): string {
     if (!jsonData || jsonData.length === 0) {
       return "_No data_";
     }

     const rows = jsonData as Record<string, string | number | boolean>[];
     const headers = Object.keys(rows[0]);

     // Header + separator row
     let markdown = `| ${headers.join(" | ")} |\n`;
     markdown += `| ${headers.map(() => "---").join(" | ")} |\n`;

     // Data rows (escape pipe characters so the table stays valid)
     for (const row of rows) {
       const cells = headers.map(h => (row[h] ?? "").toString().replace(/\|/g, "\\|"));
       markdown += `| ${cells.join(" | ")} |\n`;
     }

     return markdown;
   }

2) Create Script B, HTML to Markdown

  • Add a second script, paste the code below and save it as HtmlToMarkdown. 🔸 This is a deterministic string converter based on regular expressions. It handles headings, bold/italic, links, images with a src, lists, code, blockquotes, paragraphs and <br> tags. It strips wrapper tags such as <body>; nested list structure and malformed or complex HTML aren’t preserved reliably.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
   function main(workbook: ExcelScript.Workbook, htmlInput: string): string {
     if (!htmlInput) {
       return "";
     }

     let md = htmlInput;

     // 1. Drop script, style and comment blocks
     md = md.replace(/<script[\s\S]*?<\/script>/gi, "");
     md = md.replace(/<style[\s\S]*?<\/style>/gi, "");
     md = md.replace(/<!--[\s\S]*?-->/g, "");

     // 2. Headings h1–h6
     for (let level = 1; level <= 6; level++) {
       const re = new RegExp(`<h${level}[^>]*>([\\s\\S]*?)<\\/h${level}>`, "gi");
       md = md.replace(re, (_m, inner: string) => `\n\n${"#".repeat(level)} ${inner.trim()}\n\n`);
     }

     // 3. Bold and italic
     md = md.replace(/<(strong|b)[^>]*>([\s\S]*?)<\/\1>/gi, (_m, _t, inner: string) => `**${inner.trim()}**`);
     md = md.replace(/<(em|i)[^>]*>([\s\S]*?)<\/\1>/gi, (_m, _t, inner: string) => `*${inner.trim()}*`);

     // 4. Preformatted blocks and inline code
     md = md.replace(/<pre[^>]*>([\s\S]*?)<\/pre>/gi, (_m, inner: string) => `\n\n\`\`\`\n${inner.trim()}\n\`\`\`\n\n`);
     md = md.replace(/<code[^>]*>([\s\S]*?)<\/code>/gi, (_m, inner: string) => `\`${inner.trim()}\``);

     // 5. Images (with and without alt) and links
     md = md.replace(/<img[^>]*alt=["']([^"']*)["'][^>]*src=["']([^"']*)["'][^>]*>/gi, (_m, alt: string, src: string) => `![${alt}](${src})`);
     md = md.replace(/<img[^>]*src=["']([^"']*)["'][^>]*>/gi, (_m, src: string) => `![](${src})`);
     md = md.replace(/<a[^>]*href=["']([^"']*)["'][^>]*>([\s\S]*?)<\/a>/gi, (_m, href: string, text: string) => `[${text.trim()}](${href})`);

     // 6. List items and list wrappers
     md = md.replace(/<li[^>]*>([\s\S]*?)<\/li>/gi, (_m, inner: string) => `- ${inner.trim()}\n`);
     md = md.replace(/<\/?(ul|ol)[^>]*>/gi, "\n");

     // 7. Blockquotes
     md = md.replace(/<blockquote[^>]*>([\s\S]*?)<\/blockquote>/gi, (_m, inner: string) => `\n> ${inner.trim()}\n`);

     // 8. Paragraphs and line breaks
     md = md.replace(/<br\s*\/?>/gi, "\n");
     md = md.replace(/<\/p>/gi, "\n\n");
     md = md.replace(/<p[^>]*>/gi, "");

     // 9. Strip any remaining tags
     md = md.replace(/<[^>]+>/g, "");

     // 10. Decode the most common HTML entities
     const entities: Record<string, string> = {
       "&nbsp;": " ", "&amp;": "&", "&lt;": "<", "&gt;": ">",
       "&quot;": "\"", "&#39;": "'", "&apos;": "'"
     };
     md = md.replace(/&nbsp;|&amp;|&lt;|&gt;|&quot;|&#39;|&apos;/g, m => entities[m]);

     // 11. Tidy up whitespace
     md = md.replace(/[ \t]+\n/g, "\n").replace(/\n{3,}/g, "\n\n").trim();

     return md;
   }

3) Add the Run script action

  • In your flow add Excel Online (Business) > Run script and pick the workbook, then choose JsonToMarkdown or HtmlToMarkdown.

4) Pass the input 🔸 JSON: map your array to jsonData and click Switch to input entire array, otherwise Power Automate sends single elements instead of the full array.

🔸 HTML: map your HTML string (e.g. an email body) to htmlInput.

5) Use the Markdown output

  • Reference the dynamic content named result in the next action. 🔸 Write it to a .md file (Create file), post it to a Teams channel, or add it to a SharePoint page.

🎉 Result

JSON arrays and HTML both become clean, valid Markdown in a single reusable flow step, deterministically, with no AI action and no premium capacity. Run it a hundred times and you get the exact same output every time.

🌟 Key Advantages

🔸 No AI, fully deterministic: pure TypeScript, identical output on every run, nothing to review or hallucinate.

🔸 Two formats, one pattern: JSON tables and HTML content handled by the same Run script mechanism.

🔸 No premium: standard Excel Online (Business) connector, no AI Builder, no Copilot licence.

🔸 Reusable: drop the scripts into any flow for docs, Teams posts or wiki pages.

🛠️ FAQ

1. Why does my JSON arrive as single items instead of one array in the Run script action?

Power Automate passes arrays element by element by default. Click Switch to input entire array on the jsonData field to send the complete array as one object.

2. Where is the converted Markdown in my flow?

The return value appears as dynamic content named result. Ensure your main function declares a return type (: string); if you change the signature later, recreate the Run script action so the connector picks up the new output.

3. How complete is the HTML converter?

It’s a deterministic string converter based on regular expressions. It handles common tags including <br>, strips wrapper tags such as <body>, and converts images when it finds a src attribute. It doesn’t preserve nested-list structure and isn’t a general HTML parser, so complex or malformed input can lose structure. Some web APIs such as TextEncoder and Crypto aren’t available when Office Scripts run in Power Automate.

This tip is by Marcel Lehmann, Microsoft MVP (Business Applications) and founder of the Power Platform Academy, which runs free live workshops (in German). See the next free workshop dates (in German). For companies that want this implemented for them, see KMUpower: Power Platform consulting & automation for SMEs (DACH).

This post is licensed under CC BY 4.0 by the author.

Microsoft and the Power Platform product names (for example Power Apps, Power Automate, Copilot Studio) are trademarks of the Microsoft group of companies. This is an independent website. It is not operated, sponsored or endorsed by Microsoft.