1

I have a Google Form, with multiple sections, and duplicated questions. Google Forms Automatically creates separate columns in the resulting Spreadsheet for each question, even if it is the exact same question re-used. I have looked everywhere to find a way to have the Google Form to record the duplicated questions in the same column but to no resolution.

Example of what the form contains:

First section...
Facebook Name (Text), 
Legal Name (Text), 
Phone Number (Text), 
Email Address (text) 
and Course of Interest (Drop-down that then sends the responder to a set section based on an answer - see below).

This section generates Columns B1 to F2 as it should.

However, from here it gets difficult and messy. Sections 2, 3,4, and 5 are based on Level of course requested:

Section 2 - Certificate III Level
Section 3 - Certificate IV Level
Section 4 - Diploma Level
Section 5 - Advanced Diploma Level

Because students cannot have qualifications of the same level or higher than the course they are applying for, the duplicated questions are all the same again, but the first question in this section alters with each level.
Highest Qualification:
Options include:
Year 9, year 10, year 11, year 12, Certificate I, and Certificate II .. for each section, but...

Section 3 adds Certificate III
Section 4 adds Certificate IV
And Section 5 adds Diploma.

The questions that are the same are:
Age (Single tick box), Citizenship (Drop-down list), and Closest Location (Drop down list that then sends the respondent to another section based on locations)

The remaining sections again have duplicated questions, but again, the first drop-down is altered according to the location regarding an appointment date for that location.

“Appointment” (Drop-down or Date and Time) alters per location. If specific dates are set for that location for appointments it is a drop-down list of those dates. If Not, it is a date and time picker.

The following two questions (And final questions) are exactly the same. Notes (Paragraph), Referer (Drop-down)

My problem is: This results in 49 Columns (A1 to AT1) and I only need 14 (A1 to N1) - These values then need to be appended to separate location sheets in the Spreadsheet (I know HOW to do that once I get the column issues sorted out).

Here is what I am trying:

Function UpdateCells(e) {
// Triggered on Form Submit
// Normal Active Sheet, e.range, etc settings

...
// Need to work out how to recurse through the columns in e.range and make it the ‘active cell
// Need to then copy the data in that active cell to the correct cell
...
If e.range.cell for Columns O, S, or W are not blank, copy to column G...
// This will take care of the Sections 2, 3, 4, and 5 answers and put them in the correct PRIMARY Columns

If e.range.cell for columns AA, AD, AG, AJ, etc are not blank, copy them to K...

// Then append columns A to N to the new sheet based on a value in G...
{

Can anyone help with sample code for the copying of cells AND the correct ‘Append To’ Statement to get the values from e.range (1, to 14) (Or A to N)??


UPDATE:

What I have thus far...

Would REALLY appreciate it if someone can go through and DOUBLE check the spelling and Upper/Lower Cases in this (As I am legally blind, and my programs do not tell me if the words are spelt correctly (Especially for variables and functions),

function copyData(e) {
// To be set up via a "On Form Submit" Trigger
var ss = SpreadsheetApp.getActiveSpreadsheet();
 
// Testing bothoptions below..
var Source_Data = e.values;
// var Source_Data = e.response.getItemResponses();
// The above gets the responses direct from the form, not within the spreadsheet
 
// Lets set our variables correctly now..
 
// The first set of columns are already in the order we require them ("Section 1" on the related Form)...
 
// Column 0 (A) TimeStamp
// var Time_Stamp = Source_Data[0].getvalues();
var Time_Stamp = Source_Data[0];
// Column 1 (B) Facebook Name
var Facebook_Name = Source_Data[1];
// Column 2 (C) LegalName
var Legal_Name = Source_Data[2];
// Column 3 (D) Phone
var Phone_Number = Source_Data[3];
// Column 4 (E) Email
var Email_Address = Source_Data[4];
// Column 5 (F) Course of Intererest
var Course = Source_Data[5];
 
// Write the remaining Values to correct columns in Sheet
var Highest_Qual = e.values[14] && e.values[19] && e.values[24] && e.values[29];
e.Values[6].setValues(Highest_Qual);
 
var Citizenship = e.values[15] && e.values[20] && e.values[25] && e.values[30];
e.Values[7].setValues(Citizenship);
 
var Age = e.values[16] && e.values[21] && e.values[26] && e.values[31];
e.Values[8].setValues(Age);
 
var Class_Type = e.values[17] && e.values[22] && e.values[27] && e.values[32];
e.Values[9].setValues(Class_Type);
 
var Location = e.values[18] && e.values[23] && e.values[28] && e.values[33];
e.Values[10].setValues(Location);
 
var Appointment = e.Values[34] && e.Values[37] && e.Values[40] && e.Values[43] && e.Values[46];
e.Values[11].setValues(Appointment);
 
var Rep = e.Values[35] && e.Values[38] && e.Values[41] && e.Values[44] && e.Values[47];
e.Values[12].setValues(Rep);
 
var Notes = e.Values[36] && e.Values[39] && e.Values[42] && e.Values[45] && e.Values[48];
e.Values[13].setValues(Notes);
 
var Row = e.getRow();
 
// Now prepare to append the updated data to the appropriate sheet...
 
// Need to change the following line to get the 'Active Sheet by Name'
var SourceSheet = ss.getSheetByName("SortedColumns");
 
// I only want to get the first 14 columns of data
// This should be all columns from "TimeStamp" to "Notes" from the first lot of columns ONLY
// ONCE THE VALUES HAVE BEEN PROPERLY WRITTEN
SpreadsheetApp.flush // Ensures all values are written BEFORE we continue
 
var My_Data = SourceSheet.getRange(Row, 1, 1, 14).getValues();
 
// Start with the NAME of the sheet we are going to append this record TO
// In my case, Tabs on the Spreadsheet (Seperate Sheets) are sorted by Location
// And that information is stored in the 'Location' variable...
var sheetName = Location;
 
// So, lets now GET that sheet by name....
var sheet = ss.getSheetByName(sheetName);
 
// See if the sheet already exists, if not, prepare to append to "Other"
if (!sheet) {
sheetName = "Other";
sheet = ss.getSheetByName(sheetName);
}
// Double check the sheet exists, and then append or make a new sheet
if (!sheet) {
// Stil does not exist, create it
sheet = ss.insertSheet(sheetName);
// Put the Headers in the First row of the new sheet
vIr Headers = SourceSheet.getRange(1, 1, 1, 13) // Only first 13 columns
headers.copyTo(sheet.getRange(1, 1));
}
 
// Now we KNOW the sheet exists, or that we are appending to "Other" sheet...
// Lets append the correct data in correct order...
sheet.appendRow(My_Data.getvalues());
// ALL DONE
}

 

This code has not as yet been tested, as I really DO need someone to assist in getting the text correct before I go and launch it.

However, the comments previously do link to the Spreadsheet and Forms used for testing purposes, and these are NOT ACTUAL data storages, but merely examples, so feel free to play with this a bit to get things right

  • Welcome! To best "help us help you", please consider sharing your file (or a copy with some anonymized placeholder data). Make sure it's shared as "Anyone with the link can Edit." Not sure how? Here's Google's help topic or check out this quick video of the steps. Especially helpful here would be a set of expected output data (CLEARLY MARKED as manually entered! …with the understanding you'd like that part automatic). – Joel Reid Mar 19 at 18:08
  • Resulting Spreadsheet URL (Code NOT included):docs.google.com/spreadsheets/d/… – Marty Lord Mar 20 at 11:38
  • Example form with only the main three sections (Section2 is douplicated three times, and thre. Is douplicated six times):goo.gl/forms/OJb2lF59XZlsr4ex2 – Marty Lord Mar 20 at 11:53
  • Example of full Form:goo.gl/forms/kj9zenD6gSfBzJ153 – Marty Lord Mar 20 at 13:02
  • Example spreadsheet has the linked forms (first form can be filleed out to see what I want, and second to see what it is currently doing WRONG). The “Desired Layout” is the form with teh layout for at least the first 14 columns (A to N) that I want to have populated with the actual answers from the “Copy1...” Form responses. Hope this all helps? Sorry I was unable ot edit the actual question with this information, as i am on my iPad at the moment, and when ‘Pasting” the stupid thing does nto paste wehere i want it (at teh end of the question) :( – Marty Lord Mar 20 at 13:07
0

Are you dedicated to using Apps Script, and manipulating form responses as they come in? If not, consider using a separate tab (sheet) to condense responses with native spreadsheet formulas. This condensed version could then be your source. Leaving form responses pristine is also usually a good idea for other reasons.

I created a quick sample of how that might work, from your example file. Stepping through my thinking:

The "Condensed" form responses sheet should pull in the same column headers from your Form Responses 1 sheet, but with no repeats. Turning the data sideways with TRANSPOSE and back since it seems to like rows but not columns, I just used UNIQUE:
=TRANSPOSE(UNIQUE(TRANSPOSE('Form Responses 1'!A1:1)))
This one formula in the yellow-highlighted A1 populates all the headers in row 1.

Next I want the responses shown again, but only columns A through F, since they don't need collapsing. I populated those columns with the single array formula in green-highlighted cell A2:
=ARRAYFORMULA('Form Responses 1'!A2:F)
The ARRAYFORMULA and open-ended cell reference make this A2 formula populate however many rows are needed to cover all the input, across whole columns.

Finally, for each response row, I want to condense all of that person's Highest Qualification form answers into one cell (nevermind that three of four are blank). It can find them in Form Responses 1, columns G, L, Q, and V. For the first pale-aqua-highlighted cell, G2 right under Highest Qualification, I entered the following:
=ARRAYFORMULA( 'Form Responses 1'!G2:G & 'Form Responses 1'!L2:L & 'Form Responses 1'!Q2:Q & 'Form Responses 1'!V2:V )
Again, it's an array formula to go down all form responses. The repetitive meat of the formula just grabs "all four of their answers" to the four Highest Qualification questions, appends them into one string using the string-concatenation operator, ampersand &, and puts that result into one cell.

Likewise then with "all of their answers" for Citizenship, Age, Class Type, and Closest Location. Since the formulas will take the same form, I just selected G2 and dragged the— the… "bottom-right of the selected cell, auto-populate, tiny square handle" control from G2 to K2. All those cyan cells' formulas were then pulling in their question-matching, consolidated, once-per-row sets of answers.

And again for the magenta-highlighted cells L2, M2, and N2; concatenating respondents' "six answers" to each of: Appointment, Representative, and Notes. The only difference there was the formulas have to pull in six answers each rather than four:
=ARRAYFORMULA( 'Form Responses 1'!AA2:AA & 'Form Responses 1'!AD2:AD & 'Form Responses 1'!AG2:AG & 'Form Responses 1'!AJ2:AJ & 'Form Responses 1'!AM2:AM & 'Form Responses 1'!AP2:AP )
Then I drag-autofilled (or copy-pasted, which also intelligently updates the formula cell references) that formula to M2 and N2.

Just wanted to offer another approach, and spell it out in case you end up doing this or something similar. Sheets is quite capable—not bad for four bog-standard formulas I'd say. I strongly prefer using data-consolidation- and report- type sheets, living next to the original source data sheet. It offers transparency, data safety while experimenting, minimized risk of damaging the form-sheet relationship, reduced programming need, and some great formula-based flexibility.

EDIT: It's important…

…to note that when any formula that has an result array; that is, multi-cell output (so, SORT, UNIQUE, FILTER, ARRAYFORMULA, etc.); that formula's output is both dynamic and one-way from input to output.

Why "one-way" matters

You cannot edit the output of a spreadsheet formula. If you type some text or a number over a formula result, be it one cell or one value out of five thousand, the formula is no longer functioning. You've just got the one value you entered. It may be the right value even, but it's merely what you typed, not a live formula result anymore. This also applies if you copy a result and paste as plain text. If you need to alter the result of any formula, you must either change the formula itself to match your actual need, or directly alter the source values.

Example: if someone has a typo in their form entry, if you type over it in a simple one-cell formula or a huge multi-column report, the formulas are broken.

The takeaway: fix it in Form Responses. Since any formulas that read entries are 'live', when you edit a Form Responses row, the Condensed report using it will recalculate and update as well.

Why "dynamic" matters

Any spreadsheet formula doesn't care what you type near its output, and cannot help you keep data organized or associated.

Example: imagine the nightmare of having entered text comments next to an automatic list (e.g. your Condensed entries), but then a couple rows of source values (e.g. two students applications are invalidated) are intentionally and correctly deleted. The source values all move up to fill the gap as you delete the rows. Also, the live formulaic output all shifts up a couple rows. But since your comments are not part of the automatic list, they stay put and are now misaligned.

The takeaway: keep related data together. You are free, for example, to add new columns next to form-linked "Form Responses" columns. They would be sorted, moved, or deleted as rows and always stay together. In kind, any formulas that were reading and displaying those new columns elsewhere would also reflect the changes correctly.

Mitigating both problems, also perhaps consider visual cues to not edit an formula-result area nor work next to it. Deleting extra columns next to reports can discourage "margin notes". A light gray background, or white cell outlines, or just a label row above the real headers can remind all the file's users that those columns are automatic.

  • Ok, not really famular with this approach, but it DOES seem uch easier if it AUTOAMTICALLY updates on a new form submission? Sorry to ask, but are you able to insert those formulas into the existing sheet so I can get a better idea of its useage? And, how to ensure those formulaas are repeated for new submissions?.. Also, would these formularas also not work in the programming if required later? As I am also trying to programatically ‘Apend” the corrected data into sheets for the “Closests Location” column final result. (EG: Werribeee, Craigieburn, Melton, Ballarat, etc) To sort by location – Marty Lord Mar 29 at 1:26
  • Yes. The great appeal of ARRAYFORMULA (and other formulas with ranges as input) is that they operate on whole arrays of values. UNIQUE gets an input range, and has multi-cell output—a column of the input values but with repeats dropped. The other formulas don't use any real calculations—they're just & concatenating cells into 1 cell, so I added an explicit ARRAYFORMULA(…) because we need it to operate on every row of input. Then, instead of feeding it say, G2, we can feed it G2:G —the entire column. Since it operates on the entire column, it will recalculate for any new data added. – Joel Reid Mar 29 at 12:22
  • @MartyLord Given your comment question, please read my important edit at the end of my answer above. Also, I created the demo sheet as we cannot add to your example sheet—it is shared "View Only". – Joel Reid Mar 29 at 12:33
  • My appologies, I thought I had put link sharing on. It is done now :) – Marty Lord Mar 30 at 14:26
  • If I understand you correctly, using code such as... Var Class_Type = e.Values[14] && e.Values[19] && e.Valuse[23] && e.Valuse[28]; var Row = e.getRow(); (Just as an example, as this is NOT the correct numbers for those values) Would not result in eventually being able to write.. Sheet.getRange(Row, 9).setValues(Class_Type); ?? And then Eventually recalculatoing t the entire array of values.. Var Append_Array = Sheet.getRange(Row, 1, 1, 15); So this new array can be appended with ... NewSheeet.AppendRow(Append_Array); ?? – Marty Lord Mar 30 at 14:41

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.