I have some code which adds the responses to a google sheets from Google Forms sheet, the issue I am facing is it adds it to the first column irrelevant of whether it is the correct column. I know this likely caused by the recordarray just assigning it the first free space but I have no idea how to specify the column based on the title? Thanks for any help
function onFormSubmit(event) {
record_array = []
var form = FormApp.openById('12sDsbDUiYvq5Ba2XurnXKh7S5gfM-VDaxzqLG_5n3ao'); // Form ID
var formResponses = form.getResponses();
var formCount = formResponses.length;
var formResponse = formResponses[formCount - 1];
var itemResponses = formResponse.getItemResponses();
for (var j = 0; j < itemResponses.length; j++) {
var itemResponse = itemResponses[j];
var title = itemResponse.getItem().getTitle();
var answer = itemResponse.getResponse();
Logger.log(title);
Logger.log(answer);
record_array.push(answer);
}
AddRecord(record_array[0], record_array[1], record_array[2], record_array[3], record_array[4],
record_array[5], record_array[6], record_array[7], record_array[8]);
}
function AddRecord(q1, q2, q3, q4, q5, q6, q7, q8) {
var url =
'https://docs.google.com/spreadsheets/d/1ZHlvt_Blqe7rDheIxVjvYhneoZfZPGiGbXFSIri6u1c/edit#gid=0';
//URL OF GOOGLE SHEET;
var ss= SpreadsheetApp.openByUrl(url);
var dataSheet = ss.getSheetByName("Sheet7");
dataSheet.appendRow([q1, q2, q3, q4, q5, q6, q7, q8, new Date()]);
}
It won't let me post a screenshot in the comments, so for now this is just a comment but I will come back and try and answer based on response. Just wondering, is there a reason that the default behavior of Google Forms saving to a Google Sheet won't work for you? Or rather, what is the problem you are trying to solve by scripting to move the answers from the form to a sheet? Below is an image of a form, you can click the green Sheets logo pictured and it will automatically set up a Sheet to collect form responses. This sheet will also auto-update with any new responses. I've found that it's much easier to manipulate the data once it is already in a Sheet.