I am trying to create a sheet that has a database mastersheet and 8 different locations.

so everytime I submit a form to the database, I want my code to read the HUB location of the submitted form. and copy that details of the submitted form to relevant sheet. this is what I have so far.

basically it will be easier if I describe the flow:

1. user enter relevant details
2. form submits it to the database sheet
3. code then see which hub it is for then copy the details to the relevant sheet but keeps a copy in the database mastersheet.

I hope this makes sense?

CodePudding user response：

from what I can understand you want the answer to the form submitted to the main sheet then once the answer is submitted you what it to be sent to the relevant sheet

for that i would create a form submit that places the answer to the sheet

  const SOURCE_FORM_ID = link; // Change according to your needs

 {
  const form = FormApp.openById(SOURCE_FORM_ID);
  ScriptApp.newTrigger("onFormSubmitTrigger")
    .forForm(form)
    .onFormSubmit()
    .create();
  }



function onFormSubmitTrigger(e) {
  const targetSpreadsheet = SpreadsheetApp.openById("insert sheet id");
  const targetSheet = targetSpreadsheet.getSheetByName("C");
  if (targetSheet.getLastRow() === 0) { // Add headers if they don't exist yet
    const itemTitles = e.source.getItems().map(item => item.getTitle()); // Get item titles
    itemTitles.unshift("Timestamp"); // Append "Timestamp" to the sheet (if desired)
    targetSheet.appendRow(itemTitles); // Append form item titles to the sheet
  }
  const itemResponses = e.response.getItemResponses();
  const responses = itemResponses.map(itemResponse => itemResponse.getResponse()); // Get user responses
  responses.unshift(new Date()); // Add today's date to the responses (if desired)
  targetSheet.appendRow(responses.map(e => Array.isArray(e) ? e.join(",") : e)); // Append responses to the sheet
}

the code above will create a trigger that is linked to your form that will submit the answer to a particular sheet

after that a on edit or manually triggered code that moves it to the relevant sheet

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var seet = ss.getSheetByName('TC'); //source sheet
  var testrange = seet.getRange('J:J'); //range to check
  var testvalue = (testrange.getValues());
  var csh = ss.getSheetByName('C'); //destination sheet
  var data = [];
  var j =[];

  //Condition check in H:H; If true copy the same row to data array
for (i=0; i<testvalue.length;i  ) {
  if ( testvalue[i] == 'SENT') {
  data.push.apply(data,seet.getRange(i 1,1,1,25).getValues());
  //Copy matched ROW numbers to j
  j.push(i);
 }
 }
//Copy data array to destination sheet

 csh.getRange(csh.getLastRow() 1,1,data.length,data[0].length).setValues(data);

//Delete matched rows in the source sheet
  for (i=0;i<j.length;i  ){
  var k = j[i] 1;
  sheet.deleteRow(k);

//Alter j to account for deleted rows
  if (!(i == j.length-1)) {
  j[i 1] = j[i 1]-i-1;
}

the code above is what I use to move one row to another sheet with slight bit on modification you can get it to move it to the destination sheet while leaving it in the master sheet if you set up the form submit to send the form data to the master