Hi @all,
I am currently working on a project where I need to run an office script through Power Automate.
In the associated excel file, I have a column with a date formatted as a string.
Using an Excel formula, I want to change the format to a date value.
For this I use the following code:
// getting the date string and pull out year and month information for further usage
let date_string = selectedSheet.getRange("A2").getValue() as string;
let jahr_str = date_string.substring(6, 10);
let monat_str = date_string.substring(3, 4);
let jahr_int = parseInt(jahr_str);
let monat_int = parseInt(monat_str);
let jahr_monat = jahr_str + "_" + monat_str;
// converting the date and employee id string values into date value and number value via excel formula
selectedSheet
.getRange("AR2:AS2")
.setFormulas([
[
'=IF(tblschuiexport[Datum]<>"",DATEVALUE(tblschuiexport[Datum]),"")',
'=IF(tblschuiexport[Personalnummer]<>"",NUMBERVALUE(tblschuiexport[Personalnummer]),"")',
],
]);
// Paste result of conversion into the table
tblschuiexport
.getColumn("Datum")
.getRangeBetweenHeaderAndTotal()
.getRow(0)
.copyFrom(
selectedSheet
.getRange("AR2:AS2")
.getExtendedRange(ExcelScript.KeyboardDirection.down),
ExcelScript.RangeCopyType.values,
false,
false
);
// Set number format for date column
tblschuiexport
.getColumn("Datum")
.getRangeBetweenHeaderAndTotal()
.getRow(0)
.getResizedRange(parseInt(last_row)-1, 0)
.setNumberFormat("dd.mm.yyyy");
Problem: When I run this script in Excel, everything is fine. However, when I run the script with Power Automate, I get a lot of #VALUE errors in the date column.
I assume this is due to different language settings?! But how can I fix this - does anyone have any ideas?
For further understanding I attach two screenshots and an anonymized sample file as well as the office script file.
Thanks in advance!!
Best Regards,
Peter
Posted : 23/12/2022 6:17 am
