Hi Curt, thanks for the code, it has been very helpful. I have a question, if my form consists of two different sections how do I pass the answer of the second section to a new line in sheets without deleting the line with the answers of the first section, who will be the above line of this new one? Thanks.
Good day sir, is it possible make it the other way around? Dynamically change google form's questions from google sheets, i.e. in Form question number 1, a list of car models, be in dropdown list or the selectin list from sheets then question number 2 would dynamically change depending on the selection in question 1. basically an EASY CRUD version of google form, hope I make sense somehow. right now I still can't digest the manual doing of the CRUD web app... Thanks
Yes, but if you want to do another things with this data you have to do importrange. This is very helpful for what I'm trying to acumplish. I want to be able to select multiple people and add it to a list. Right now you can do it with check boxes to multi select but they come in one cell, then you have to split it. Now with this I can split it with the script and display the data directly.
@@genesiscatano can you explain how I can manage to use the functionality as you described. So I have a google forms questions which allows multiple selections. In google sheets this comes back in one cell as follows: answer1,answer2,answer3... etc. I would like to have these answers in seperate cells in seperate rows.
Good day sir, is there a way to make a reverse thing like I want to populate the google form using the datas from the spreadsheet using the app script?
Great video Curt! Got a few questions if ya don't mind. Firstly, are they advantages to using apps scripts to populate the google sheet as opposed to just linking the form to the sheet? Secondly, do the questions on the google form need to match the column names in the google sheet that the responses are populated with? for example, if a question on the form is "What is your date of birth?" can the corresponding column name in sheets be "Birthdate?" Thanks mate!
I want to create a form that logs something that gives me a clue as to who or what workstation, or something, submitted the form, without asking the user who they are, so I can see if people are submitting multiple answers without making them log into google when using the sheet. Seems like if I just add one more parameter to the appendrow function, that would do it. But what? is there a function that can access the responder's ip address, or workstation name, or location (the answers will all come from managed chromebooks, I can specify location in the admin console)?
Hi, I just made a Google form and link it with a spreadsheet, but suddenly the number of responses doesn't correspond to the number on the spreadsheet, I got 23 response on the form and only got 20 on the spreadsheet, and I don't know how it happen. Is it a bug or something? Is there any way to fix?. Thank u for anyone who would answer my question.
I feel the script is able to run one item at a time. If that is the case, we can use safety precautions as such one write at a time, and other requests standing in a queue.
I’m a complete newbie. I need my form to send data to more than one sheet. How do I make this script send to two sheets. Added bonus if you could explain how to set it up in a different column so that it goes to the next row, but starts in column B instead of A. I hope that make sense!
hmm looks like when setting up new form triggers there is no longer the permission for viewing and opening sheets. How can that permission be added since this method does not work anymore?
Here's an example of an Apps Script code that retrieves form responses from Google Forms and inserts them into a Google Sheets spreadsheet: javascript Copy code function onFormSubmit(e) { var formResponse = e.response; var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]; var row = [];
headers.forEach(function(header) { var value = formResponse.getResponseForItem(header); row.push(value ? value.getResponse() : ''); });
sheet.appendRow(row); } To set up this code: Open your Google Forms. Click on the vertical ellipsis (⋮) in the upper-right corner. Select "Script editor" from the menu. This will open the Apps Script editor in a new tab. In the Apps Script editor, replace any existing code with the provided code. Save the script by clicking on the floppy disk icon or pressing Ctrl+S. Close the Apps Script editor tab. Go back to your Google Form. Click on the vertical ellipsis (⋮) again and select "Script editor" to reopen the Apps Script editor. In the Apps Script editor, click on the "Triggers" icon (clock-shaped) on the left sidebar. Click on the "+ Add Trigger" button. Configure the trigger settings as follows: Choose which function to run: onFormSubmit Choose which deployment should run: Head Select event source: From form Select event type: On form submit Click "Save" to create the trigger. Now, whenever a form response is submitted, the onFormSubmit function will be triggered, and it will add the response to the active sheet of your Google Sheets spreadsheet. Make sure to have the corresponding headers in the first row of the sheet to match the form question titles. Note: Remember to save and set up the trigger for the script to work correctly.
Curt, can you make a script that will allow me to enter a field on a Form and search the linked Spreadsheet for an existing match and then populate the Form with the data from the Sheet?
SO think you would have a hit with combining this with your Creating a estimate geneerator using a form builder as those two would combine the most commonly sought out videos of what people want to do for learning programming like this in the first place. However if you already did it than clap clap cause I find myself coming back to learning form you more than any other programmer to sheer tone of voice and non schalant BS lets getr done attitude. Clap clap again.
To demonstrate how to use apps script, this is helpful when you want to customize / do some business logic on how the record will store in google sheet.
is there a way to get the answer of an item, but not when finishing the form? Let me explain: I think of making a dropdown menu dependent on what the user marks in a test. So, in one section the user marks an option, and in the next section the options are loaded according to this answer.
You can achieve this with conditional questions, you will have to set the question as multiple choice, then close to the "required icon", click on the three dots and select "Go to section based on answer".
@@blessedkanu16 thanks for the reply, but I wonder if this would be possible with script. If the response in one section of the form could be used as a variable to then generate changes in the next section of the form
Can you write a script that can duplicate a row the number of times a comma appears in a column? I have a Google Form that's for scheduling appointments for blower door testing and sometimes there are multiple addresses for one appointment. However, I need each address to be it own entry with the same base info(first name, last name, company name, date requested, etc..)? Is this possible? Is this difficult? Anything? LOL
I can only think of the SPLIT() function once in Google Sheets. =SPLIT(text, ",") Then you would have an address per column across; maybe put that right at the end so that no data is overwritten. =TRANSPOSE(SPLIT()) Would do the same for the addresses but in rows. The rest of the data wouldn't be carried into those rows though
Google Apps Script Code in Video Below: function onFormSubmit(event) { record_array = [] var form = FormApp.openById(''); // Form ID var formResponses = form.getResponses(); var fromCount = formResponses.length; var formResponse = formResponses[fromCount - 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]); } function AddRecord(first_name, last_name, color) { var url = ''; //URL OF GOOGLE SHEET; var ss= SpreadsheetApp.openByUrl(url); var dataSheet = ss.getSheetByName("Sheet1"); dataSheet.appendRow([first_name, last_name, color, new Date()]); }
why do you made this video and what the reason behind it, if you made video so you had to pull the data through google sheet in google form reverse video , you should to made it
Thank you very much, I and the operations users of my company appreciate it.
Greetings from Colombia
Hi Curt, thanks for the code, it has been very helpful.
I have a question, if my form consists of two different sections how do I pass the answer of the second section to a new line in sheets without deleting the line with the answers of the first section, who will be the above line of this new one? Thanks.
Good day sir, is it possible make it the other way around? Dynamically change google form's questions from google sheets, i.e. in Form question number 1, a list of car models, be in dropdown list or the selectin list from sheets then question number 2 would dynamically change depending on the selection in question 1. basically an EASY CRUD version of google form, hope I make sense somehow. right now I still can't digest the manual doing of the CRUD web app... Thanks
But Google forms responses are automatically populated to Spreadsgsheet without the need to use this script
Yes, but if you want to do another things with this data you have to do importrange. This is very helpful for what I'm trying to acumplish. I want to be able to select multiple people and add it to a list. Right now you can do it with check boxes to multi select but they come in one cell, then you have to split it. Now with this I can split it with the script and display the data directly.
@@genesiscatano can you explain how I can manage to use the functionality as you described. So I have a google forms questions which allows multiple selections. In google sheets this comes back in one cell as follows: answer1,answer2,answer3... etc. I would like to have these answers in seperate cells in seperate rows.
can possible to customize the form layout into 3 or 4 columns using app script?
Good day sir, is there a way to make a reverse thing like I want to populate the google form using the datas from the spreadsheet using the app script?
BTW if u submit 2 responses from separate devices at the same time, in the excel file it only shows one of the responses but it duplicates it.
Please provide a small project where data display in google forms from Google Sheet by search option
Can you please tell me how to get drop-down and check box values into sheets
Great video!!! What would be the script if I have check boxes with the multiple choice questions?
Great video Curt! Got a few questions if ya don't mind. Firstly, are they advantages to using apps scripts to populate the google sheet as opposed to just linking the form to the sheet? Secondly, do the questions on the google form need to match the column names in the google sheet that the responses are populated with? for example, if a question on the form is "What is your date of birth?" can the corresponding column name in sheets be "Birthdate?"
Thanks mate!
Tks Curt so much! Interesting video!
amazing video sir. Thanks!
I want to create a form that logs something that gives me a clue as to who or what workstation, or something, submitted the form, without asking the user who they are, so I can see if people are submitting multiple answers without making them log into google when using the sheet. Seems like if I just add one more parameter to the appendrow function, that would do it. But what? is there a function that can access the responder's ip address, or workstation name, or location (the answers will all come from managed chromebooks, I can specify location in the admin console)?
Hi, I just made a Google form and link it with a spreadsheet, but suddenly the number of responses doesn't correspond to the number on the spreadsheet, I got 23 response on the form and only got 20 on the spreadsheet, and I don't know how it happen. Is it a bug or something? Is there any way to fix?. Thank u for anyone who would answer my question.
I feel the script is able to run one item at a time. If that is the case, we can use safety precautions as such one write at a time, and other requests standing in a queue.
@@pramodvajire4471, that seems like a good idea. Where could I get info to let the forms stand in queue as mentioned?
Similar problem 3 responses missing , how did you resolved it ?
I’m a complete newbie. I need my form to send data to more than one sheet. How do I make this script send to two sheets. Added bonus if you could explain how to set it up in a different column so that it goes to the next row, but starts in column B instead of A. I hope that make sense!
hmm looks like when setting up new form triggers there is no longer the permission for viewing and opening sheets. How can that permission be added since this method does not work anymore?
Here's an example of an Apps Script code that retrieves form responses from Google Forms and inserts them into a Google Sheets spreadsheet:
javascript
Copy code
function onFormSubmit(e) {
var formResponse = e.response;
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var row = [];
headers.forEach(function(header) {
var value = formResponse.getResponseForItem(header);
row.push(value ? value.getResponse() : '');
});
sheet.appendRow(row);
}
To set up this code:
Open your Google Forms.
Click on the vertical ellipsis (⋮) in the upper-right corner.
Select "Script editor" from the menu. This will open the Apps Script editor in a new tab.
In the Apps Script editor, replace any existing code with the provided code.
Save the script by clicking on the floppy disk icon or pressing Ctrl+S.
Close the Apps Script editor tab.
Go back to your Google Form.
Click on the vertical ellipsis (⋮) again and select "Script editor" to reopen the Apps Script editor.
In the Apps Script editor, click on the "Triggers" icon (clock-shaped) on the left sidebar.
Click on the "+ Add Trigger" button.
Configure the trigger settings as follows:
Choose which function to run: onFormSubmit
Choose which deployment should run: Head
Select event source: From form
Select event type: On form submit
Click "Save" to create the trigger.
Now, whenever a form response is submitted, the onFormSubmit function will be triggered, and it will add the response to the active sheet of your Google Sheets spreadsheet. Make sure to have the corresponding headers in the first row of the sheet to match the form question titles.
Note: Remember to save and set up the trigger for the script to work correctly.
Curt, can you make a script that will allow me to enter a field on a Form and search the linked Spreadsheet for an existing match and then populate the Form with the data from the Sheet?
PLEASE WE WANT THIS VIDEO
Yes please
I want this! Please!
Yes please!
SO think you would have a hit with combining this with your Creating a estimate geneerator using a form builder as those two would combine the most commonly sought out videos of what people want to do for learning programming like this in the first place. However if you already did it than clap clap cause I find myself coming back to learning form you more than any other programmer to sheer tone of voice and non schalant BS lets getr done attitude. Clap clap again.
Did you type all that script or was it automatic from the creation of the form?
How to give URL of uploaded photos in google sheet
Thanks a lot for this.. don't you have one where you convert/translate the google sheets into google docs table...?
This is the closest video I have to that.
th-cam.com/video/sJCbD3LGGuo/w-d-xo.html
Is it possible to use the Apps Script to resend a google form with the same answers for over a period of time?
or can we create like a repeat table on google form?
Why did you do this, when Google Forms does this anyway ?
To demonstrate how to use apps script, this is helpful when you want to customize / do some business logic on how the record will store in google sheet.
How to m make table to collect data in google form?
Great Video, how would you implement a linear scale question?
How to upload image to spreafsheet wirh apps acript, thanks
U can’t upload a file to a sheet but u might get a url to google drive
You will get the URL in the column of Photo and then you can make anouther clomn and use the formula "=image("URL") to preview the image
is there a way to get the answer of an item, but not when finishing the form?
Let me explain: I think of making a dropdown menu dependent on what the user marks in a test. So, in one section the user marks an option, and in the next section the options are loaded according to this answer.
You can achieve this with conditional questions, you will have to set the question as multiple choice, then close to the "required icon", click on the three dots and select "Go to section based on answer".
@@blessedkanu16 thanks for the reply, but I wonder if this would be possible with script. If the response in one section of the form could be used as a variable to then generate changes in the next section of the form
Can you write a script that can duplicate a row the number of times a comma appears in a column? I have a Google Form that's for scheduling appointments for blower door testing and sometimes there are multiple addresses for one appointment. However, I need each address to be it own entry with the same base info(first name, last name, company name, date requested, etc..)? Is this possible? Is this difficult? Anything? LOL
My form and sheet are linked. I thought to use functions/formulas but I don't think I know what I'm doing
I can only think of the SPLIT() function once in Google Sheets.
=SPLIT(text, ",")
Then you would have an address per column across; maybe put that right at the end so that no data is overwritten.
=TRANSPOSE(SPLIT())
Would do the same for the addresses but in rows. The rest of the data wouldn't be carried into those rows though
Google Apps Script Code in Video Below:
function onFormSubmit(event) {
record_array = []
var form = FormApp.openById(''); // Form ID
var formResponses = form.getResponses();
var fromCount = formResponses.length;
var formResponse = formResponses[fromCount - 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]);
}
function AddRecord(first_name, last_name, color) {
var url = ''; //URL OF GOOGLE SHEET;
var ss= SpreadsheetApp.openByUrl(url);
var dataSheet = ss.getSheetByName("Sheet1");
dataSheet.appendRow([first_name, last_name, color, new Date()]);
}
sir can you trap duplicate entry in google form just like ACCESS CODE. what is the script behind that. thank you.
Yo Chode why you using that Google form from windows 98? They got running water where you from?
Hi
why do you made this video and what the reason behind it, if you made video so you had to pull the data through google sheet in google form reverse video , you should to made it