0

I'm a new user to Sheets! I'm reaching out for help on a problem that I have with trying to create row. I've read a ton of blogs, and watched more Youtube videos than I care to admit without finding a solution. Here's my problem. I deal with a employee roster that changes daily due to job needs and the normal stuff like vacation and sick leave. As I fill out daily assignments, I use drop down boxes to fill the data in like names and work status codes.

Is there a way to automatically add a row below the current, that's automatically formatted the same as the previous row based off a check box, or even a response in the drop down box? Like if I give some one a sick leave code, it adds the next row so I can fill in the replacement worker for the day? I have created a sample roster Sheet for help and examples. Thank you for your time and help!!!

[Sample Roster] (https://docs.google.com/spreadsheets/d/1ptnIx0SJy59CRx0WTo9Qd8bn39CPMcnGZ4fTs2xng9U/edit?usp=sharing)

New contributor
Josh is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
4
  • Hi and welcome to WebApps. So just to clarify... IF a check box is checked AND IF the "Job Code" for that row = "Sick", then you want to insert a new row below the checked row.
    – Tedinoz
    Jan 13 at 22:21
  • No so much an "AND." I was thinking just the check mark being clicked would be simple vs the check box and the work code having to match...
    – Josh
    Jan 13 at 23:43
  • It's all about ensuring that the data is "complete" before you insert the new rule. But you can just do that if you like.
    – Tedinoz
    Jan 14 at 0:02
  • Gotchya, I think. like a check and balance? So if I clicked the box and had "Reg" work in, it wouldn't add another row?
    – Josh
    Jan 14 at 0:06
0

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

6
  • First, once again, Thank you! I'm going to spend a bunch of time reverse engineering this so I can learn. That being said, when I pasted the script in, I got an error "TypeError: Cannot read property 'range' of undefined onEdit @ Code.gs:27" Do I need to tweek something in the code?
    – Josh
    Jan 14 at 0:23
  • Not sure, but let me clarify something. You can't "Run" onEdit(e) from the Script editor. onEdit() will only run when you edit a cell in the spreadsheet.
    – Tedinoz
    Jan 14 at 0:35
  • Once I put the script into the editor, and hit the save button, does that inject the script into the sheet? I'm going to sit down tomorrow and start reading those script resources you provided. What's a little light reading...
    – Josh
    Jan 14 at 0:45
  • This is awesome!!! I figured out what I was doing wrong... newbie mistakes... @Tedinoz, could other job codes be added too, like "work comp?" Or would that complicate the coding too much and be better off deleting the required sick work code?
    – Josh
    Jan 14 at 2:19
  • Yes. Other job codes could be added. Might modify the “IF” code slightly. I’ll get back to you.
    – Tedinoz
    Jan 14 at 3:13

Your Answer

Josh is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.