jeudi 22 décembre 2022

onEdit in google sheets script is very slow. Making sure only one checkbox is selected per row

I have a google sheet workbook with four sheets that have surveys. Each survey has one question per row, with two to four choices that can be selected. Each choice has a corresponding checkbox. I need to make sure that if the user changes a selection in a row then the previously selected checkbox is unchecked. This works fine, but it's slow in unchecking the old choice. At it's very fastest it takes a couple of seconds but it can take 7 or 8 seconds sometimes. It seems to me it should take just a fraction of a second, there's not that much being done. Maybe I'm going about it the wrong way.

Here's my onEdit function and one of the functions it calls. All of the called functions are basically the same, with different numbers of rows, and they all behave about the same as far as slowness goes.

function onEdit(e) {
   
  // get event object data: sheet name, row number and column number
  const sheet = e.range.getSheet();
  const row = e.range.rowStart;
  const col = e.range.columnStart;
  const currentsheet = sheet.getSheetName()
   
  switch(currentsheet) {
    case "Adaptive Behavior":
      onEditADaptive(sheet, row, col)
    case "Social Communication":
      onEditSocComm(sheet, row, col)
    case "Behavior 6-18":
      onEditBehavior618(sheet, row, col)
    case "BRIEF-2 Parent Report":
      onEditBRIEF2(sheet, row, col)

  }
}


`
function onEditBehavior618(mySheet, myRow, myCol) {
  
  switch(myCol) {
    
        // case when column C is checked   C-E-G
        case 3:
          mySheet.getRangeList(["E" + myRow,"G" + myRow]).uncheck();
          break;
    
        // case when column E is checked
        case 5:
          mySheet.getRangeList(["C" + myRow,"G" + myRow]).uncheck();
          break;
    
        // case when column G is checked
        case 7:
          mySheet.getRangeList(["C" + myRow,"E" + myRow]).uncheck();
          break;
        
        // cell is outside of columns C, E or G
        default:
          return;
    
      }
}



I'm new to google sheets and scripts. this is the technique I found through a search, and it works fine other than being slow. I'm not sure how else to accomplish what I'm trying to do



Aucun commentaire:

Enregistrer un commentaire