Office Script Excel Get Newest date from Column Range

Question

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.

taller · Accepted Answer · 2024-01-22 14:14:20Z

1

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()));

edited Jan 22, 2024 at 14:14

answered Jan 19, 2024 at 19:09

taller

19.7k2 gold badges9 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andy Donegan Over a year ago

thank you, your first code, without sorting the table does not work as .flat is not recognised for some reason within Office-Script when it should be. I want to accept your answer over mine, but do not want to leave an answer up with one part not working. Thank you.

taller Over a year ago

I've tested it on Excel desktop before posted it. Unfortunately, it doesn't work on Excel App online. I've removed it.

Andy Donegan · Accepted Answer · 2024-01-17 15:14:41Z

I figured it out which feels like a work around, but this is all I can think of currently with my limited knowledge. This gets the latest date from Server time Column, Filtering Newest to Oldest, selects first cell and then coverts it into a Date object I can use to create my filter.

// Filter column Ascending
selectedSheet.getAutoFilter().getRange().getSort().apply([{ key: 1, ascending: false }], false, true);

// Get Range (First Cell only) below Column title which is Date Time Format.
var server_time_first_cell = selectedSheet.getRange("A2");

// Get Value of Cell as a Number (Excel returns a number for Date/Time)
let server_time_first_cell_time_as_number: number = server_time_first_cell.getValue() as number;

// Convert Number to Date as per Microsoft Instructions.
let server_time_as_datetime: Date = new Date(Math.round((server_time_first_cell_time_as_number - 25569) * 86400 * 1000));

// Update Time to remove 28 days
server_time_as_datetime.setDate(server_time_as_datetime.getDate() - 28);

// Create Date only string to use in Filter.
let query_date_string: String = server_time_as_datetime.toLocaleDateString();

// Filter on Column
selectedSheet.getAutoFilter().apply(selectedSheet.getAutoFilter().getRange(), 1, { filterOn: ExcelScript.FilterOn.custom, criterion1: "<" + query_date_string });

Collectives™ on Stack Overflow

Office Script Excel Get Newest date from Column Range

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related