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