Taking user input to Google Sheets using Apps Script and process with queries

Question

I created a spreadsheet for reporting students attendance that contains 8 sheets (each sheet named as a subject code). After completing a particular class, I go to the specific sheet (subject) and select all the rows of that particular date and press the button AfterClass-->Process Data (sorting, removing duplicates and protecting) using Google Apps Script/Macros. All working fine.

Now I created a DASHBOARD and I want that a teacher can do everything from the dashboard rather than going to individual sheet (subject). S/he can give two inputs - subject (sheetname) and Date from the Dashboard and then automatically process these specific dataset of that Sheet (Not all data of the sheet). Please note that date is in Column A and subject-code in Column F. The code I wrote as follows:

  function AfterClass() {
      var spreadsheet = SpreadsheetApp.getActive();
  //Sorting and removing duplicates
  var height = spreadsheet.getActiveSheet().getActiveRange().getHeight();
  spreadsheet.getCurrentCell().offset(0, 0, height, 6).activate()
  .sort({column: spreadsheet.getActiveRange().getColumn() + 2, ascending: true});
  spreadsheet.getActiveRange().removeDuplicates([spreadsheet.getActiveRange().getColumn() + 2]).activate();

  //Protecting data finally
  //var lastRow = spreadsheet.getLastRow();
  var timeZone = Session.getScriptTimeZone();
  var stringDate = Utilities.formatDate(new Date(), timeZone, 'dd/MM/yy HH:mm');
  var me = Session.getEffectiveUser();
  var description = 'Protected on ' + stringDate + ' by ' + me;
  var protection = SpreadsheetApp.getActiveSheet().getActiveRange().protect().setDescription(description);
  //protection.setDomainEdit(false);   
  protection.addEditor(me);
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  }      

//Removing blank spacess in between data
      var sheet = SpreadsheetApp.getActiveSheet();
      var rows = sheet.getDataRange();
      var numRows = rows.getNumRows();
      var values = rows.getValues();
      var rowsDeleted = 0;
      for (var i = 0; i <= numRows - 1; i++) {
        var row = values[i];
        if (row[1] == '') {
          sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
          rowsDeleted++;
        }
      }   

     //For Double periods in a class
          //var ss = SpreadsheetApp.getActiveSpreadsheet()
          //var database = SpreadsheetApp.openById("xxx");
          //var source = ss.getSheetByName('yyy');
          var dataToCopyRng = SpreadsheetApp.getActiveSheet().getActiveRange(); //Gets range object of all data on source sheet
          var dataToCopy = dataToCopyRng.getValues(); //Gets the values of the source range in a 2 dimensional array
          var copyToSheet = SpreadsheetApp.getActiveSheet();
          var copyData = copyToSheet.getRange(copyToSheet.getLastRow()+1,1,dataToCopy.length,dataToCopy[0].length).setValues(dataToCopy); 

      //Calculate class attendance and signed
      var height2 = spreadsheet.getActiveSheet().getActiveRange().getHeight();   
      SpreadsheetApp.getActiveSheet().getCurrentCell().offset(2*height2,1).activate();
      SpreadsheetApp.getActiveSheet().getCurrentCell().setRichTextValue(SpreadsheetApp.newRichTextValue()
      .setText(height2 + ' Students, SIGNED')
      .setTextStyle(0, 12, SpreadsheetApp.newTextStyle()
      .setBold(true)
      .build())
      .build());
      spreadsheet.getCurrentCell().offset(0, -1, 1, 6).activate();
      spreadsheet.getActiveRangeList().setBackground('#e6b8af');
      //.setBackground('#d9d9d9')            

    }    

  [dashboard][1]


  [1]: https://i.stack.imgur.com/cMtHC.png

Answer 1

How to run your script from Dashboard after selecting the specified sheet and time

• Modify your function in a way that it takes input from the cells A2 and C2 and D2 from Dashboard
• Replace all instances of getActiveSheet() through getSheetByName(name), whereby name is your input from A1
• Replace all instances of getActiveRange() through sheet.getRange(), whereby you define the range as as subrange as defined by the dates you retrieved
• In order to find the first occurrence of the start date and the last occurrence of the end date - use the methods indexOf() and lastIndexOf()
• Make sure that your date notation in Dashboard is the same as in the sheets for correct functionality

Sample:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('After Class')
  .addItem('Process Data', 'AfterClass')
  .addToUi();
}

function AfterClass() {
  var spreadsheet = SpreadsheetApp.getActive();
  var dashboard = spreadsheet.getSheetByName("Dashboard");
  var sheetName = dashboard.getRange("A2").getValue();
  //retrieve the start date to use as desired
  var startDate = dashboard.getRange("C2").getDisplayValue();
  var endDate = dashboard.getRange("D2").getDisplayValue();
  var sheet = spreadsheet.getSheetByName(sheetName);
  //chose the range within the specified dates, for this first locate the date column
  var startRow = 2;
  var dateColumn = sheet.getRange(startRow,1,sheet.getLastRow(), 1);
  var dates = dateColumn.getDisplayValues().flat();
  var firstRow = dates.indexOf(startDate)+startRow;
  var lastRow = dates.lastIndexOf(endDate)+startRow;
  //now get the range between (and including) those rows
  var range = sheet.getRange(firstRow, 1, lastRow-firstRow+1, sheet.getLastColumn());
  //Sorting and removing duplicates
  // You need to specify by which column you want to sort your data, in this sample it it column 3 - that it column C
  var column = 3;
  range.sort({column: column, ascending:true});
  range.removeDuplicates([column]);
  //now delete empty rows if any
  for (var i = range.getHeight(); i >= 1; i--){
    if(range.getCell(i, 1).isBlank()){
      sheet.deleteRow(range.getCell(i, 1).getRow());
    }
  }
  //Protecting data 
  var timeZone = Session.getScriptTimeZone();
  var stringDate = Utilities.formatDate(new Date(), timeZone, 'dd/MM/yy HH:mm');
  var me = Session.getEffectiveUser();
  var description = 'Protected on ' + stringDate + ' by ' + me;
  var protection = range.protect().setDescription(description)
  //protection.setDomainEdit(false);   
  protection.addEditor(me);
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  }
}

Important

The sample above will work if your sheet is sorted by dates (ascending) - as you specified in the comments. However, once the data is sorted by column C and not dates anymore it might not work as intended.

Sidenote:

From your code I understand that you recorded it as a macro rather than writing it from scratch (visible by the (unnecessary) calls of activate()).

I very much recommend you to take some time to study Apps Script in roder to understand your code in depth and be able to perform modificaitons and adjustments according to your needs.

There is the basic tutorial for Apps Script in general, samples and explanations for Google Sheets in specific and the references for all available Apps Script methods, whereby most methods feature a code sample.