Though you are a new user, you will have to to learn scripting. This is somewhere to start:
Overview, Guide to Automating Tasks with Apps Script, the Extending Google Sheets page, Triggers and Event Objects and the Mozilla Javascript Tools
This script should meet your needs
function onEdit(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet()
// display Event Objects
// Logger.log(JSON.stringify(e)) // DEBUG
// create variable for the Roster sheet
var sheetname = "Roster"
// test for Roster sheet to ignore work on other sheets
if (ss.getActiveSheet().getName() != sheetname){
// Logger.log("DEBUG: this sheet is not 'Roster'")
return;
}
else{
// the sheet is the Roster sheet
// Logger.log("this is "+sheetname+" sheet")
}
// establish variables
var checkCol = 4 // column containing Checkbox
var codeColOffset = -1 // offset to 'code' column
var nameColOffset = -2 // offset to 'name' column
var targetcode = "Sick" // required value of the Jobcode
// get event object variables
var range = e.range;
var editedRow = range.getRow();
var editedCol = range.getColumn()
var editedValue = e.value;
// test for an edit to the checkbox
if (editedCol === checkCol && editedValue === "TRUE" ){
// checkbox was ticked
// Logger.log("DEBUG: Checkbox was ticked")
// get the jobcode and the name for this row
var jobcode = range.offset(0,codeColOffset).getValue()
var name = range.offset(0,nameColOffset).getValue()
// Logger.log("DEBUG: range:"+range.getA1Notation()+" row:"+editedRow+" col:"+editedCol+" value:"+editedValue+" code:"+jobcode+" name:"+name)
// define conditions to proceed
// Jobcode = 'SICK, and name isn't empty
if (jobcode === targetcode && name != null){
// there was a match
// Logger.log("DEBUG: match")
// insert a new row after the checkbox
ss.getActiveSheet().insertRowAfter(editedRow)
// Logger.log("DEBUG: inserted a new row")
}
else {
// mismatch on jobcode or name
// Logger.log("DEBUG: mismatch on jobcode or name")
return
}
}
else{
// one of the criteria failed
// Logger.log("DEBUG: no match")
}
}
Logic
- This is an Apps script.
- It is uses a "simple trigger" called
onEdit. This means that it executes whenever a user edits a cell
- note
onEdit(e) - the e captures Event Objects (data collected at the time of the edit) that you can use in your script
if (ss.getActiveSheet().getName() != sheetname){ - this is a test early in the script to rule out edits on any sheet other than "Roster"if (editedCol === checkCol && editedValue === "TRUE" ){ - this is the main test. Is the edit in Column 4, and is was the checkbox ticked (as opposed to "unticked")?if (jobcode === targetcode && name != null){- I added an extra test to make sure that there was a name and the code was "Sick", but you can delete these if you wish.ss.getActiveSheet().insertRowAfter(editedRow) this is the line that inserts the row after the edited row. The "Name" and "Jobcode" retain Dropdowns, and a checkBox is inserted into Column D.
Update - checking for multiple jobcodes
function onEdit(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet()
// display Event Objects
// Logger.log(JSON.stringify(e)) // DEBUG
// create variable for the Roster sheet
var sheetname = "Roster"
// test for Roster sheet to ignore work on other sheets
if (ss.getActiveSheet().getName() != sheetname){
// Logger.log("DEBUG: this sheet is not 'Roster'")
return;
}
else{
// the sheet is the Roster sheet
// Logger.log("this is "+sheetname+" sheet")
}
// establish variables
var checkCol = 4 // column containing Checkbox
var codeColOffset = -1 // offset to 'code' column
var nameColOffset = -2 // offset to 'name' column
// required value of the Jobcode
// if there are multiple codes, separate then with a comma
var targetcode = ["Sick","Work Comp"]
// get event object variables
var range = e.range;
var editedRow = range.getRow();
var editedCol = range.getColumn()
var editedValue = e.value;
// test for an edit to the checkbox
if (editedCol === checkCol && editedValue === "TRUE" ){
// checkbox was ticked
// Logger.log("DEBUG: Checkbox was ticked")
// get the jobcode and the name for this row
var jobcode = range.offset(0,codeColOffset).getValue()
var name = range.offset(0,nameColOffset).getValue()
// Logger.log("DEBUG: range:"+range.getA1Notation()+" row:"+editedRow+" col:"+editedCol+" value:"+editedValue+" code:"+jobcode+" name:"+name)
// define conditions to proceed
// name isn't empty and Jobcode = any of the codes in the array
// check whether there is a name
if (name != null){
// test to see if the jobcode equals any of the values listed in the array variable
// indexOf will return the index number of any match BUT -1 if there is no match
// targetcode contains a list of allowable codes
// jobcode is the value in column C
if(targetcode.indexOf(jobcode) != (-1)){
// there was a match
// Logger.log("DEBUG: match")
// insert a new row after the checkbox
ss.getActiveSheet().insertRowAfter(editedRow)
// Logger.log("DEBUG: inserted a new row")
}
}
else {
// mismatch on jobcode or name
Logger.log("DEBUG: mismatch on jobcode or name")
return
}
}
else{
// one of the criteria failed
Logger.log("DEBUG: no match")
}
}
There is nothing new under the sun; there are many precedents for this type of code. I used the answer by @AndresDuartein in StackOverflow: Allowing for multiple values in onEdit function in Google Sheets Apps Script
