Home > Blockchain >  Copying & pasting drop down lists clears cells
Copying & pasting drop down lists clears cells

Time:02-16

So I'm quite new to script, and I've got some working dynamic dependent drop down lists. When I clear a cell in column A, it clears the cell directly to the right of it, which is great.

However, when I copy and paste a row, it clears the cell to the right of column A. My question is: Is there a way to make a row "copy & paste-able", so it doesn't clear column B when pasting?

Here are some images of what I'm describing: [1]: https://i.stack.imgur.com/F0xOg.png [2]: https://i.stack.imgur.com/geHuu.png

Lastly, here's the script that I'm using:

function onEdit(){
    var tabLists = "Exercise Index";
    var spreadsheet = SpreadsheetApp;
    var activeSheet = spreadsheet.getActiveSpreadsheet().getActiveSheet();
    var data = spreadsheet.getActiveSpreadsheet().getSheetByName(tabLists);
    
    var activeCell = activeSheet.getActiveCell();
    
    if(activeCell.getColumn() == 1 && activeCell.getRow() > 3 && activeSheet.getSheetName().includes("Week")){
      
      activeCell.offset(0, 1).clearContent().clearDataValidations();
      
      var makes = data.getRange(1, 1, 1, data.getLastColumn()).getValues();
      
      var makeIndex = makes[0].indexOf(activeCell.getValue())   1;
      
      if(makeIndex != 0){
      
          var validationRange = data.getRange(2, makeIndex, data.getLastRow());
          var validationRule = spreadsheet.newDataValidation().requireValueInRange(validationRange).build();
          activeCell.offset(0, 1).setDataValidation(validationRule);
    
      }  
        
    }
    
  }

Copy of the google sheet: https://docs.google.com/spreadsheets/d/1OrDTJiAlVJRU5tKCA55CoVgnT83_k8n_yz9BkWYpZ_0/edit?usp=sharing

Thanks in advance!

CodePudding user response:

From your replying, I thought that the reason for your current issue is due to activeCell.offset(0, 1).clearContent().clearDataValidations(). When your script is run, the right side cell of the active cell is cleared. So, in your script, how about the following modification?

From:

activeCell.offset(0, 1).clearContent().clearDataValidations();

To:

activeCell.offset(0, 1).clearDataValidations();

Or, remove activeCell.offset(0, 1).clearContent().clearDataValidations();.

  • Related