Google Apps Script: Sheets Forms Data Manipulation

Question

This question is a continuation of the following: Google Apps Script: Sheets Forms Data Manipulation and Deleting Rows if Certain Cells are Blank

I have a sheet "Form Responses" where users can fill out the same question up to 5 times. The following code will organize these lines row by row in the 'Paste Values' sheet. In addition to this, I have tried to sort the columns by the Timestamp column in ascending order (so that new replies from the google form will show up at the top).

The first issue with this is that I have additional columns in my Paste Values sheet (A, O, P, Q, R, S) that are not in my Forms Sheet. When a new form goes to the top, the information in these columns do not move with the correct row they were supposed to be in; they just stay where they are.

The second issue with this is that if I try to add an On Form Submit trigger, so that the Paste Values sheet updates every time a new form gets submitted, the rows will accumulate from start to finish

So for example if I have:
Row1
Row2
Row3

And then a new form gets submitted, it becomes:
Row1
Row2
Row3
Row1
Row2
Row3
Row4

This leads to a duplication of rows which I don't want.

My end goal is to achieve the following:

1) every time someone submits a new form, the answers come to the top; everything is sorted in ascending order (by Timestamp column) in both 'Form Responses' and 'Paste Values'

2) Add an on edit trigger so that each time a user submits a form, only the new row(s) are added to 'Paste Values', rather than all of the rows duplicating over and over again

3) Columns: A, O, P, Q, R, S, and T are columns that I want to be able to edit freely in 'Paste Values', meaning that every time a new row is added to 'Paste Values', the corresponding information in these columns move with that row, and are not affected by an On Edit trigger.

Here is the google sheet example: https://docs.google.com/spreadsheets/d/1QUzPxxPB6CDL7Y5Dh92w6nsEM0QFUAaGG9LomjnGgLM/edit?usp=sharing

Form Responses sheet is the data received by the google form. Paste Values 'this is the goal' sheet is what I would like it to become

code for the row manipulation:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var src = ss.getSheetByName("Form Responses");
  var dst = ss.getSheetByName("Paste Values");
  var values = src.getDataRange().getValues();
  var header = values.splice(0, 1)[0].splice(0, 13);
  var res = values.reduce(function(ar, e) {
    var h = e.splice(0, 3);
    h.unshift("");
    for (var i = 0; i < 5; i++) {
      var temp = e.splice(0, 10);
      if (temp.filter(String).length == 0) continue;
      if (temp.length < 10) temp.splice(temp.length, 10 - temp.length, "");
      ar.push(h.concat(temp));
    }
    return ar;
  }, []);
  if (dst.getRange("A1").getValue() != "Status") res.unshift(["Status"].concat(header));
  dst.getRange(dst.getLastRow() + 1, 1, res.length, res[0].length).setValues(res);
}

And code for sorting each row in ascending order by the Timestamp column:

// Sort Timestamp column from newest to oldest: 

function Sort_Timestamp(event){
  var spreadsheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses");
  var editedCell = spreadsheet1.getActiveRange().getColumnIndex();

  var columnToSortBy = 1;
  var tableRange = "A:AZ";

  if(editedCell == columnToSortBy){   
    var range = spreadsheet1.getRange(tableRange);
    range.sort( { column : columnToSortBy, ascending: false } );
  }
}

Updated attempt to achieve the Row Manipulation into Paste Values (and return each row (splitting them) from each row of Form Responses):

function so55716140_01(event) {
  // setup this function as an installable trigger OnFormSubmit

  // set up spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourcename = "Form Responses";
  var targetname = "Paste Values";
  var sourcesheet = ss.getSheetByName(sourcename);
  var targetsheet = ss.getSheetByName(targetname);

  // get the response data - assumes 10 questions plus timestamp
  var sourcerow = event.range.getRow();
  // Logger.log("DEBUG: Response row = "+sourcerow); //DEBUG
  var sourcerange = sourcesheet.getRange(sourcerow, 1, 1, 12);  // getRange(source row, column #, number of rows, number of columns); so 13  goes to column N in form resonses "do you want to submit another form" 
  var sourcerange2 = sourcesheet.getRange(sourcerow, 13, 1, 10);  //want to start at column 13 (col M)
  var sourcerange3 = sourcesheet.getRange(sourcerow, 23, 1, 10);  // third round column W
  var sourcerange4 = sourcesheet.getRange(sourcerow, 33, 1, 10);  // fourth round
  var sourcerange5 = sourcesheet.getRange(sourcerow, 43, 1, 9);  // fifth round

  //Logger.log("DEBUG: Source range = "+sourcerange.getA1Notation()); //DEBUG
  var sourcedata = sourcerange.getValues();
  var sourcedata2 = sourcerange2.getValues();
  var sourcedata3 = sourcerange3.getValues();
  var sourcedata4 = sourcerange4.getValues();
  var sourcedata5 = sourcerange5.getValues();

  // setup the target
  var Bvals = targetsheet.getRange("B1:B").getValues();
  var Blast = Bvals.filter(String).length;
  //Logger.log("DEBUG: Blast = "+Blast); //DEBUG
  var targetrange = targetsheet.getRange(Blast + 1, 2, 1, 12);  //you're starting at the next row. so if there is data in column B3 row 2, you'll start at column B3 row 3
    // starting at column 2 in Paste Values (timestamp), only on that one row, and paste in the next 12 values (started at column 2)
  var targetrange2 = targetsheet.getRange(Blast + 2, 4, 1, 10);  //trying blast + 2, and you're only pasting 10 values
  //Logger.log("DEBUG: Target range = "+targetrange.getA1Notation()); //DEBUG
  var targetrange3 = targetsheet.getRange(Blast + 3, 4, 1, 10);
  var targetrange4 = targetsheet.getRange(Blast + 4, 4, 1, 10);
  var targetrange5 = targetsheet.getRange(Blast + 5, 4, 1, 9);

  // paste the response to the target
  targetrange.setValues(sourcedata);
  targetrange2.setValues(sourcedata2);
  targetrange3.setValues(sourcedata3);
  targetrange4.setValues(sourcedata4);
  targetrange5.setValues(sourcedata5);
 }

Answer 1

Tanaike's code is a work of art, but I think it is based on an assumption that you would only run the script once.

You've said that users will fill out a Google Form. You then manipulate this so that rows with identical columns will be transferred to one column. But ironically you then disassemble this to produce the results on "Paste Values".

I suggest a far less complicated process:

• create and install a header for "Paste Values" before any form responses are received.
• write a script that is manually installed as an OnFormSubmit' trigger. Use the object data returned by the trigger to copy the relevant data to the last row (plus 1) of "Paste Values". You might consider adjusting the form so that the name of the Submitter is selected from a dropdown - to ensure consistent spelling.
• sort "Paste Values" progressively; that is, add the code to the FormSubmit trigger.

This enables you to write your notes and other comments on Paste Values, and these will remain aligned with the relevant row after the sort.

CODE

function so55716140_01(event) {
  // setup this function as an installable trigger OnFormSubmit

  // set up spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourcename = "Form Responses 2";
  var targetname = "Paste Values";
  var sourcesheet = ss.getSheetByName(sourcename);
  var targetsheet = ss.getSheetByName(targetname);

  // get the response data - assumes 10 questions plus timestamp
  var sourcerow = event.range.getRow();
  // Logger.log("DEBUG: Response row = "+sourcerow); //DEBUG
  var sourcerange = sourcesheet.getRange(sourcerow, 1, 1, 11);
  //Logger.log("DEBUG: Source range = "+sourcerange.getA1Notation()); //DEBUG
  var sourcedata = sourcerange.getValues();

  // setup the target
  var Bvals = targetsheet.getRange("B1:B").getValues();
  var Blast = Bvals.filter(String).length;
  //Logger.log("DEBUG: Blast = "+Blast); //DEBUG
  var targetrange = targetsheet.getRange(Blast + 1, 2, 1, 11);
  //Logger.log("DEBUG: Target range = "+targetrange.getA1Notation()); //DEBUG

  // paste the response to the target
  targetrange.setValues(sourcedata);

 // OP to add sort code according to preference
}

---

Code for a form potentially having 5 sections

function ejb_op3(event) {

  // setup this function as an installable trigger OnFormSubmit

  // set up spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourcename = "Form Responses 4";
  var targetname = "Paste Values";
  var sourcesheet = ss.getSheetByName(sourcename);
  var targetsheet = ss.getSheetByName(targetname);

  // get the response row and range
  var sourcerow = event.range.getRow();
  //Logger.log("DEBUG: Response row = "+sourcerow); //DEBUG

  // range is from Column A to Column AZ: 52 columns; 3=common; 4x10 (40) = printer "section" questions PLUS "do you want to submit another form"; the final (5th) 1x9 printer "section" questions; 3+40+9=52
  var sourcerange = sourcesheet.getRange(sourcerow, 1, 1, 52);
  //Logger.log("DEBUG: Source range = "+sourcerange.getA1Notation()); //DEBUG

  // get the response data
  var sourcedata = sourcerange.getValues();

  // find the number of rows already populated on the target
  var Bvals = targetsheet.getRange("B1:B").getValues();
  var Blast = Bvals.filter(String).length;
  //Logger.log("DEBUG: Blast = "+Blast); //DEBUG

  // establish some variables
  var datastart = 3; // the first 3 columns are common data
  var dataqty = 10; // the first 4 responses have 10 columns of response data
  var printcount = 0; // status counter
  var responsecount = 0; // column status counter
  var responsedata = []; // array to compile responses

  // process the first section
  if (printcount == 0) {
    responsedata = [];

    // get the timestamp, submitter name and email
    responsedata.push(sourcedata[0][0]);
    responsedata.push(sourcedata[0][1]);
    responsedata.push(sourcedata[0][2]);

    //get the responses for the next 10 questions
    for (i = datastart; i < (datastart + dataqty); i++) {
      responsedata.push(sourcedata[0][i]);
    }

    // define the target range
    // the last line (Blast)plus one line plus the print count; column B; 1 row; 13 columns
    var targetrange = targetsheet.getRange(Blast + 1 + printcount, 2, 1, 13);
    // paste the values to the target
    targetrange.setValues([responsedata]);

    // update variables
    responsecount = i; // copy the value of i
    printcount++; // update status counter
    responsedata = []; // clear the array ready for the next section
  }
  // end opening response

  // build routine for 2nd to 4th sections
  for (z = 2; z < 5; z++) {

    //Make sure not to double count the first section
    if (printcount > 0 && printcount < 5) {

      // test if the next section exists
      if (sourcedata[0][i - 1] == "Yes") {
        // Yes for next section
        //Logger.log("DEBUG: value = "+sourcedata[0][i-1]);  //DEBUG

        // get the timestamp, submitter name and email
        responsedata.push(sourcedata[0][0]);
        responsedata.push(sourcedata[0][1]);
        responsedata.push(sourcedata[0][2]);

        //get the responses for the next 10 questions
        for (i = responsecount; i < (responsecount + dataqty); i++) {
          responsedata.push(sourcedata[0][i]);
          //Logger.log("DEBUG: data: "+sourcedata[0][i]);//DEBUG
        }

        // define the target range
        // the last line (Blast) plus one line plus the print count; column B; 1 row; 13 columns
        targetrange = targetsheet.getRange(Blast + 1 + printcount, 2, 1, 13);
        // paste the values to the target
        targetrange.setValues([responsedata]);

        // update variables
        responsecount = i;
        printcount++;
        responsedata = [];
      } else {
        // NO for next section
      }
      // end routine if the next section exists
    } // end routine for the next section
  } // end routine for sections 2, 3 and 4

  // checking for 5th response
  if (printcount == 4) {

    // test if response exists
    if (sourcedata[0][i - 1] == "Yes") {
      // Yes for next section
      //Logger.log("DEBUG: value = "+sourcedata[0][i-1]);  //DEBUG

      // get the timestamp, submitter name and email
      responsedata.push(sourcedata[0][0]);
      responsedata.push(sourcedata[0][1]);
      responsedata.push(sourcedata[0][2]);

      //get the responses for the next 9 (nine) questions
      for (i = responsecount; i < (responsecount + dataqty - 1); i++) {
        responsedata.push(sourcedata[0][i]);
        //Logger.log("DEBUG: data: "+sourcedata[0][i]);//DEBUG
      }

      // define the target range
      // the last line (Blast) plus one line plus the print count; column B; 1 row; 12 columns only
      targetrange = targetsheet.getRange(Blast + 1 + printcount, 2, 1, 12);
      // paste the values to the target
      targetrange.setValues([responsedata]);
    } else {
      // NO for next section
    }
  }
  // end routine for the 5th section


}