Office Script Excel Get Newest date from Column Range

Issue

I am trying to get the Newest date in column A which is formatted as date time.

Currently I have the following Script which will set a filter on Column A and filter out anything newer than 28 days.

Somebody might run this report a week after downloading the sheet so the only way I have currently to find the date the report was fun is by the Server Time, if I can find the latest date in the Server Time column I can then grab that date and do my calculation based off that date and not new Date() which I perform below.

 let selectedSheet = workbook.getActiveWorksheet();

 let d: Date = new Date(); // Here I would like to be getting the latest date from Column A
  d.setDate(d.getDate() - 28);
  let date_string: String = d.toLocaleDateString();

  selectedSheet.getAutoFilter().apply(selectedSheet.getAutoFilter().getRange(), 1, { filterOn: ExcelScript.FilterOn.custom, criterion1: "<" + date_string });

This is my first morning using office-scripts, from googling I am seeing it looks like I might have to grab the full column range and then filter it by newest-oldest and then take Cell A2 value ?

I can grab the full column range, but I could not find a way how to just setup a standard filter on newest-oldest.

Any help/direction and or links to beginner documentation would be really appreciated.

Thanks in Advance.

Solution

• Get the max value without sorting the data table

• Loop through the array to find the max value

function main(workbook: ExcelScript.Workbook) {
  let sheet = workbook.getActiveWorksheet();
  let lastRow = sheet.getRange("A:A").getLastCell().getRangeEdge(ExcelScript.KeyboardDirection.up).getRowIndex()+1;
  // console.log(lastRow)
  let range = sheet.getRange("A2:A" + lastRow);
  let values = range.getValues();
  let maxValue = Number.MIN_VALUE;
  for (let i = 0; i < values.length; i++) {
    let cellValue = values[i][0];
    if (typeof cellValue === "number" && !isNaN(cellValue)) {
      maxValue = Math.max(maxValue, cellValue);
    }
  }
  console.log("The max value in col A is：" + maxValue);
}

• btw, if you are using Excel desktop app, flat is another option.

let maxValue = Math.max(...values.flat()));

Answered By - taller