2023-04-23

Range.find is not finding date from Date object

I have created a script in excel (typescript) that works all fine locally but when I run it in the Power Automate Cloud I receive the following error: Cannot read property 'getOffsetRange' of undefined clientRequestId.

The purpose of the script is to find, in a table with dates, the date corresponding to the first day of the current month and write in the cell next to it.

function main(workbook: ExcelScript.Workbook) {
    // Get the active worksheet.
    let sheet = workbook.getWorksheet("Blad1");
    let escp_tab = sheet.getTable("table_name");

    // takes the day of today
    let date = new Date();
    // get the first day of the month
    let firstDay = new Date(date.getFullYear(), date.getMonth(), 1);
    // format the date in DD/MM/YYYY and makes it a string
    const formattedDate = firstDay.toLocaleDateString('en-GB', {
        day: 'numeric', month: 'numeric', year: 'numeric'
    }).replace(/ /g, '/');

    //sets the range of find to the coulmn Year of the table
    let range = escp_tab.getColumnByName("Year").getRange();
    //find in the specified range the first cell with value formattedDate    
    let cellToFind = range.find(formattedDate, { completeMatch: true });
    //define the cellToFill as the one on the same row and one column to the right of cellToFind
    let cellToFill = cellToFind.getOffsetRange(0,1);
    //sets the value of the cellToFill
    cellToFill.setValue("250");
}

I have already tried to save the excel file again or create a new one from scratch, but none of these two solution seems to be fixing the bug.



No comments:

Post a Comment