Replicate values in other cells on same sheet if box is checked

[Tech Dee ECE]

I have a spreadsheet that allows volunteers to call and request cases to work. Once they call, a lead volunteer will simply enter their email, a date and how many cases they want assigned to them. 

Each sheet in the spreadsheet is prepopulated with an avg of 200 cases (case information starts in column F of every sheet and there are over 50 sheets.) Volunteers can request 1 to 50 cases at any given time. Currently, I have it set up where the lead will choose the volunteer from a drop down and there's a script that will email. However, it is cumbersome if the volunteer wants the max cases.  The lead has to select the name too many times.

I have 2 working functions: 

1. Function: f_boxesChecked captures 1 row for each volunteer per day.  It simply has a checkbox to email to Agent, Agent's name,  and quantity (columns B-D). It is entered into the 1st available case. Each agent will get 1 row entered per day. At the end of the day, the last lead volunteer will send all the emails out (via a script that works when I manually enter the data. I do not need help with that)
   1. Problem 1:  I can see the correct data in the log, but I am unable to use setValue or setValues nor copyTo and it show up on the sheet. 
   2. Problem 2: Since I want the records to autogenerate based on the quantity, I don't know how to loop it.

function f_boxesChecked() {  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var rows = SpreadsheetApp.getActiveSheet().getRange("B3:D").getValues();

  var headers = rows.shift();

  var lr =f_lrInRange(ss,"B1:D");

  rows.forEach(function(data) {

    if(data[0]) {

      var qty= data[1]-1;

     Logger.log(lr);

     Logger.log(data);

    //return (data);

     } 

  });

}

Since the spreadsheets are prepopulated with everything except agent data,  I have successfully created a function that captures the last row within the range B1:D It is called f_lrInRange. I know I need that piece of information to tell the loop where to start inserting the records. 

function f_lrInRange(sheet,rangeString){

  var rng = sheet.getRange(rangeString).getValues();

  var lrindex;

    for

    (var i = rng.length-1;i>=0;i--){

      lrindex = i;

       if(!rng[i].every(function(c){return c == "";})){

   break;

   }

  

   }

  return lrindex + 1;

  }

Please help. I have attached the link to the spreadsheet. I would greatly appreciate any help at all.  On the spreadsheet, Sheet-Current Process shows what a blank sheet looks like when it is assigned to a lead volunteer. Sheet-Desired Process is what I would like to happen. 

https://docs.google.com/spreadsheets/d/1leV0WrB2KjHmP2M_kacPxJ5rDAovKH1dWPUwuo7b9qQ/edit?usp=sharing

Thanks

[Braja Patnaik]

Most of the time without having the broad view of what the system is supposed to do, providing a solution is not prudent. Still you may consider the below or discard if it does not suit the requirement.

A much simpler and cleaner solution would be to have another sheet (lets call it qty-assignment sheet)  where the lead selects the volunteer name and the no of cases that the volunteer wants. It could be defaulted to 1. Once the lead has selected all the volunteer names and no of cases for each volunteer, then s/he clicks on a button, which will trigger a script.   The script, thus triggered,  reads the rows of information from the qty-assignment sheet and update the actual case assignment sheet and send out emails! I am not sure why only 200 cases are there per sheet and why 50 sheets are maintained. There could be some valid reasons behind it. But if you could consolidate it all in some way to one sheet, the proposed solution will work fine.

[Tech Dee ECE]

Thank you for your input. The cases are broken down into 50+ sheets based on geography. We tried 1 but there are also several lead volunteers. This was a way to consolidate the data but have specific leads responsible for their cases geographically. 

I am hoping there is a workable solution mainly because of the disability (with their hands) to reduce the amount of typing they have to do. That is why the sheets are prepopulated for them.  

Even if it isn't optimal, can you think of a way to populate the existing case sheet via a customized menu item?

[Braja Patnaik]

If sheets are made geographically, then geography/region can be used as the first selection in the input sheet (new sheet that I have proposed).
Then the lead selects the volunteer name , the date and the number of cases that the volunteer wants to take up.

With this information, the rest of the details (EmailAddres,  SendEmail. Qty. Agent. Date. Case,  Status)  in the case-assignment sheet can be auto-populated and email also triggered.

I have 1 question: Does the lead select use any criteria to assign a case to a volunteer? Or the lead can assign any case to a volunteer as long as it is less than equal to the qty that the volunteer has signed up for? If there is no criteria, then the solution is quite straight forward. 

Now, this can also be done through a menu item. But to create a drop down with a menu item, I think you will have to use the html sidebar. Doing the drop down on another sheet itself will be much easier I think.

[Tech Dee ECE]

The lead does not use any criteria, it is 1st come 1st assigned. Also the geography is user defined. They have their own version of geography/region. 

Currently, the lead can assign any number of cases to a volunteer and it is email if the box is checked. The emails are sent through a menu item that runs the script.  My only issue is I have older users with disabilities, so, manually choosing the volunteer for each row is cumbersome. I'm afraid a different sheet or sidebar would be more work as well.  But I'm willing to use whatever works.

So, would you share how it would be simpler with a different sheet or sidebar versus creating an app script to update 3 columns per row for the qty assigned? I'm truly trying to make it simpler for them.  I've never seen a sidebar in action, so I can't visualize it. I currently have several customized menu items for the users so they are used to clicking on the menu and it 'doing things' for them. With this population, I'm trying to make it very simple for them.