I have two google forms which store the responses in a linked sheet. Users provide some information into the form the key part being a question in each form where they enter their name and birthday. I am trying to move the responses from the first sheet to the second by iterating through the previous responses and copying the information across if there is a matching name and birthdate. However I can't seem to get the birthdates to match in the if statement. If I leave out the birthday condition and just use first and last name everything works exactly as planned but it won't find a successful match if i have the birthdate condition in the if statement.
Any suggestions would be appreciated.
//Set sheet containing previously submitted data
var freeResponsesSheet = "originalSheet";
//set new template document
var docTemplate = "docTemplate";
//Get data range from original sheet
var original = SpreadsheetApp.openById(freeResponsesSheet);
var ss = original.getActiveSheet();
var lr = ss.getLastRow()
var lc = ss.getLastColumn()
var range = ss.getRange(2,1,lr,lc);
var freeResponses = range.getValues();
Logger.log(freeResponses);
function onFormSubmit(e) { // add an onsubmit trigger
// Gather data from submitted values in the form
var date_stamp = e.values[0];
var email_address = e.values[1];
var first_name = e.values[2];
var last_name = e.values[3];
var year_level = e.values[4];
var birthday = e.values[5];
var newData = "NoMatch";
Logger.log(email_address);
Logger.log(birthday)
//search data range for info
for (var i = freeResponses.length-1; i >= 0; i--) {
//set variables for info from free responses spreadsheet
var checkEmail = freeResponses[i][1];
var checkFirstName = freeResponses[i][2];
var checkLastName = freeResponses[i][3];
var checkYearLevel = freeResponses[i][4];
var checkBirthday = freeResponses[i][5];
Logger.log(checkEmail);
Logger.log(checkBirthday);
// Compare the data
if (birthday == checkBirthday && first_name == checkFirstName && last_name == checkLastName) {
//set variables for info from free responses spreadsheet
var freeEmail = freeResponses[i][1];
var freeFirstName = freeResponses[i][2];
var freeLastName = freeResponses[i][3];
var freeYearLevel = freeResponses[i][4];
var freeBirthday = freeResponses[i][5];
var freeq1 = freeResponses[i][6];
var freeq2 = freeResponses[i][7];
var freeq3 = freeResponses[i][8];
var freeq4 = freeResponses[i][9];
var freeq5 = freeResponses[i][10];
var freeq6 = freeResponses[i][11];
var freeq7 = freeResponses[i][12];
var freeq8 = freeResponses[i][13];
var freeq9 = freeResponses[i][14];
var freeq10 = freeResponses[i][15];
var freeq11 = freeResponses[i][16];
var freeq12 = freeResponses[i][17];
var freeq13 = freeResponses[i][18];
var freeq14 = freeResponses[i][19];
var freeq15 = freeResponses[i][20];
var freeq16 = freeResponses[i][21];
var freeq17 = freeResponses[i][22];
var freeq18 = freeResponses[i][23];
var freeq19 = freeResponses[i][24];
var freeq20 = freeResponses[i][25];
var freeq21 = freeResponses[i][26];
var freeq22 = freeResponses[i][27];
var freeq23 = freeResponses[i][28];
var freeq24 = freeResponses[i][29];
Logger.log(freeEmail);
var newData = [freeEmail, freeFirstName, freeLastName, freeYearLevel, freeBirthday, freeq1, freeq2, freeq3, freeq4, freeq5, freeq6, freeq7, freeq8, freeq9, freeq10, freeq11, freeq12, freeq13, freeq14, freeq15, freeq16, freeq17, freeq18, freeq19, freeq20, freeq21, freeq22, freeq23, freeq24,];
break;
};
};
Aucun commentaire:
Enregistrer un commentaire