How do I fix this script in Google Apps Script to create a button regarding Date Range?

Question

Is it possible to request specific months of data after I enter the name? For example, after I input "September 2024" will it display all the records for that month?

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 selectedColumns = newData.map(function (r) {
    return [r[10], r[9], r[1], r[17], r[18], r[19], r[20], r[21], r[22]];
  });


  if (newData.length > 0){
  var newWs = ss.insertSheet(userSelectedRep);
  newWs.getRange(3, 3, selectedColumns.length, selectedColumns[0].length).setValues(selectedColumns);
  var headers = ["PLATFORM", "TYPE OF ACCESS", "INSTITUTION", "QUANTITY/ACCESS CODE", "START DATE", "START TIME", "END DATE", "END TIME", "TOTAL TIME"];
  newWs.getRange (3, 3, 1, headers.length).setValues([headers]);
  } else {
    ui.alert ("No Matching data found for the entered name.");
  } 
  
  } else {
    ui.alert("Operation Canceled.");
  }
}

After I enter the name, I will be directed to the next button where I can input the months

Answer 1

Using Prompts to Get a Filter Parameters

You can try the code below, which is a modified version that includes a second prompt for the month and year and It will create a new sheet with the filtered results.

Code:

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 monthInput = ui.prompt("Please enter the month and year (e.g., September 2024):", ui.ButtonSet.OK_CANCEL);

    if (monthInput.getSelectedButton() == ui.Button.OK) {
      var selectedMonth = monthInput.getResponseText();
      var filteredMonthData = newData.filter(function (r) {
        var date = new Date(r[18]);
        return date.toLocaleString('default', { month: 'long', year: 'numeric' }) === selectedMonth;
      });

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

      if (filteredMonthData.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 month.");
      }
    } else {
      ui.alert("Month input canceled.");
    }
  } else {
    ui.alert("Operation Canceled.");
  }
}

Sample Output:

Reference/s:

filter