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();
.