CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-forms
Gav
Gav

Reputation: 393

Create a Google Form that responses go into a specific column on an existing spreadsheet

I have an existing Google Sheet which I would like to create a form for so users can input data more easily.

The form would have three questions and I would like them to go into their corresponding columns on the existing spreadsheet.

Question 1 would go into Column 1, Question 2 would go into Column 3 on the same row and Question 3 would go into Column 5 on the same row.

Does anyone know how I can do this or an Apps script for this please?

Upvotes: 3

Views: 9347

Answers (1)

ziganotschka
ziganotschka

Reputation: 26796

The easiest way to link the form to your existing spreadsheet

  • Create a new form by going to https://docs.google.com/forms/u/0/?tgif=d
  • Click on "+"
  • Populate your form questions as desired
  • Click on "Responses"
  • Click on the three vertical dots and "Select response destination"

On each form submitting the responses will automatically go into your spreadsheet - most likely into a new sheet created automatically.

To move form responses from the new sheet into the already existing sheet into specified columns

  • Bind to your destination spreadsheet a script with an installable onFormSubmit trigger
  • onFormSubmit retrieve the newest response with the event object namedValues
  • assign the values individually to the last row of the desired columns in your sheet of interest

Sample:

function myFunction(e) {
  var mySheet = SpreadsheetApp.getActive().getSheetByName("PASTE HERE THE SHEET NAME");
  var freeRow = (mySheet.getLastRow()+1);
  var column1 = 1;
  var column3 = 3;
  var column5 = 5;
// replace the following question titles by your real question titles
  mySheet.getRange(freeRow, column1).setValue(e.namedValues["What is your name?"]);
  mySheet.getRange(freeRow, column3).setValue(e.namedValues["How old are you?"]);
  mySheet.getRange(freeRow, column5).setValue(e.namedValues["Do you like Apps Script?"]);
}

Upvotes: 6

Related Questions