Google form responses to existing Google sheet columns and maintain existing formatting

0
votes

I have an existing google form and am looking to:

Image 1. of the google form question.

1) Have the response to the question (What is your name) in the form automatically populate (Sheet 1, Column C) on this existing google sheet

Image 2. Where the google form data will have to go

2) The timestamp that gets generated with each google form submission to automatically populate (Sheet 1, Column E) in the YYYY-MM-DD format.

3) While these google form responses will be recorded in this spreadsheet there will be times when I will have to manually go in and enter information in subsequent rows as well.

Is this possible to do? I am new to bringing in data from google forms into google sheets, can anyone help with the questions above?

1
google-sheetsgoogle-forms
It looks like the form responses are in another sheet in your spreadsheet. You could simply use ={'Form Responses 1'!A2:A} (or whichever sheet name/column combination the name answer is in) in cell C2. Same with the date. Then you can format the date column. – CalamitousCode
Thank you @s1c0j1. If I understand you correctly, I need to use ={'Form Responses 1'!A2:A} as a formula when conditional formatting? + Also, isn't there a way not to have to post process the date column by formatting the column everytime a new time stamp is sent from the google form? Is there a way to simply have the time stamp appear in the column as YYYY-MM-DD format. + Second, there will be times when I will have to enter the names myself and sometimes rely on the google form responses. – Experimental Yogi
There was no mention of conditional formatting. If you set the whole column's format at the beginning, you won't have to change it after each submission. I'll get back to you about the rest. – CalamitousCode
Hi @s1c0j1, I did everything as you had listed out but end up getting this error: "TypeError: Cannot read property "namedValues" from undefined. (line 2, file "Code")" in the script editor when I press the play button. – Experimental Yogi
You cannot debug in the normal way with an event object. Try submitting a form. – CalamitousCode

1 Answers

0
votes

Okay. A couple of things.

  1. Go to the Tool menu > Script editor.
  2. Name the script (maybe 'Form Submission'?) by clicking the 'untitled project' text in the top left of the editor.
  3. Replace all text in code.gs with the code below. (Change the code where indicated).
  4. Then go to Edit > Current project's triggers.
  5. Click the link that says: No triggers set up. Click here to add one now.
  6. Under Run, select onSubmit.
  7. Under Events, select on form submit.
  8. Click save.
  9. Now you should go back to the editor and push the play button. This will run the function and initiate the authorisation process. Click through the prompts and accept.

Now, every time a form is submitted, the name and timestamp will be copied over.

function onSubmit() {

    var spreadsheet = SpreadsheetApp.getActive();

    var responseSheet = spreadsheet.getSheetByName('Form Responses 1');
    var copyToSheet = spreadsheet.getSheetByName('Target');

    var rLastRow = responseSheet.getLastRow();
    var tLastRow = copyToSheet.getLastRow() + 1;
    var lastCol = responseSheet.getLastColumn();

    var values = responseSheet
        .getRange(rLastRow, 1, 1, lastCol)
        .getValues()[0];

    var timestamp = Utilities.formatDate(new Date(values[0]), Session.getScriptTimeZone(), 'yyyy-MM-dd');

    var name = values[1];

    copyToSheet.getRange('C' + tLastRow).setValue(name);
    copyToSheet.getRange('E' + tLastRow).setValue(timestamp).setNumberFormat('yyyy-MM-dd');
}