I'm compiling a google sheet with a list of Form Responses after answering questions on a google form. This is what the google From Response Page would look like: Form Response

I want to compile a script so that upon a new row of entry it will:

  1. identify and determine the substrings in Columns E and F (Location and names).
  2. For the substrings found for locations, it will copy and paste the row in the respective location tab. (Ie if "hub" is found it will copy and paste the row in the tab: "hub" Same thing for the Names.

The tab for "Hub" and "John" would look like this after data entry: Dub John

What I have gotten so far for automatically entering data to Tab "John":

function onEdit(event) {

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

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


Here's what I have so far to try and separate a cell into substrings using Split():

function John(range) {
  var output2 = [];
  for(var i=0, iLen=range.length; i<iLen; i++) 
    var s = range[i][1].split(",");    
    for(var j=0, jLen=s.length; j<jLen; j++) 
      var output1 = []; 
      for(var k=0, kLen=range[0].length; k<kLen; k++) 
        if(k == 1) {
        } else {
  return output2;

Your Answer

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

Browse other questions tagged or ask your own question.