google-sheetsgoogle-apps-script

How to get data by filtering YEAR using google sheet script


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.");
  }
}

enter image description here

enter image description here

I can't display all his record for year 2024

enter image description here


Solution

  • 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:

    enter image description here

    SAMPLE OUTPUT: (entered data: John - 2025)

    enter image description here

    REFERENCE:

    getFullYear()