Wednesday, July 10, 2013

Google Spreadsheet Updated status from Forms

Question:

( by gahvin )

I'm trying to build a spreadsheet that automatically updates certain cells based on form responses that come in another sheet...

Screenshot of 'Form Response 1' Sheet:



"Sheet 1" contains the table I'd like to update based on the "Form Responses 1" sheet (which contains some example responses). I've tried different ways, but can't seem to find an efficient way of setting this up.

Note: One thing that makes is a bit complicated is that the "Comments" field is sometimes blank.

Thanks in advance.

Solution:

Now to get the updated status and comments from the forms, you can get the last row (based on the time in Column A), and then fetch Column B and Column C from it.

Here is the screenshot of Sheet1:



I have the following formula in Cell B2:

=query('Form Responses 1'!A:D;"select C,D where B = '"&A2&"' order by A desc limit 1";0)


I have the following formula in Cell B3:

=query('Form Responses 1'!A:D;"select C,D where B = '"&A3&"' order by A desc limit 1";0)

and so on...


I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

2 comments:

  1. Hi Kishan,

    Thank you very much for this above info.

    I am just wondering if it's possible to set it up so when a form is completed, it adds the data to a new row and keeps adding down the list as more forms are completed?

    Thanks!

    ReplyDelete
  2. How can I delete 1 of response or add in new response to response form and spread sheet update too?

    ReplyDelete