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
FormSubmittrigger.
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
}
6
solved Google Apps Script: Sheets Forms Data Manipulation, Deleting Rows if Certain Cells are Blank, while Maintaining Certain Columns