How to get data by filtering YEAR using google sheet script

Question

could I ask for your help with filtering the data? I want it to display all the information for 2024 when I input that year.

Please see below the script and the screenshot for you reference. Thank you.

function showInputYear() {
  var ui = SpreadsheetApp.getUi();
  var input = ui.prompt("Please enter your First Name.", ui.ButtonSet.OK_CANCEL);

  if (input.getSelectedButton() == ui.Button.OK) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var ws = ss.getSheetByName("Monitoring (Database)");
    var data = ws.getRange("A2:X" + ws.getLastRow()).getValues();
    var userSelectedRep = input.getResponseText().toLowerCase();

    var newData = data.filter(function (r) {
      return r[23].toLowerCase() == userSelectedRep;
    });

    var yearInput = ui.prompt("Please enter the year (e.g., 2024):", ui.ButtonSet.OK_CANCEL);

    if (yearInput.getSelectedButton() == ui.Button.OK) {
      var selectedYear = yearInput.getResponseText();
      var filteredYearData = newData.filter(function (r) {
        var date = new Date(r[3]);
        return date.toLocaleString('default', { year: 'numeric' }).toLowerCase() === selectedYear;
      });


      var selectedColumns = filteredYearData.map(function (r) {
        return [r[3], r[10], r[9], r[1], r[17], r[18], r[19], r[20], r[21], r[22]];
      });

      if (filteredYearData.length > 0) {
        var newWs = ss.insertSheet(userSelectedRep);
        var headers = ["YEAR", "PLATFORM", "TYPE OF ACCESS", "INSTITUTION", "QUANTITY/ACCESS CODE", "START DATE", "START TIME", "END DATE", "END TIME", "TOTAL TIME"];

        newWs.getRange(4, 3, selectedColumns.length, selectedColumns[0].length).setValues(selectedColumns);
        newWs.getRange(3, 3, 1, headers.length).setValues([headers]);

      } else {
        ui.alert("No matching data found for the entered year.");
      }
    } else {
      ui.alert("Year input canceled.");
    }
  } else {
    ui.alert("Operation Canceled.");
  }
}

I can't display all his record for year 2024

Answer 1

Get the filtered Data based on year

You can try this complete version:

function showInputBox() {
  var ui = SpreadsheetApp.getUi();
  var input = ui.prompt("Please enter your First Name.", ui.ButtonSet.OK_CANCEL);

  if (input.getSelectedButton() == ui.Button.OK) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var ws = ss.getSheetByName("Monitoring (Database)");
    var data = ws.getRange("A2:X" + ws.getLastRow()).getValues();
    var userSelectedRep = input.getResponseText().toLowerCase();

    var newData = data.filter(function (r) {
      return r[23].toLowerCase() == userSelectedRep;
    });

    var yearInput = ui.prompt("Please enter the year (e.g., 2024):", ui.ButtonSet.OK_CANCEL);

    if (yearInput.getSelectedButton() == ui.Button.OK) {
      var selectedYear = yearInput.getResponseText();
      var filteredYearData = newData.filter(function (r) {
        var date = new Date(r[18]);
        return date.getFullYear() == selectedYear; 
      });

      var selectedColumns = filteredYearData.map(function (r) {
        return [r[10], r[9], r[1], r[17], r[18], r[19], r[20], r[21], r[22]];
      });

      if (filteredYearData.length > 0) {
        var newWs = ss.insertSheet(userSelectedRep);
        var headers = ["PLATFORM", "TYPE OF ACCESS", "INSTITUTION", "QUANTITY/ACCESS CODE", "START DATE", "START TIME", "END DATE", "END TIME", "TOTAL TIME"];

        newWs.getRange(4, 3, selectedColumns.length, selectedColumns[0].length).setValues(selectedColumns);
        newWs.getRange(3, 3, 1, headers.length).setValues([headers]);

      } else {
        ui.alert("No matching data found for the entered year.");
      }
    } else {
      ui.alert("Year input canceled.");
    }
  } else {
    ui.alert("Operation Canceled.");
  }
}

Here is the only modified part from your code:

var yearInput = ui.prompt("Please enter the year (e.g., 2024):", ui.ButtonSet.OK_CANCEL);

if (yearInput.getSelectedButton() == ui.Button.OK) {
  var selectedYear = yearInput.getResponseText();
  var filteredYearData = newData.filter(function (r) {
    var date = new Date(r[18]);
    return date.getFullYear() == selectedYear; 
  });
}

SAMPLE DATASET:

SAMPLE OUTPUT: (entered data: John - 2025)

REFERENCE:

getFullYear()