Move row based on cell value generated by submitted form

Question

I have a Google spreadsheet called Product Request Form (Responses) with three sheets labelled Form Responses, Clifford and Jim. In the Form Responses sheet there are three columns: timestamp, ProductArtist and ProductLabel.

I'd like the whole row to be moved/cut to either Clifford or Jim sheet based on the cell value under ProductLabel. E.g. If the cell value is Warner I'd like the row moved to Clifford, if the cell value is Universal I'd like that row moved to Jim.

It would also be extermley handy to have them moved as soon as the sheet Form Responses is populated upon someone completing the form.

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

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

I'm still finding it difficult to understand how to write the "on form submit" script. I've managed to find a similar script which works when the cell value is edited however it doesn't work when a row is created on the submission of the form.

Answer 1

If I was writing this code, here's the thought process I'd follow.

It would also be extermley handy to have them moved as soon as the sheet "Form Responses" is populated upon someone completing the form.

You can write a function that will be automatically triggered when a form is submitted. It will receive an Event that contains information about the submission (Form values & the Range where they have been inserted in the Responses sheet).

• Using Container-Specific Installable Triggers
• Understanding Events

I'd like the whole row to be moved/cut to either "Clifford" or "Jim" sheet based on the cell value under "ProductLabel"

Because the values are provided with the event delivered on form submit, it is very easy to copy info to other sheets. The following skeleton function makes a decision based on ProductLabel - and it's set up to easily evolve to consider more than two values for that column. It doesn't yet have the code to copy the new form data to the target sheet (but it will delete the input before exiting). Personally, I'd tend to add a non-Form column to the source sheet and use it to record "state" once I'd handled the new form input, but let's stick with "move", or "copy-then-delete", as asked.

function handleNewForm(event) {
  switch (event.namedValues.ProductLabel) {
    case "Warner":
      // write event.values to "Clifford" Sheet.
      break;
    case "Universal":
      // write event.values to "Jim" Sheet.
      break;
    default:
      // Unexpected input, needs to be handled
      break;
  }
  // We have the range that contained the input, we can clear it.
  // There are side-effects to this that you might not want.
  event.range.clear();
}

Copy row to target sheet

To accomplish this, you need to figure out what the destination Range is, then do something like:

destination.setValues([event.values]);

To get the destination range, you'll need to open the appropriate sheet, figure out the existing data range (and thus the next available line), and use that to get destination. For .setValues() to work, destination must have the same dimensions as the two-dimensional array being written to it. The event.values parameter is a one-dimensional array - a row. To make that into a 2-D array, we simply write it as the lone element in a new array, which represents rows. Therefore, to write [event.values] to the target sheet, we require a 1-row by 3-column range.

All that's left is work!

Answer 2

Be aware that if you use the query function to copy data, the query fetches only the literal text/values in the source cells. For example, if you have =HYPERLINK()'s in your data, using a query to copy the data will strip the links, leaving only the anchor text. To copy what is effectively a formula, you need to use the getFormulas() method.

The tricky thing with this method is that it gives a String[][] full of blank strings for all cells that are not formulas. If you blindly apply this result to the target using setFormulas() you blank out all the non-formula content copied using copyTo() or setValues().

Here is a discussion with code to merge the value and formula arrays, that you can then apply using the setValues() method within the same workbook. Moving data to a sheet in a different workbook requires more low-level work.

Answer 3

Easy just use function "query" as a cell formula, no script needed.

=query (base! A1:x; "select * where c='blah' ...")

Use it on each sheet.