Power Platform Community Forum Thread Details

Hello everyone,

I am working on a flow that searches for birthdays in an Excel list. I am using a script in Office Script for this:


function main(workbook: ExcelScript.Workbook, zweiWochen: boolean) {
    // Vergleicht Geburtstage mit heutigem Datum, wenn heute oder in zwei Wochen ein Geburtstag ist, wird der zurückgegeben.
    let sheet       = workbook.getActiveWorksheet();   
    let today       = new Date(Date.now());
    let todayMonth  = today.getMonth();
    let todayDay    = today.getDate();
    let range       = sheet.getCell(0,0).getSurroundingRegion();
    let table       = sheet.addTable(range, true);

    let tableValues = table.getRangeBetweenHeaderAndTotal().getValues();
    let col         = table.getColumnByName("Geburtsdatum").getIndex();

    let azubis: AzubiGeb[] = [];

    for (let i = 1; i <= tableValues.length; i++){
        let excelDate       = sheet.getCell(i, col).getValue() as number;
        let compDate = new Date(Math.round((excelDate - 25569) * 86400 * 1000));

        if(zweiWochen){
            compDate.setDate(compDate.getDate() + 14);
        }

        let compDateMonth = compDate.getMonth();
        let compDateDay = compDate.getDate();
       

        if(compDateMonth == todayMonth && compDateDay == todayDay){            
            let currentRow = tableValues[i-1];
            let currentAzubi: AzubiGeb = {
                Beruf: currentRow[table.getColumnByName("Beruf").getIndex()] as string,
                AzubiID: currentRow[table.getColumnByName("Azubi-ID").getIndex()] as number,
                Einstellort: currentRow[table.getColumnByName("Einstellort").getIndex()] as string,
                BestellunterlagenInfo: currentRow[table.getColumnByName("Bestellunterlagen-Info").getIndex()] as string,
                FK: currentRow[table.getColumnByName("Manager/FK BU").getIndex()] as string,
                Nachname: currentRow[table.getColumnByName("Name").getIndex()] as string,
                Vorname: currentRow[table.getColumnByName("Vorname").getIndex()] as string,
                Strasse: currentRow[table.getColumnByName("Straße").getIndex()] as string,
                PLZ: currentRow[table.getColumnByName("PLZ").getIndex()] as string,
                Stadt: currentRow[table.getColumnByName("Stadt").getIndex()] as string,
                Mail: currentRow[table.getColumnByName("Mailadresse").getIndex()] as string,
                Tel: currentRow[table.getColumnByName("Telefonnummer").getIndex()] as string,
                Geburtsdatum: currentRow[table.getColumnByName("Geburtsdatum").getIndex()] as number,
                Alter: currentRow[table.getColumnByName("Alter").getIndex()] as number
            };

            azubis.push(currentAzubi);
        }
    }
    table.convertToRange();

    console.log(azubis);

    return azubis;
}

interface AzubiGeb{
    "Beruf": string;
    "AzubiID": number;
    "Einstellort": string;
    "BestellunterlagenInfo": string;
    "FK": string;
    "Nachname": string;
    "Vorname": string;
    "Strasse": string;
    "PLZ": string;
    "Stadt": string;
    "Mail": string;
    "Tel": string;
    "Geburtsdatum": number;
    "Alter": number;
}

When I run the script in Excel, I get correct values. When I run it via Power Automate, only the age from the "Alter" column appears for all values.

Result in Office Script (from console.log(azubis)):

(1) [Object]
1. ▶0: Object
  1. Beruf: "Testberuf"
  2. AzubiID: "200265"
  3. Einstellort: "Hannover"
  4. BestellunterlagenInfo: "Testabteilung"
  5. FK: "mustermann,max"
  6. Nachname: "Mustermann"
  7. Vorname: "Max"
  8. Strasse: "Musterstr. 13"
  9. PLZ: 30560
  10. Stadt: "Hannover"
  11. Mail: "testemail@outlook.com"
  12. Tel: 123456789
  13. Geburtsdatum: 38211
  14. Alter: 26

Retrurn value in Power Automate:

[
  {
    "Beruf": 26,
    "AzubiID": 26,
    "Einstellort": 26,
    "BestellunterlagenInfo": 26,
    "FK": 26,
    "Nachname": 26,
    "Vorname": 26,
    "Strasse": 26,
    "PLZ": 26,
    "Stadt": 26,
    "Mail": 26,
    "Tel": 26,
    "Geburtsdatum": 26,
    "Alter": 26
  }
]