Send Pre-filled Google Forms with data from Google Sheets. Mail Merge for Gmail (send bulk emails) - gsuite.google.com/marketplace/app/mail_merge_with_attachments/223404411203 Document Studio (generate documents) - gsuite.google.com/marketplace/app/document_studio/429444628321 Google Form Notifications (Email form respondents) - gsuite.google.com/marketplace/app/email_notifications_for_google_forms/984866591130
Great Video! I have a question related to this. After the user edits/enters the missing information, a new data tuple(row) gets created and the previous entry is also present. The sheet now contains duplicates. Is there a way to avoid this? To be precise, I want only the updated information in my google sheet.
As usual, very nice video from you Amit. Thank you. Btw, how do you change the volume of the background music? Is there an automatic way to slow the music when you speak?
Great tutorial, but this google forms Google Sheets integration is still missing some key functionality in order to be useful, based on my experience and the other video commentators below. I've created a sheet that lists details about various store locations where each row represents a store, and columns are various details of those stores. The store number is a primary key. I would like a google form that allows any user to enter a store number and all existing fields can be recalled into the form for entry OR editing - upon submitting the form the row for that store would then be updated (without creating duplicates). Is this possible?
@@krishanharry7916 thanks for the response. If I may ask, what platform did you find to help with this issue? Is there like a free website to make a table with the search function?
Good info... The submission of the google form using prefilled link , puts a new entry in google sheets.... is it how it works ? or is there a way to update the existing row entry of google sheeets ?
This was great! But it needs one more component! Now that everything is set up, I need to be able to have each link AUTOMATICALLY SUBMIT from within Google Forms! Without that feature, I have to click every single "form creating" link and then manually click "submit" for each unique Google Form!!! So, how do we get these links to auto-submit the Google Form that it creates?????
Thank you Amit. I have a question, once a user receives an email an updates any information in the form, does that updated information show up in the Excel spreadsheet you had prefilled? if not, where does the updated data go to? Thanks
Someone please help! When I copy the form link, it is shortened and does not give me the option to paste a "code," is there a program or special way to get to that pivotal step?
Hey i want to make a google form wherewith any body entering emp no will be provided with data available against that emp no and they only have to fill blanks or remaining data.
I am having one issue with this method, I am using this method for internal task updates. So if one employee has multiple jobs when he tries to update one row, it replaces other row he previously added. How to keep each row unique if only one person has to update the sheet for multiple tasks?
If the Google Form has the setting "Collect email addresses" turned on, so that is the first question will this still work? I'm running into issues where the substitue value is not showing with my updated URL and I'm wondering if it's because the first page is the email address collection? It's still populating the fake data I entered when getting the pre-fill link
The Google form is not populating with the data from the Google sheet after I updated the pre-filled link with SUBSTITUTE; the dummy data remains in the form when I click on the link. What am I doing wrong?
good tutorial make more video kindly make video for taking order of Multiple sizes of inventory with specific qty in per box for distributors to customers
This is amazing! You made it so easy! Can you tell me why my link might be "unlinking" once I change the formula? It still works. But I can't just click on it. I have to copy and paste it. Thank you!
Great job, very easy formula. Hi Amit Agarwal, can you help me to create QR code using Google QR API in the same Google Sheet with the URL. So that can scan and input data at any time. Thank you in advance
One suggestion, instead of substitute, we can use concatenate which I feel would be easier. Btw, the tutorial is well explained. Thanks for making such videos.
Good evening sir 🙏 Thanks for the video. Please add a video how to make the name of the particular respondent appear automatically in confirmation message that we receive after submitting the Google forms. Thanks. Pradeep Parichha
This was really good. Thank you. Similar to Nathan Lipson's comment below, I have a follow-up question on this topic. Can you script it so that the form allows for "Other" followed by the user typing in the "other". Then, can the new entry be appended to the Google Sheet column automatically. The result would be the form user can enter something not on the list but the next form user would see the addition by the previous user. What do you think?
sir i have a Question, Let suppose we are conducting a Test using Google form, and at entry level we have created 2 Questions Q1: Roll Number Q2: Student Name, what i want is student enter his/her roll number manually but Student Name gets auto-filled in 2nd question's answer from a spreadsheet.
Very helpful - I will definitely be using this. Thank you for putting this video together (but please please kill the music, I almost stopped watching it was so distracting, I am glad I didn't but it made it really hard to keep going)
Thanks Amit for a great tutorial - any chance you can write the array formula or something else to fill in the PreFilled link automatically rather than having to drag it down manually? when I do mine all the PreFilled links have the same value as the 1st cell & it doesnt get filled down with the updated cell values - in my formula: ={"PreFilled Link";ARRAYFORMULA(IF(LEN(X2:X)=0,, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE('Form Link'!$A$1,"PATIENTID",X2),"2021-05-27",C2),"15:15",D2),"Next+Day+Result",E2),"100",F2)," ","+")
Hi Amit, I would like to request you to make a video on Plan V/s Execution. The requirement is that the Plan will be created through Forms by the Employee for a Week. And in next week He will open a pre-filled link and fill the Execution part of the plan. And google will calculate if its achieved or carried forward. Such Carry forward can be allowed only three times. Employee should also get a list of pending tasks on weekly basis. Can you create a video. Please. It will be Helpful for all.
This was so helpful, but right after I start using the substitute formula, the link is black, so I can't click it. Does anybody know how to solve this?
Link broke because you did not finish the formula, skipped last step SUBSTITUTE(" ","+") which replaces spaces with plus character. Formula should be something like: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE('Form Link'!$A$1, "Question1",A2),"Question2",B2),"yyyy-mm-dd",C2),"Question4",D2),"Question5",E2)," ","+")
please help please help In my case I hope if you can help me: I have a google spreadsheet which contains only customers IDs who I issued a certificate to them So how could I let my make a google e form reply the customer when they write their ID so if the customer ID was one of the ids in my google spreadsheet, the the form will reply them automatically (Yes your certificate ready) if not (the customer ID was not in my google spreadsheet) the the form will reply them ( No please wait your turn) I hope that I succeeded in communicating the information to you clearly
Practically useless if you can't easily include read-only fields. If a user can change key fields like IDs or email addresses (which are often used as a key), then I see little purpose for it.
This is amazing, answered questions google couldn’t. Appreciate you
Send Pre-filled Google Forms with data from Google Sheets.
Mail Merge for Gmail (send bulk emails) - gsuite.google.com/marketplace/app/mail_merge_with_attachments/223404411203
Document Studio (generate documents) - gsuite.google.com/marketplace/app/document_studio/429444628321
Google Form Notifications (Email form respondents) - gsuite.google.com/marketplace/app/email_notifications_for_google_forms/984866591130
GREAT video - very complete. As long as I'm sending a form link in the email, I should also be able to prefill the email address as well.
Thankyou For this Video. This video saves my Job life
Hi Amit! Thanks! Everything worked like a charm - but how do I make it so that what people fill/edit in the forms is actually updated into the sheet?
Highly appreciated for sharing it with the community. It is really good. Thanks.
In place of Substitute can also use "&[Cell Ref]&" eg. "&A2&"
In fact very well explained video
Very simple and beautifully explained. Thank you.
Exactly what I was looking for! The example was very good too. Appreciate you taking time to do this.
Great Video! I have a question related to this. After the user edits/enters the missing information, a new data tuple(row) gets created and the previous entry is also present. The sheet now contains duplicates. Is there a way to avoid this?
To be precise, I want only the updated information in my google sheet.
Thank you Amit, for this tutorial! I have been looking for a solution like this for a long time now. Great explanation, it really helps!
As usual, very nice video from you Amit. Thank you. Btw, how do you change the volume of the background music? Is there an automatic way to slow the music when you speak?
Hi Amit, I want to populate all other fields once the employee code is filled by user 🤔. It will be a great help if we can have a video on this.
Thank You, very well explained! 😊
Great tutorial, but this google forms Google Sheets integration is still missing some key functionality in order to be useful, based on my experience and the other video commentators below.
I've created a sheet that lists details about various store locations where each row represents a store, and columns are various details of those stores. The store number is a primary key. I would like a google form that allows any user to enter a store number and all existing fields can be recalled into the form for entry OR editing - upon submitting the form the row for that store would then be updated (without creating duplicates). Is this possible?
hey there, sorry it's been 9months, I was wondering, did you ever find a solution for this?
@@andysabur4966 nope, not with this platform.
@@krishanharry7916 thanks for the response. If I may ask, what platform did you find to help with this issue? Is there like a free website to make a table with the search function?
Great tutorial. I have a question: How can I let readonly fields?. For example, user can't edit his name in the form.
got the answer?
This was great. Thank you!
Excellent tutorial. Thank you.
You save my life with this ;)
Thank you
Good stuff! Thanks for the tutorial.
Good info... The submission of the google form using prefilled link , puts a new entry in google sheets.... is it how it works ? or is there a way to update the existing row entry of google sheeets ?
Thank you bro. This method really helps me to key in all manual survey from excel into google form easily. God bless u brother
I am also searched what you shown in the video, Let me Try, Thank you for your Video
this is really good, than you for posting it.
Thanks
it was very helpful for me.
YOU AREA MAGICIAN MAN! THANKS!
Great video, easy to follow, and hit the spot! Thnx
This was great! But it needs one more component! Now that everything is set up, I need to be able to have each link AUTOMATICALLY SUBMIT from within Google Forms! Without that feature, I have to click every single "form creating" link and then manually click "submit" for each unique Google Form!!! So, how do we get these links to auto-submit the Google Form that it creates?????
Thank you Amit. I have a question, once a user receives an email an updates any information in the form, does that updated information show up in the Excel spreadsheet you had prefilled? if not, where does the updated data go to? Thanks
Thankyou for making such useful tutorials.. you are a lifesaver
Great work, keep it up
You're a genius! This was super helpful and so well explained. Thank you thank you thank you!!
It is really what I wanted. The link generated is too lengthy to share with SMS or WhatsApp. Is there any way we can shorten the link ?
Someone please help! When I copy the form link, it is shortened and does not give me the option to paste a "code," is there a program or special way to get to that pivotal step?
Can we use the same formula for questionnaire? Like for 100 people
Hey i want to make a google form wherewith any body entering emp no will be provided with data available against that emp no and they only have to fill blanks or remaining data.
How does one do multiple choices with this?
Incredibly well explained. Thank you so much!
i wana do like if i fill employee id all the other details are automaticaly filled
I am having one issue with this method, I am using this method for internal task updates. So if one employee has multiple jobs when he tries to update one row, it replaces other row he previously added. How to keep each row unique if only one person has to update the sheet for multiple tasks?
If the Google Form has the setting "Collect email addresses" turned on, so that is the first question will this still work? I'm running into issues where the substitue value is not showing with my updated URL and I'm wondering if it's because the first page is the email address collection? It's still populating the fake data I entered when getting the pre-fill link
The Google form is not populating with the data from the Google sheet after I updated the pre-filled link with SUBSTITUTE; the dummy data remains in the form when I click on the link. What am I doing wrong?
I'm having the same issue, were you able to find a solution?
good tutorial make more video kindly make video for taking order of Multiple sizes of inventory with specific qty in per box for distributors to customers
Excellent idea and great tutorial! Thank you!
This is amazing! You made it so easy! Can you tell me why my link might be "unlinking" once I change the formula? It still works. But I can't just click on it. I have to copy and paste it. Thank you!
Great video! Many thanks. Can you please share the formula where you inserted the substitute function in the spreadsheet? Thanks again.
Great job, very easy formula. Hi Amit Agarwal, can you help me to create QR code using Google QR API in the same Google Sheet with the URL. So that can scan and input data at any time. Thank you in advance
One suggestion, instead of substitute, we can use concatenate which I feel would be easier.
Btw, the tutorial is well explained.
Thanks for making such videos.
Yes, CONCATENATE is an option - I prefer SUBSTITUTE since it doesn't require fiddling with the actual Google Form prefilled link.
@@labnol does this have to be user-specific? Can I make it so this works for every student in my district the minute they sign in to access the form?
The form creates a new row in sheet instead of editing the same row. What am I missing?
Excellent Tutorial
Thank you so much! Wonderful tutorial!!
Hi Amit, how do i create dynamic drop-down list based on the previous selection in google forms
Good evening sir 🙏
Thanks for the video. Please add a video how to make the name of the particular respondent appear automatically in confirmation message that we receive after submitting the Google forms.
Thanks.
Pradeep Parichha
This was really good. Thank you. Similar to Nathan Lipson's comment below, I have a follow-up question on this topic. Can you script it so that the form allows for "Other" followed by the user typing in the "other". Then, can the new entry be appended to the Google Sheet column automatically. The result would be the form user can enter something not on the list but the next form user would see the addition by the previous user. What do you think?
Hi Amit, how can we bulk upload in the drop-down list in the sections of Google forms
I subscribed for this. Thanks Sir :)
Sir, I wanna ask, how to make random responses? example: I want to make my response is 200
How to do this with document studio?
great tutorial
can we prefill data in google forms thru excel.
sir i have a Question, Let suppose we are conducting a Test using Google form, and at entry level we have created 2 Questions Q1: Roll Number Q2: Student Name, what i want is student enter his/her roll number manually but Student Name gets auto-filled in 2nd question's answer from a spreadsheet.
Will this work with a google form published on a website?
Very helpful - I will definitely be using this. Thank you for putting this video together (but please please kill the music, I almost stopped watching it was so distracting, I am glad I didn't but it made it really hard to keep going)
I have a question: How can I let read only fields?. For example, user can't edit his name in the form.
same question as you... did you manage to find the soulution for this?
Really Nice, How would you to use it with check boxes ? If I want to import answers from a G sheet column to a google form.
I'm wondering this as well. I haven't figured it out yet.
Thanks Amit for a great tutorial - any chance you can write the array formula or something else to fill in the PreFilled link automatically rather than having to drag it down manually? when I do mine all the PreFilled links have the same value as the 1st cell & it doesnt get filled down with the updated cell values - in my formula:
={"PreFilled Link";ARRAYFORMULA(IF(LEN(X2:X)=0,, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE('Form Link'!$A$1,"PATIENTID",X2),"2021-05-27",C2),"15:15",D2),"Next+Day+Result",E2),"100",F2)," ","+")
How can I take your services.
Hi Amit, I would like to request you to make a video on Plan V/s Execution. The requirement is that the Plan will be created through Forms by the Employee for a Week. And in next week He will open a pre-filled link and fill the Execution part of the plan. And google will calculate if its achieved or carried forward. Such Carry forward can be allowed only three times.
Employee should also get a list of pending tasks on weekly basis. Can you create a video. Please. It will be Helpful for all.
You need a software for this, not sheets
Nice job..!
Amazing
hello, im a new user, anyone knows if using the same sheet, i can created 2 diferents documents and send 2 dirents e mail
Thank you for such video. Requesting you to help me another channel who creat such videos in hindi Language.
Can we replicate this using Survey Monkey?
This was so helpful, but right after I start using the substitute formula, the link is black, so I can't click it. Does anybody know how to solve this?
Link broke because you did not finish the formula, skipped last step SUBSTITUTE(" ","+") which replaces spaces with plus character. Formula should be something like: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE('Form Link'!$A$1, "Question1",A2),"Question2",B2),"yyyy-mm-dd",C2),"Question4",D2),"Question5",E2)," ","+")
please help please help
In my case I hope if you can help me:
I have a google spreadsheet which contains only customers IDs who I issued a certificate to them
So how could I let my make a google e form reply the customer when they write their ID so if the customer ID was one of the ids in my google spreadsheet, the the form will reply them automatically (Yes your certificate ready) if not (the customer ID was not in my google spreadsheet) the the form will reply them ( No please wait your turn)
I hope that I succeeded in communicating the information to you clearly
Practically useless if you can't easily include read-only fields. If a user can change key fields like IDs or email addresses (which are often used as a key), then I see little purpose for it.
Then there's soft soft ruining the content for my new
Hi
What the hell is that background music can't even play this out loud in office
Useless