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.sstatic.net/cMtHC.png
A2 and C2 and D2 from DashboardgetActiveSheet() through getSheetByName(name), whereby name is your input from A1getActiveRange() through sheet.getRange(), whereby you define the range as as subrange as defined by the dates you retrievedindexOf() and lastIndexOf()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.