Office Script

(0) Share

Report

Posted on by tmj1

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...
 
 

Categories:

Building flows

TimeSheetLines-Ti...

I have the same question (0)

All responses (6)

Answers (0)

rzaneti 4,249 Super User 2025 Season 2 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

David_MA 12,982 Super User 2025 Season 2 on at

Like (0)

Report

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

Was this reply helpful? Yes No
tmj1 2 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

rzaneti 4,249 Super User 2025 Season 2 on at

Like (0)

Report
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/

Was this reply helpful? Yes No
tmj1 2 on at

Like (0)

Report

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

Was this reply helpful? Yes No
rzaneti 4,249 Super User 2025 Season 2 on at

Like (0)

Report

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/

Was this reply helpful? Yes No