screen shot of Google Sheet

Basically, I have a book which gets data anytime someone fills a form.

There are five groups I need the data divided into.

If a cell contains one of the five answers all the answers by user need to be copied in its respective sheet.

I have tried using nested if statements but it gets too complex too quickly.

=IF(D2 ="Squeaky clean",IF(E2="Just once","Normal"),IF(D2="A little tingly. Sometimes it stings",IF(E2="Just once","Sensitive")),IF(D2="A little tight all over",IF(E2="Just once","Normal")),IF(D2="Tight across my forehead but otherwise it feels pretty good",IF(E2="Just once","Combination")))

Here's what I have tried using.

This data then goes to Mailchimp lists which triggers emails when the list is updated.


I tried asking the same question on Google Product Forums and someone posted this script. It works like a charm. Hope you guys find it useful.

 * Moves row of data to another spreadsheet based on criteria in column 6 to sheet with same name as the value in column 3.

function onEdit(e) {
  var ss = e.source;
  var s = ss.getActiveSheet();
  var r = e.range;

  // to let you modify where the action and move columns are in the form responses sheet
  var actionCol = 6;
  var nameCol = 3;

  // Get the row and column of the active cell.
  var rowIndex = r.getRowIndex();
  var colIndex = r.getColumnIndex();

  // Get the number of columns in the active sheet.
  // -1 to drop our action/status column
  var colNumber = s.getLastColumn()-1;

  // if the action/status col is changed to ok do stuff. Change "ok" below to whatever you want the trigger word to be.
  if (e.value == "ok" && colIndex == actionCol) {
    // get our target sheet name - in this example we are using the Skin Type column
    var targetSheet = s.getRange(rowIndex, nameCol).getValue();
    // if the sheet exists do more stuff
    if (ss.getSheetByName(targetSheet)) { 
      // set our target sheet and target range
      var targetSheet = ss.getSheetByName(targetSheet);
      var targetRange = targetSheet.getRange(targetSheet.getLastRow()+1, 1, 1, colNumber);
      // get our source range/row
      var sourceRange = s.getRange(rowIndex, 1, 1, colNumber);
      // new sheets says: 'Cannot cut from form data. Use copy instead.' 
      // ..but we can still delete the row after.  Put // in front of below row if you don't want to delete after copying to new sheet
      // or remove // below if you want to keep but note the move, but you'll need to add // to row above if removing // from below
      //  r.setValue("Copied");
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.