Home > Software engineering >  What function would I use to sort dates by clicking a checkbox?
What function would I use to sort dates by clicking a checkbox?

Time:11-27

I'm trying to create a spreadsheet that sorts a list of assignments by their due date simply by clicking a checkbox and marking it true. I'm very new to JavaScript and using spreadsheets, but I do have some coding background, mainly Java and Python.

My code so far is this. The checkbox is in the top left (cell A1) and I have the dates in column C (column 3). Theres a lot of unused variables and other extra things because I copied some lines from other places and posts. As of right now it does nothing when I press the checkbox. Idk what half of this does, so any advice is welcome. Just don't roast me too much plz haha

function SortbyCheck(input) {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];
 var range = sheet.getRange("A3:E");
 const DateRange = [A3:E]

 // Sorts by the values in the second column (B)
 //range.sort(3);
  if(s.getName() == "Sheet1" && r.getColumn() == 1 && r.getRow() == 1 && r.getValue() == true) {
 // Sorts descending by column B
    DateRange.sort(3);
  }
}

CodePudding user response:

Dallin, here it goes a modified function. You should set an installable trigger as stated by Ping, or you must name your function onEdit. Here you have a sample of what I understand you need:

function onEdit(e) {
 var eran = e.range
 var sheet = eran.getSheet()
 var sortingrange = sheet.getRange("A3:E");
 
 if(sheet.getName() == "Sheet1" && eran.getColumn() == 1 && eran.getRow() == 1 && eran.getValue() == true) {
   sortingrange.sort({column: 2, ascending: false}) //sorting by B descending
   eran.setValue(false) //unticks the checkbox
  }

}

CodePudding user response:

An alternative to a checkbox...

Insert transparent drawings on top of the column headers to use as buttons to activate a sorting script:

image 1

image 2

function sortDatePUlog() {
  var shPUlog = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('PU Log');
  shPUlog.getRange("B3:F3").setFontColor('#ffffff');
  shPUlog.getRange("A3").setFontColor('#f388f3');
  shPUlog.sort(1, false); //false puts newest date on top
}

function sortCustNamePUlog() {
  var shPUlog = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('PU Log');
  shPUlog.getRange("B3:F3").setFontColor('#ffffff');
  shPUlog.getRange("C3").setFontColor('#f388f3');
  shPUlog.sort(3, true);
}
  • Related