Multi-currency Table Sum — Confluence Cloud Macro

#set($costCol = $parameters["costColumn"])
#set($currencyCol = $parameters["currencyColumn"])
#set($resultCurrency = $parameters["resultCurrency"].toUpperCase())

## Get exchange rates
#set($apiUrl = "https://open.er-api.com/v6/latest/${resultCurrency}")
#set($exchangeResponse = $RequestManager.get($apiUrl))
#set($rates = $exchangeResponse.rates)

#set($tableHtml = $body)

## Extract tbody content
#set($tbodyStart = $tableHtml.indexOf("<tbody>"))
#set($tbodyEnd = $tableHtml.indexOf("</tbody>"))

## Fallback if <tbody> not found
## Shows the Note pannel
#if ($tbodyStart < 0 || $tbodyEnd < $tbodyStart)
  ## Fallback if <tbody> not found
  <div style="background-color: var(--ds-background-accent-purple-subtlest, #F3F0FF); padding: 10px; border-radius: 3px;">
    <div style="white-space: nowrap;">
      <div style="display: inline-block; vertical-align: top; width: 30px; text-align: center; margin-top: 4px;">
        <ac:image ac:width="19" ac:height="19">
          <ri:url ri:value="https://api.iconify.design/mdi/note.svg?color=%23667eea" />
        </ac:image>
      </div>
      <div style="display: inline-block; width: 95%;">
        <div style="font-weight: bold; margin: 1px 0 5px 0;">
          Insert Table into macro on edit page
        </div>
        <div style="margin: 3px 0 5px 0; white-space: normal; width: 95%;">
          To calculate multy-currency sum in <b>$resultCurrency</b> insert Table into macro body with.<br>
          Based on the current settings table must have:
          <ul>
            <li>column <b>#$costCol</b> contains cost amount (numbers)</li>
            <li>column <b>#$currencyCol</b> contains currency (USD, EUR, etc.)</li>
          </ul>
        </div>
      </div>
    </div>
  </div>
  #stop ## End of processing, there is no valid inner table
#end

## If table is present and valid => proceed
#set($beforeTbody = $tableHtml.substring(0, $MathTool.add($tbodyStart, 7)))
#set($tbodyContent = $tableHtml.substring($MathTool.add($tbodyStart, 7), $tbodyEnd))
#set($afterTbody = $tableHtml.substring($tbodyEnd))

## Process rows
#set($rows = $tbodyContent.split("(?i)</tr>"))
#set($total = 0)

#foreach($row in $rows)
  #if($row.contains("<td")) ## Skip headers
    #set($cleanRow = $row.replaceAll("(?i)<tr>", ""))
    #set($cells = $cleanRow.split("(?i)</t[dh]>"))

    #set($costText = "")
    #set($currencyCode = "")
    #if($costCol <= $cells.size() && $currencyCol <= $cells.size())
      #set($costText = $cells.get($MathTool.add($costCol, -1)).replaceAll("(?s)<.*?>", "").trim())
      #set($currencyCode = $cells.get($MathTool.add($currencyCol, -1)).replaceAll("(?s)<.*?>", "").trim().toUpperCase())

      #if($costText != "" && $currencyCode != "")
        #set($rate = $rates.get($currencyCode))
        #if($rate)
          #set($cost = $MathTool.toDouble($costText))
          #set($converted = $MathTool.div($cost, $rate))
          #set($total = $MathTool.add($total, $converted))
        #end
      #end
    #end
  #end
#end

## x100 -> rount -> /100
#set ($roundedTotal = $MathTool.div($MathTool.round($MathTool.mul($total, 100)), 100))

## Build footer row (appending at the end of tbody)
#set($footerRow = "<tr><td colspan='$MathTool.add($MathTool.max($costCol, $currencyCol), -1)' style='font-weight:bold;text-align:right;'>Total ${parameters.resultCurrency}</td><td style='font-weight:bold;'><span title='USD'>$roundedTotal</span></td></tr>")

## Reassemble table
$beforeTbody
$tbodyContent
$footerRow
$afterTbody