Script APP - Function triggered by value submitted through the Google Form

Question

I will highlight that I'm not a scripter and trying to put something simple for a friend but i have hit the wall.

I have put together a script which is moving the row to another sheet based on value from Col.

This is working perfectly with onEdit (e) - where user need to add the value to the cell.

I'm looking for a something slightly different which is same/similar code but will move the row to the sheet - pre-defined in code or based on cell value, but triggered by Google Form.

Is there any solution for this as i don't know what to try. Any help will be appreciated

Here is my code which is working perfectly with function onEdit(e) - with user input into 2nd column where is IN/OUT

function onEdit(e){

  let range = e.range;
  let col = range.getColumn();
  let row = range.getRow();
  let val = range.getValue();
  let source = e.source.getActiveSheet();

  if (col == 7 && val != ''){
    let ss = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = ss.getSheetByName(source.getName());
    let targetSheet = ss.getSheetByName(val);
    let data = sheet.getRange(row,1,1,sheet.getLastColumn()).getValues();

    targetSheet.appendRow(data[0]);
    sheet.deleteRow(row);

  }
}

​Thanks

Move the row from Responses to Tab with Value defined from 2nd column triggered by Google Form.

Update with example how i want to move the row

UPDATE after Tanaike help:

function onSubmit(e) {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form responses")
  var rows = sheets.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  const sheetName = e.values[1];
  if (!sheetName) {
    console.log("No sheet name.");
    return;
  }
  const sheet = e.source.getSheetByName(sheetName);
  if (!sheet) {
    console.log("No sheet.");
    return;
  }
  sheet.appendRow(e.values);

  var rowsDeleted = 0;
  for (var i = 0; i <= numRows - 1; i++) {
    var row = values[i];
    if (row[1] == 'IN' || row[1] == 'OUT') { // This searches all cells in columns A (change to row[1] for columns B and so on) and deletes row if cell is empty or has value 'delete'.
    sheet.deleteRow(i+1)
      //sheets.deleteRow((parseInt(i)+1) - rowsDeleted);
      rowsDeleted++;
    }
    }


}

Answer 1

From your following reply,

I would like to row which will be populated from Google Form to be moved to another row without any human interaction. Based by value from 2nd column IN / OUT - if marked as IN the whole row will be moved to IN sheet, if marked as OUT row will be moved to OUT sheet. As my script is based on onEdit this require human interaction to work. Is there any solution that this can be done without human interaction.

How about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Google Spreadsheet linked to your Google Form. And, save the script. And, please install OnSubmit trigger to onSubmit.

When you run this script, please submit your Google Form. By this, the script is automatically run by the OnSubmit trigger.

function onSubmit(e) {
  const sheetName = e.values[1];
  if (!sheetName) {
    console.log("No sheet name.");
    return;
  }
  const sheet = e.source.getSheetByName(sheetName);
  if (!sheet) {
    console.log("No sheet.");
    return;
  }
  sheet.appendRow(e.values);
}

• When this script is run by the OnSubmit trigger, the submitted row is copied to the sheet by retrieving the sheet name from the value of column "B".

Note:

• If the submitted row is not copied to the specific sheet, please check the log. By this, you can see the reason for the issue.