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.
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?
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?
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.
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.
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?
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!
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
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?
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
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?
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?????
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 ?
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?
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)
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
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
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
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.
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.
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?
Very simple and beautifully explained. Thank you.
Highly appreciated for sharing it with the community. It is really good. Thanks.
Thankyou For this Video. This video saves my Job life
In place of Substitute can also use "&[Cell Ref]&" eg. "&A2&"
In fact very well explained video
Exactly what I was looking for! The example was very good too. Appreciate you taking time to do this.
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!
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.
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!
Thank You, very well explained! 😊
Excellent tutorial. Thank you.
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?
Thank you bro. This method really helps me to key in all manual survey from excel into google form easily. God bless u brother
Incredibly well explained. Thank you so much!
Thankyou for making such useful tutorials.. you are a lifesaver
Great work, keep it up
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?
You're a genius! This was super helpful and so well explained. Thank you thank you thank you!!
Great video, easy to follow, and hit the spot! Thnx
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.
I am also searched what you shown in the video, Let me Try, Thank you for your Video
Excellent idea and great tutorial! Thank you!
Thanks
it was very helpful for me.
Good stuff! Thanks for the tutorial.
YOU AREA MAGICIAN MAN! THANKS!
Thank you so much! Wonderful tutorial!!
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.
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?
this is really good, than you for posting it.
You save my life with this ;)
Thank you
Excellent Tutorial
Great video! Many thanks. Can you please share the formula where you inserted the substitute function in the spreadsheet? Thanks again.
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?
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!
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
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?
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
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?
great tutorial
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?????
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 ?
I subscribed for this. Thanks Sir :)
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?
Amazing
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)
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 ?
The form creates a new row in sheet instead of editing the same row. What am I missing?
Nice job..!
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?
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
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
Hi Amit, how do i create dynamic drop-down list based on the previous selection in google forms
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?
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
i wana do like if i fill employee id all the other details are automaticaly filled
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.
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.
Hi Amit, how can we bulk upload in the drop-down list in the sections of Google forms
Can we use the same formula for questionnaire? Like for 100 people
can we prefill data in google forms thru excel.
Will this work with a google form published on a website?
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.
Thank you for such video. Requesting you to help me another channel who creat such videos in hindi Language.
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
Can we replicate this using Survey Monkey?
hello, im a new user, anyone knows if using the same sheet, i can created 2 diferents documents and send 2 dirents e mail
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
Then there's soft soft ruining the content for my new
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.
Hi
What the hell is that background music can't even play this out loud in office
Useless