I am currently working on a script in Excel online to automate date generation. My goal is to input a specific date into cell F1, such as 13/2/2023, and have G1 display the last day of the same year - 31/12/2023. The date needs to be extracted from cell A13, which contains text like: "Applied filters:PCA Costing Year Plan is 21291544 2022 1872". I want to extract the year portion (in this case 2022) and use it for the date calculation, resulting in any date from 2022 in F1 and 31/12/2022 in G1.
Here's what I've written so far, although it seems to be malfunctioning:
function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// Extract the year from cell A13
const yearString = /(\d{4})/.exec(selectedSheet.getCell("A13").getText())[1];
const year = parseInt(yearString);
// Set the date in cell F1
const date = new Date(year, 1, 13);
selectedSheet.getCell("F1").setValue(date);
// Set the last day of the year in cell G1
const lastDayOfYear = new Date(year, 11, 31);
selectedSheet.getCell("G1").setValue(lastDayOfYear);
}