App Script Google sheet - button for pasting data values simple form

Question

at the beginning I immediately inform you that I am totally green in appscript

I would need a script that pasted values into the appropriate sheets and searches for the appropriate criteria to paste the values into the appropriate cells

explains exactly what I mean I know that there is such a thing as a google form but it does not meet my expectations, below is created a so-called form.

cell B tells you what Shift they need to fill out the form the same cell C shows which area to fill it

D3 = current day
E3 = what a shift
F3 = what area

in column G, not all fields are filled in by Inbound or Outbound

and I would like the person filling out the form to click the Send button after clicking, the script would search for the date in the form, shift and area, then the script would enter the data into the appropriate tabs and shift

my bookmarks in each tab there is a report divided into days and data I can't describe it that's why we put a link to google sheet so you can go in and see

Google-Sheet AppScript

but i'll try to be brief I fill out the form on 30/03/2023 on the Yellow shift in the Inbound area, cells G4:G12, G14:G17, G20:G21, G26:G27 after filling them out, I click send and paste them to the "Inbound" tab shift "Yellow" and on the appropriate date

Answer 1

You can try the following code:

I took the time to paste it in your sheet for you to test the button. Let me know if that works.

function myFunction() {

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var form = spreadsheet.getSheetByName("Form");
  var inboundSheet = spreadsheet.getSheetByName("Inbound");
  var outboundSheet = spreadsheet.getSheetByName("Outbound");

  var date = form.getRange("D3").getValue().toLocaleDateString({year: 'numeric', month: 'numeric', day: 'numeric' });
  var shift = form.getRange("E3").getValue();
  var area = form.getRange("F3:G3").getValue();
  var dateFinder = inboundSheet.createTextFinder(date).matchCase(true).findNext();

  var inboundRanges = form.getRangeList(["G4:G8", "G9:G12","G14:G17","G20:G21","G26:G27"]).getRanges();
  var outboundRanges = form.getRangeList(["G4:G12","G9:G12", "G18","G22:G24","G28:G29"]).getRanges();

  switch(shift)
  {
    case "Yellow":
    if(area=="Inbound")
    {
      inboundSheet.getRange(66, dateFinder.getColumn(), inboundRanges[0].getValues().flat().length, 1).setValues(inboundRanges[0].getValues());
      inboundSheet.getRange(73, dateFinder.getColumn(), inboundRanges[1].getValues().flat().length, 1).setValues(inboundRanges[1].getValues());
      inboundSheet.getRange(80, dateFinder.getColumn(), inboundRanges[2].getValues().flat().length, 1).setValues(inboundRanges[2].getValues());
      inboundSheet.getRange(86, dateFinder.getColumn(), inboundRanges[3].getValues().flat().length, 1).setValues(inboundRanges[3].getValues());
      inboundSheet.getRange(92, dateFinder.getColumn(), inboundRanges[4].getValues().flat().length, 1).setValues(inboundRanges[4].getValues());
    }
    else
    {
      if(area=="Outbound")
      {
        for(var i=0; i<outboundRanges.length; i++)
        {
          outboundSheet.getRange(66, dateFinder.getColumn(), outboundRanges[0].getValues().flat().length, 1).setValues(outboundRanges[0].getValues());
          outboundSheet.getRange(73, dateFinder.getColumn(), outboundRanges[1].getValues().flat().length, 1).setValues(outboundRanges[1].getValues());
          outboundSheet.getRange(84, dateFinder.getColumn(), outboundRanges[2].getValues().flat().length, 1).setValues(outboundRanges[2].getValues());
          outboundSheet.getRange(88, dateFinder.getColumn(), outboundRanges[3].getValues().flat().length, 1).setValues(outboundRanges[3].getValues());
          outboundSheet.getRange(94, dateFinder.getColumn(), outboundRanges[4].getValues().flat().length, 1).setValues(outboundRanges[4].getValues());
        }
      }
    }
    break;

    case "Brown":
    if(area=="Inbound")
    {
      inboundSheet.getRange(35, dateFinder.getColumn(), inboundRanges[0].getValues().flat().length, 1).setValues(inboundRanges[0].getValues());
      inboundSheet.getRange(42, dateFinder.getColumn(), inboundRanges[1].getValues().flat().length, 1).setValues(inboundRanges[1].getValues());
      inboundSheet.getRange(49, dateFinder.getColumn(), inboundRanges[2].getValues().flat().length, 1).setValues(inboundRanges[2].getValues());
      inboundSheet.getRange(55, dateFinder.getColumn(), inboundRanges[3].getValues().flat().length, 1).setValues(inboundRanges[3].getValues());
      inboundSheet.getRange(61, dateFinder.getColumn(), inboundRanges[4].getValues().flat().length, 1).setValues(inboundRanges[4].getValues());
    }
    else
    {
      if(area=="Outbound")
      {
        for(var i=0; i<outboundRanges.length; i++)
        {
          outboundSheet.getRange(35, dateFinder.getColumn(), outboundRanges[0].getValues().flat().length, 1).setValues(outboundRanges[0].getValues());
          outboundSheet.getRange(42, dateFinder.getColumn(), outboundRanges[1].getValues().flat().length, 1).setValues(outboundRanges[1].getValues());
          outboundSheet.getRange(49, dateFinder.getColumn(), outboundRanges[2].getValues().flat().length, 1).setValues(outboundRanges[2].getValues());
          outboundSheet.getRange(55, dateFinder.getColumn(), outboundRanges[3].getValues().flat().length, 1).setValues(outboundRanges[3].getValues());
          outboundSheet.getRange(61, dateFinder.getColumn(), outboundRanges[4].getValues().flat().length, 1).setValues(outboundRanges[4].getValues());
        }
      }
    }
    break;

    case "Dark":
    if(area=="Inbound")
    {
      inboundSheet.getRange(97, dateFinder.getColumn(), inboundRanges[0].getValues().flat().length, 1).setValues(inboundRanges[0].getValues());
      inboundSheet.getRange(104, dateFinder.getColumn(), inboundRanges[1].getValues().flat().length, 1).setValues(inboundRanges[1].getValues());
      inboundSheet.getRange(111, dateFinder.getColumn(), inboundRanges[2].getValues().flat().length, 1).setValues(inboundRanges[2].getValues());
      inboundSheet.getRange(117, dateFinder.getColumn(), inboundRanges[3].getValues().flat().length, 1).setValues(inboundRanges[3].getValues());
      inboundSheet.getRange(123, dateFinder.getColumn(), inboundRanges[4].getValues().flat().length, 1).setValues(inboundRanges[4].getValues());
    }
    else
    {
      if(area=="Outbound")
      {
        for(var i=0; i<outboundRanges.length; i++)
        {
          outboundSheet.getRange(97, dateFinder.getColumn(), outboundRanges[0].getValues().flat().length, 1).setValues(outboundRanges[0].getValues());
          outboundSheet.getRange(104, dateFinder.getColumn(), outboundRanges[1].getValues().flat().length, 1).setValues(outboundRanges[1].getValues());
          outboundSheet.getRange(111, dateFinder.getColumn(), outboundRanges[2].getValues().flat().length, 1).setValues(outboundRanges[2].getValues());
          outboundSheet.getRange(117, dateFinder.getColumn(), outboundRanges[3].getValues().flat().length, 1).setValues(outboundRanges[3].getValues());
          outboundSheet.getRange(125, dateFinder.getColumn(), outboundRanges[4].getValues().flat().length, 1).setValues(outboundRanges[4].getValues());
        }
      }
    }
    break;
  };
}