CODEWITHSUNDEEP
google-sheetsgoogle-forms
Experimental Yogi
Experimental Yogi

Reputation: 3

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

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?

Upvotes: 0

Views: 3648

Answers (1)

CalamitousCode
CalamitousCode

Reputation: 1414

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');
}

Upvotes: 0

Related Questions