Google form integration: How to get Customer ID to make sure feedback goes under the right customer

[Aditya_K1999]

I have a google form as a data source for one of the feedback tables.

Its basically a feedback form which goes to the customer, after a service is done. My requirement is that the feedback filled should be recorded under that particular customer in appsheet.

But how do I get the customer ID?

[Aditya_K1999]

please help!

[Steve]

---

@Aditya_K1999 wrote:

But how do I get the customer ID?

---

Is that not collected by the form?

[Aditya_K1999]

If I add a question in the form as Customer ID, how do we make sure its filled correctly? Sorry I am still new to this integration...

[Steve]

I don't have much experience with Google Forms, myself. Looking at it, I'd say your best bet would be to have a separate form for each customer.

[Aditya_K1999]

I understand Steve. But a separate form for each customer, is too much of a manual task to be done. Thank you for looking into my problem!

[Denzil_Snyman]

@Aditya_K1999 As Steve mentioned each  client filling their own form is closest yes, but as a workaround can have their own URL sent to each of them, I have researched this before and there is a way to have a Google forms with a field pre filled using some URL ingenuity.

Once you wrapped your head around setting up a prefilled form using a URL, go create an action to concatenate the pre-filled form link with the entry pulling the client ID column inside appsheet. You can perhaps use an email bot to send each client their own specific link with their ID in the URL. 

Its just an idea As I have seen a prefilled URL work, which then makes this an option to generate links from AppSheet. 

[Steve]

My concern with that is I saw no way in Forms to prevent the user from editing a value.

[Aditya_K1999]

Yes, this is a genuine problem in that workaround.

[Denzil_Snyman]

I have not tried myself but is it possible to use a multiple choices with one value and set it as required, it is not hidden but at hopefully not editable.

I do agree all these workarounds do leave a strange feeling inside me that makes the solution feel ductaped together. 

[Aditya_K1999]

Not to sure, will have to try & see.
Ysa it is ductaped...

[Aditya_K1999]

Yes, I had also seen it, but for some reason it didnt work. Will try again. Thank you!

[Aditya_K1999]

@Mike_Procopio If you can help work out a solution for this. It will be really helpful. Thank you!

[scott192 · Accepted solution]

A full solution to this can be found in the following thread

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Appsheet-and-Google-Form/m-p/524403

[Aditya_K1999]

Hi Scott, I had actually gone through that thread before.

I think the difference between that use case & mine is that the value in the ID column is already there whereas in my case it is blank...what do you think?

[scott192]

Ah yes, I can see the difference.  My solution does not use the new google forms integration, but you should still be able to make a few changes to the integrated solution to get the same outcome?
From what I can see, you would have to take down your publicly available google form.  No need to delete it, just don't publish it's URL.  Add your customer id field to the form with the 'For office use only, please don't delete or amend' etc.  Then continue the rest of the workflow as detailed in that post.

Thinking about it, I advise you not to use the integrated form method for now.  It seems far easier in this situation to just add the Google Sheet that the form outputs to as a table in your app.

What do you think?

[Aditya_K1999]

So even if I go with the sheet method. I am ID field is still blank.

If I am right in the old use case there were 2-3 fields which were already filled in the appsheet. And for the remaining fields to be filled the google form was being used.

But in my case there are no fields filled in prior.

Hope I am making sense here..?

[scott192]

You said that your require the customer feedback after one of your services has been completed?  Well, how do you know when a service has been completed?  How do you know which customer the service was completed for?  Don't you have a customers sheet with details of their email address and so on?

You workflow should be something like this:

1. Complete the service
2. Lookup the completed service details and retrieve the customer details
3. Compile an email to the customer using retrieved details
4. The body of the email includes a concatenated prefilled link to the feedback form
5. Send the email

Does that make sense?

[Aditya_K1999]

I have a customers table, booking table etc.

I have a separate table for feedback with customer ID field in it.

Do you mean to say I have to trigger the feedback form from the service booking table itself.

Sorry, I am not able to connect the dots on how this will work out...

[scott192]

Not specifically from the service booking table, but more at the time the service is marked as completed?  Is that a manual task?  Does somebody have to click a button that updates a field on the record?  Does the service complete workflow trigger an Invoicing workflow somehow?

Work through it as if there was zero IT involved in the process.  It will be a human's job to keep in contact with the customer, check whether services are in progress and their current status.  Once you have done that, you will have a business process rule that you can adapt to an automation bot.  You need to be able to know via record updates when it is suitable to email the customer with the URL of THEIR feedback form.  Their form being the specially concatenated form url with their specific customer id as part of that url.

[Aditya_K1999]

So Scott my purpose is not to send a mail, whereas send a WA msg. That being aside, I tried the way you suggested. I have a feedback status, table in which only when the status is yes, it generates customer id, response id value in a different table by the name of feedback form.

Then basis the same info it generates a link in which the above mentioned details are prefilled. I fill the rest of the details.

But once I am doing that...its creating a separate entry in the back end with the same response id. Response id being the key column it should not work like that..

Am i going wrong somewhere?

[scott192]

The responseid of the google form submission is nothing to do with your customer id.  The same customer could fill your form 20 times and correctly there would be 20 different response ids for that one customer.

You are supposed to be looking at the customer id that you pre-filled for the customer when you sent them a link to your feedback form...

[Aditya_K1999]

Umm Scott, I am able to prefill the fields, that is working fine. But when the form is being submitted by the customer, a response ID has to be generated correct?

[scott192]

The response id is created automatically by the form itself.  You do not need to even include that column on the form.

[Aditya_K1999]

Umm how is that? because the form is getting filled & it generates a timestamp on the google sheet once a form is filled. Is that what you consider a response id?

[scott192]

The timestamp could be good...or even the row_number as the responses come in one row at a time.  But doesn't appsheet add a response id automatically anyway when using the google form integration?

[Aditya_K1999]

@scott192 can you assist?

[Aditya_K1999]

@markcollins please consider this particular scenario also as a problem to be resolved through google form integration itself.

[MiguelPilo]

Aditional to your google foms, include a table to manage your customers.  Be sure to include an email column.

Now you can work with this 2 tables TO GET CUSTOMER ID.
In G_Forms table add a virtual column [Customer_ID] with the formula: LOOKUP("Email Address", "Customer_DB", "Email", "Customer_ID")

Also I recomend to you add a new action in your main G_Forms view just for rows that have not an validate email in Customer_DB. To do this use: "Add a new row to another table using values from this row"