lundi 19 septembre 2022

Sort a whole row based on a checkbox on appscript

hope you are doing well.

I'm trying to sort a row that contains 18 cells based on checkboxes. Each checkbox assigns a task to each person and I basically would like to group people who are working on each task.

People with tasks

As you can see in the screenshot it's difficult to identify each task. I would like that all the people who are working on the "new" task to be together and not separated and People who are working on the "Open" task to be in a group below the "New" task

Another problem I have is that I'm getting these names (column A) from a formula based on another list.

Do you think is there a way to this action works? or it's not possible? I found this code based on other values but I'm not sure if I could modify it to achieve the action I want. This is the code:

function autoSort(e) {
  
  const row = e.range.getRow()
  const column = e.range.getColumn()
  const ss = e.source
  const currentSheet = ss.getActiveSheet()
  const currentSheetName = currentSheet.getSheetName()

  if(!(currentSheetName === "Assignation" && column === 2 && row >= 2)) return

  const ws = ss.getSheetByName("Assignation")

  const range = ws.getRange (2,2, ws.getLastRow()-1,7)

  range.sort({column: 1, ascending: false})
  
}

function onEdit(e){

  const row = e.range.getRow()
  const column = e.getRange.getColumn()

  if(!(column === 1 && row >= 2)) return

  autoSort()
}

Also, this is the sheet I'm using, it's a copy so you can modify it as you want.

https://docs.google.com/spreadsheets/d/1f5Mu2SKPAYKC9UcpDOkGdtS6JeFDHcsqPMmuZyz-zWA/edit?usp=sharing

I hope anyone can help me with this. Thank you!




Aucun commentaire:

Enregistrer un commentaire