Google Script - Move new submissions to another sheet based on the responses

Question

I'm trying to create a script that will take a new form response and move it to another sheet based on the information submitted. For example, let's say the form has two answer choices A, B. The spreadsheet has three sheets; Form Responses, Sheet A, Sheet B. If someone submits the form and selects A, I need that new row to be moved from "Form Responses" to "Sheet A." I found someone else's script that does exactly this but using the OnEdit function. I cannot figure out how to modify this script to work when a new form response is submitted.

function onEdit(event) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = event.source.getActiveSheet();
var r = event.source.getActiveRange();

if(s.getName() == "Form Responses" && r.getColumn() == 2 && r.getValue() == "A") {
var row = r.getRow();
var numColumns = s.getLastColumn();
var targetSheet = ss.getSheetByName("Sheet A");
var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
s.getRange(row, 1, 1, numColumns).moveTo(target);
s.deleteRow(row);
  }
}

I used the installable triggers and replaced the OnEdit function with onFormSubmit but that doesn't work. I'd really appreciate it if anyone could help me with this.

Thanks,

Answer 1

To achieve what you want, you will need to:

1. Create a function write_to_new_sheet that we'll use in a trigger function whenever a new response hits the form. This function will take the form response as an event object e:

function write_to_new_sheet(e){
  let responses = e.response.getItemResponses()
  let new_row = get_new_response_data_as_row(responses)
  let sheet_to_write = SpreadsheetApp.openById('your spreadsheet id').getSheetByName('sheet A') // or 'sheet B', you can set this dynamically by checking the new_row, corresponding to the response as a gsheet row
  write_values_in_first_row(sheet_to_write, new_row)
}

this are the auxiliary functions to write_to_new_sheet:

function get_new_response_data_as_row(responses){
  let new_row = []
  responses.forEach(response => {
     new_row.push(response.getResponse())
  })
  return new_row
}

function write_values_in_first_row(sheet, new_row_values){
  let row_to_write_from = 2 // assuming you have a header
  let sheet_with_new_row = sheet.insertRowBefore(row_to_write_from)
  let number_of_rows = 1
  let number_of_columns = new_row_values.length
  let range = sheet_with_new_row.getRange(row_to_write_from, 1, number_of_rows, number_of_columns)
  let results =range.setValues([new_row_values])
  return new_row_values
}

2. Set up an installable trigger that works whenever you submit a new response to the form:

function setup_write_to_new_sheet_on_form_submit(){
  ScriptApp.newTrigger('write_to_new_sheet')
  .forForm('your form id goes here')
  .onFormSubmit()
  .create();
}

3. Run the above function once, to set up the trigger.
4. try submitting a new response on the form, and check the changes in the sheets you want it to be written.

Answer 2

Try something a little less broad in your comparing of variables,, For instance the sheet that submissions are sent to is a constant and already address.

function formSubmission() {
 var s = SpreadsheetApp.getActiveSheet();
 var data = range.getValues(); // range is a constant that always contains the submitted answers
 var numCol = range.getLastColumn();
 var row = s.getActiveRow;
 var targetinfo = s.getRange(row,(Yourcolumn#here).getValue);
    if(targetinfo() == "Desired Sheet Name") {
      var targetSheet = ss.getSheetByName("Sheet A");
      var targetrow = targetSheet.getLastrow()+1);
      var Targetcol = numCol();
      targetSheet.getRange(targetrow,1,1,Targetcol).setValues(data);
     }
  }

I didn't test that but hopefully it helps look up Event Objects in the developer guide i just recently found it and it clarifies a lot

Answer 3

the triggers can be set by going to: then set it:

Answer 4

I have a spreadsheet that collects the form submissions and then has extra sheets that have filtered out these submission based on the answers. All this is just with formulas. Could that do the trick also?