ฝัง
- เผยแพร่เมื่อ 13 ก.ย. 2024
- This video is brought to you by Profound Cloud
Google Forms are one of the easiest ways to collect data from your friends, family, colleagues and more. A great way to make Forms even more powerful is by taking actions upon the responses in a Google Spreadsheet.
A lot of people get frustrated when they insert a formula into the responses Sheet, because the function doesn't seem to carry over for every new entry. It can be very time consuming, and frankly pretty irritating to manually extend the function for every new response.
This video covers a really easy way to use the QUERY function in conjunction with Google Form responses. You will have to create a new tab within the Responses Sheet, and it's definitely a time saver.
To watch the updated video and read the full article on the BetterCloud Monitor, visit: www.betterclou...
![โฮ่ง! (SUGOI) - LYKN [ OFFICIAL MV ]](http://i.ytimg.com/vi/nFp4zjAARFs/mqdefault.jpg)
SO USEFUL! I wish I could give you 100 stars rating. Thank you so much. Just a note for those who didn't understand at first sight, as me: you need to create a new sheet in the same spreadsheet. You put the query function there (you cannot see the new sheet in the video because it's missing the lower part of the monitor)
Excellent. I've been reading so many posts that are as clear as mud trying to solve this problem and you have explained it clearly in under 3 minuets. Thanks.
you saved me hours of searching with this video!
This is EXACTLY what I was looking for, you are a star!!!
I was getting super stressed out trying to figure this out, and your explanation really helped. Great video!
Thank You!!!! This is very helpful and exactly what I had been looking for!!
Brilliant! This was an excellent tutorial that helped me with an issue I've been having. Really appreciate you putting this up.
I love you my man. Thanks a million. Searching this for hours!
Nicely done! Is there a way to insert those totals back into the form? Or in other words, show a running total count on the form itself of the individual counts?
Yes! I am looking for this.
What I'm looking for, and probably many others, is a way for the form to do a little calculation with data entered in, say, field-1 and field-2. The result of this calculation would appear in field-3 upon change of f-1 and f-2 ('f' == 'field', shorthand.) This is the kind of thing that is so easily done with JavaScript in an HTML page, it seems copiously lacking here. Thanks for posting your method involving creating another Sheets spreadsheet workbook: seriously though, that's trading the line or two of code on the input form end for lots of extra weight on the back-end, and it DOES NOT update the Forms input form in real-time. I believe a good example of where that would be useful is given a couple of comments below -- showing the sum of costs of classes as they are selected with the form (just one of a bazillion useful use-cases). I guess the Forms product could be useful in a sort of limited way where the hack you detail, thanks for that!, can be applied, but I do not see how to do it in a more useful way (that satisfies all those real-time entry use-cases....) /..
You sound like Jared from Silicon Valley. Loved the tutorial. Exactly what I was looking for.
Thanks! Had a bit of a tricky time finding anything useful, but this is what I needed :)
such a simple solution to a hugely aggravating issue. Thank you!!!
This is why TH-cam was invented!! Thank you!!
for hide all the zeros form the "Totals" :
=IF(ISBLANK(A2);" "; (SUM(B2:F2)) )
this looks better
Thank you so much. Your video did a great job for me.
Thanks so much, this wasn't easy to find an answer to.
That is exactly what I have looked for. Thanks so much for sharing !
You saved by day 🙏🏼
This helped me out so much why does everyone else have to over complicate everything??
Danny really out here putting in 15 hours of overtime... holy shit.
Thanks for the tutorial!
you saved my life.
BUNDLE OF THANKS!!!
Wow! Thanks so much for this. I've been trying to solve this issue for nearly a week now.
Super helpful, thanks so much! Keep up the good work
Love the video but its a little to fast for me to understand. When you say "create a second spread sheet" are you creating a new spreadsheet, copy of spreadsheet, or sometime else???? I have tried both and neither seems to let me do the query function in the A1 cell. This is exactly what I need so any advice would be awesome! Thanks.
Hi, I've tried following the steps in video, but although I've used the query, the information I enter on my google form is not appearing on the sheet where I used the query on. Numbers only appear in the first spreadsheet, but are not carried on to the second sheet. Any thoughts on what I could be missing?
Great workaround! Thanks.
how can i input data from above row table? or automatic short data from the new insert
Thanks a lot Gooru
Is there any way to showed up the Total value in the form?
Thank-you!!!
Ugh! Finally! You are my hero for the day
What if I have to add formula in 1 column or in the middle somewhere. It’s been 3 years and there is no concrete solution to this issue. I am wondering how google work so inefficiently. Any help on this issue is highly appreciated.
Thanks
Great Video
thanks man.. you have saved me of my headache
You can simply use arrayformula in the response sheet
great video this helped a lot and thank you!
My only problem with this is I would have to edit the first sheet for the edit to appear on the second sheet.
Exactly what I want to do.. am subscribing this channel
can also be done by =ARRAYFORMULA('Form Responses 1'!A:G)
Totally just saved me! thank you so much!
Hi, Really good video. In my case I need that the second sheet always in populated in the first row with the latest information populated last. I have a third sheet where I pull information to make a more complicated report. Can this be done?
Kury - create a new sheet in the responses spreadsheet. That lets the query command look up the cells you need.
Great. very useful. Thanks
copy down add on - try that!
very helpful
how about if i'm gonna get the sum of all numbers in one column?
I found this video and now i am addicted to google sheets but i can't figure out how do i make a timesheet like you have there that enters into a spreadsheet?
this would be awesome for my business if i could add customer name and then the hours spent at their job to then add them all up to be able to invoice at the end of the month.
You are a life saver!
Gracias por la ayuda, saludos desde Chile
Thankyouuuuuu Some much. This is priceless!!!!!!!
Thanks a lot for this video helped me a lot
Muito obrigada!!! Ajudou a brasileira aqui! (E estamos precisando de toda ajuda que vier...)
Fine tutorial but not able to do the steps ! Not able to quary across spreadsheets. If i just use another tab the insert problem persists.
+Allan Jorgensen Finally got it to work. Have not succeded in quarying across spreedsheet but made it work with several tabs in same. Thank you !
Thanks
Is there a way to have the newest response data show up at the top of the spreadsheet instead of the bottom?
Thank you!
Great Tutorial!
I am finding it difficult to create google form which replaces data from previous form responses in google spreadsheet.
As of now, when i submit data from google form, it creates a new row in spreadsheet and inserts data. I want a form which would overwrite the data submitted from previous from responses. The want the spreadsheet to only have one row which displays submitted data. It would be great if you can help. Thanks
If we want some time after add data with same person and same row using other google form it's possible??
If yes then how?
Sir, I have a concatenate formula cell in R2 and dragged the formula to R10 and I want a sort formula in S2, sort cells started from R11 cell. please help me to stay sort cell at R2 onwards
Does anyone know how to show the result of data calculated with formulas on the form so the users can view it? For example, after a user has selected two courses on a course registration form, he can see the total cost of the courses he selected on the form at the end.
I am also looking for this too need to let the person submit the form know the totals from the email receipt option from google form. let me know if anyone know. Thanks
Or you could have just if added sum(A,B,C...) to your query
It seems that this only works if you have already submitted data to the sheet before you set up the query formula. If I set up my formulas on a blank sheet, I encounter the same problem. When my form submits data to the sheet, it pushes the cell references in my Query formula down a row. Does anyone know how to fix this?
David - did you ever get an answer? This has been driving me nuts and I'd like to find a solution - I see you've posted a similar comment on anotehr timesheet response. The 'arrayformula' might work if I wasn't also trying a 'query' in the same given cell...
@@andyrossthomson Hi Andy! Short answer is, "no". I was hoping someone would reply back here (or the other video) with some trick I could use, but alas, it hasn't happened. I have a sloppy apps script work around that I am using to try and solve this problem. It works, but not 100% of the time. I'm not an expert at this stuff, so I'm going to have trouble explaining it I suspect. Also, my solution is built around my very specific context. It involves having a script submit a generic response to my form before having the script create the query formula in the template. Are you working with apps script in your project? If your project isn't too sensitive, I would be happy to take a look at it and see if anything comes to mind. Just let me know.
@@davidspooner3579 I'm doing the same kind of thing - I get a second spreadsheet to 'QUERY' the replies from the form submission, and the second sheet carries a string of calcs to generate chart data, but as with everyone, the form submission 'inserting' cells bumps my second sheet cells down a row with each submission - so the new entry never shows in my second sheet unless I manually copy down the cells with formulae from the row above. Someone posted a few solutions to this but they went right over my head: productforums.google.com/forum/#!msg/docs/h8FtdaXe-I8/CaEDB18bOgAJ - I have also seen workarounds where you use an ARRAYFORMULA to copy a formula down a column so there isn't the issue with bumping rows, but I can't figure out how to get it to work. My stuff is here: docs.google.com/spreadsheets/d/1VZXzFycRtdvj6EP88BjMzv0xRbA6zbIp83adzq9VF48/edit#gid=727196492
Sounds like you want absolute cell references so that the cell reference (example "G2") won't ever change when you fill the formula down a column. Do this by using a $ in the reference, so "G2" becomes "G$2". Then, no matter where you put the formula, it will always look to cell G2 and never shift. Hope that helps you or someone else looking at this since your comment is quite old at this point.
You can use an alternative that supports calculations and Excel formulae in the form itself. clappia.com
This can be solved in same sheet with array formula💯📌💡
You never show how the form/sheet correlate. No good.
THANK YOU!!!