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
