Skip to main content

Notifications

Power Automate - Building Flows
Suggested answer

Office Script

(0) ShareShare
ReportReport
Posted on by 2
Hi
 
I have this Office Script:
function main(workbook: ExcelScript.Workbook) {
  let selectedSheet = workbook.getActiveWorksheet();
  // Set format for range B:B on selectedSheet
  selectedSheet.getRange("B:B").setNumberFormatLocal("0,00");
  // Set format for range N:T on selectedSheet
  selectedSheet.getRange("N:T").setNumberFormatLocal("0,00");
  // Replace "." with "," on selectedSheet
  selectedSheet.replaceAll(".", ",", {completeMatch: false, matchCase: false});
}
 
When run in Excelapp or ExcelOnline, it works perfectly, but when i run it through Power Automate, it does not work..
 
I have tried some of the troubleshooting hints in this link, but simply cannot figure out how to get the script working as intended...
 
 
  • rzaneti Profile Picture
    rzaneti 3,427 on at
    Office Script
    Hi @tmj1,
     
    I'm glad that it partially solved the problem!
     
    I made a few tests since yesterday to try to solve this text format issue, and my conclusion is that Excel doesn't actually follow a clear pattern when applying the number formats with Office Scripts. Sometimes it worked for me, sometimes it didn't, and with several different script commands. 
     
    My recommendation: if you have some flexibility to edit this file template, add new columns to simply use a =NUMBERVALUE formula, passing the data from your transformed columns as input. This formula takes any number formatted as text and convert it to a numeric type. I already tried to pull the cells values with Office Scripts and then overwrite it with the formula, but it also didn't work from my end. 
     
    If you don't have flexibility to change the worksheet template, maybe you can recreate this sheet in a new file, where no formats are applied by default. This approach is much more time consuming than keeping restricted to a single file, but it can also be achieved with Power Automate. 
     
    Let me know if it works for you or if you need any additional help!


    -------------------------------------------------------------------------
    If this is the answer for your question, please mark the post as Accepted Answer.
    If this answer helps you in any way, please give it a like.

    http://digitalmill.net/
    https://www.linkedin.com/in/raphael-haus-zaneti/
  • tmj1 Profile Picture
    tmj1 2 on at
    Office Script
    Hi rzaneti
     
    Thank you very much. Your script almost does the job. 
     
    Only thing is that numbers which do not have a zero (0) after the comma are not formatted as numbers - do you have a suggestion?
     
     
    Thank you so much for your help so far. 
    BR Tom
  • Suggested answer
    rzaneti Profile Picture
    rzaneti 3,427 on at
    Office Script
    Hi,
     
    I ran a few tests from my end and got a script that is working fine when ran from Power Automate:
     
     
    Here is the script:
    function main(workbook: ExcelScript.Workbook) {
        let selectedSheet = workbook.getActiveWorksheet();
        // Set format for range B:B on selectedSheet
      formatColumn(selectedSheet.getRange("B:B")) //repeat it for all columsn that must be formatted
      formatColumn(selectedSheet.getRange("N:N"))
      formatColumn(selectedSheet.getRange("O:O"))
      formatColumn(selectedSheet.getRange("P:P"))
    }
    
    function formatColumn(rng:ExcelScript.Range){
      rng.setNumberFormatLocal("0.00")
      let strValues = rng.getUsedRange().getValues().map(e => [e.toString()])
      
      rng.getUsedRange().setValues(strValues)
      rng.replaceAll(".", ",", { completeMatch: false, matchCase: false })
    }
     
    You will just need to repeat the formatColumn() line in the function main for all of your columns individually (I intentionally formatted only up to col P to test the formatting):
     
     
    The script performance can be improved to ensure a faster run when it is called from Power Automate, since it took 9 seconds during my test. I was lazy to do this improvements, but I can support you with it later if needed :)
     
    Let me know if it works for you or if you need any additional help!


    -------------------------------------------------------------------------
    If this is the answer for your question, please mark the post as Accepted Answer.
    If this answer helps you in any way, please give it a like.

    http://digitalmill.net/
    https://www.linkedin.com/in/raphael-haus-zaneti/
  • tmj1 Profile Picture
    tmj1 2 on at
    Office Script
    Hi
    Thanks for the replies.
    Script works perfectly when not run via Power Automate. 
     
    Tried Run Script from Sharepoint Library, and unfortunatelly it still does not work as intended. 
     
    it results in this formatting:
     
    Instead of this (which is the result when run i ExcelApp og ExcelOnline):
     
    Thank you.
  • Suggested answer
    David_MA Profile Picture
    David_MA 9,039 on at
    Office Script
    Since you don't show your flow or provide the error message you are getting (if you are getting one), I have limited knowledge of your issue. Which action are you using to run the script:
     
    If you are using Run script, export the script to a *.osts file and use the second action, which tends to be more reliable.
     
    This has instructions for saving the script as a file: Office Scripts in Excel - Office Scripts | Microsoft Learn
     
  • rzaneti Profile Picture
    rzaneti 3,427 on at
    Office Script
    Hi, 
     
    If you are trying to replace the "." for "," after formatting your columns, this script will not achieve it, since the replaceAll method searches for texts only and your  columns are currently formatted as text.
     
    I can help you to fix the script to transform the columns in text and then perform the replace, but it will prevent you to make any calculations with these values in the future. If this limitation is not a problem, please provide a few examples on how the final output for your columns should look like and I will be back with the script. 

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,567

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,907

Leaderboard