Google Script required to copy and save data from one tab to another, then clear original tab cells – Google Sheets [closed]

I have next to no code experience and have been set this task – any help would be much appreciated.

I have a google sheet with two tabs currently, one being a MasterTrackingSheet and the other labelled Template.

Within the Template sheet I have a column of variables set up such as Account Name, Account ID, Business Manager Name, Budget, Date, Tasks etc. Note that for the variable Account Name there is a drop down list associated that autofills the next variable Account ID and for the Task variable there is a drop down list of tasks for which multiple can be selected. Within the MasterTrackingSheet I have the exact same variables as the Template sheet however I have them as headings along the top row of the sheet.

I wish to write a script that facilitates the process of individual business managers filling out the template tab and submitting their info which then copies this data over to the MasterTrackingSheet to be saved in the next available row within this tab. Once the data has been copied over the Template tab then wipes the data out against each variable so the next Business manager may take their turn entering their info.

Any help with this task would be much appreciated – I spent my last friday googling around trying to look for code online that I could use however my lack of experience within Google Scripts and Java(?) made it hard for my to repurpose others code for my task. For reference I will attach images of the two sheets I have been referencing so hopefully it makes it more clear what I wish to happen.

Thanks again!

Image of Master Sheet for reference

Image of Template Sheet for reference

Answer

This should get you started. Do not leave any spaces in the headers or in the items in column one of the template sheet. Also the items in column 1 of the template sheet have to match exactly with the column headers in the MasterTrackingSheet (note: they don’t in your example).

function saveAcctInfo() {
  const ss=SpreadsheetApp.getActive();
  const tsh=ss.getSheetByName('Sheet1');//Template
  const msh=ss.getSheetByName('Sheet2');//MasterTrackingSheet
  const labels=tsh.getRange(1,1,getColumnHeight(1,tsh,ss)).getValues().flat();
  const items=tsh.getRange(1,2,getColumnHeight(2,tsh,ss)).getValues().flat();
  const hdrA=msh.getRange(1,1,1,msh.getLastColumn()).getValues().flat();
  let hObj={};
  hdrA.forEach(function(h,i){hObj[h]=i+1;});
  let dObj={lA:[]};
  var ll;
  items.forEach(function(item,idx){
    let l=labels[idx];
    if(l) {
      if(!dObj.hasOwnProperty(l)) {
        dObj.lA.push(l);
        dObj[l]={value:[item]};
        ll=l;
      }
    }else{
      dObj[ll].value.push(item);
    }
  });
  let row=[];
  hdrA.forEach(function(h,i){
    row.push(dObj[h].value.filter(function(e){return e;}).join('n'));
  });
  if(row.length>0) {
    msh.appendRow(row);//writes row to master sheet
  }
}

This probably doesn’t cover all situations of inconsistencies with data entry so some development on your part is expected. I presume that you can handle resetting the template sheet and creating the appropriate menu items.