Appsheet and Google Form

[alhazen]

Hi Guys,

I have a project management App. This app has a table called "job". "Job" has a column called [status]. So when the status is "completed" the bot is triggered to send an email notification to the client. 

Is it possible for a bot to generate a google form link related to the Jobs table? This form will be used by the client to provide ratings and feedback on the results of work that has been completed.

Thank you very much for your help.

[Aurelien]

Hi @alhazen 

---

@alhazen wrote:

Is it possible for a bot to generate a google form link related to the Jobs table? This form will be used by the client to provide ratings and feedback on the results of work that has been completed.

---

Yes it's possible.

You can define a template that will send one or another form that depends on your job.

You can use this syntax:

<<If:[Job]="job1">>Please fill this form: <<HYPERLINK("yourGoogleFormURL1.com", "job1 Questionnaire")>><<EndIf>>

<<If:[Job]="job2">>Please fill this form: <<HYPERLINK("yourGoogleFormURL2.com", "job2 Questionnaire")>><<EndIf>>

Of course, this requires you know which form to send for each job.

Another possibility, if you use a table for the list of jobs, you can attach the url in your table. So you would have this structure for example:

job id	job description	job form
id1	Job 1	jobUrl1.com
id2	Job 2	jobUrl2.com

Then, your template expression would turn into:

Please fill this form: <<HYPERLINK([jobDone].[job form], "Questionnaire for " & [jobDone].[job description])>>

For reference:

Send an email from an automation - AppSheet Help

HYPERLINK() - AppSheet Help

Use If expressions in templates - AppSheet Help

Dereference expressions - AppSheet Help

[alhazen]

Hi @Aurelien 

Thank you so much for your response.

This is the situation:

I have another table called "feedback". This table has some columns which are [id], [job], and [feedback].  The plan is I want to make this table as a structure for Google Forms. Where the Google Form link generated is related to the job table based on the job id.

Does it make sense?

[Aurelien]

Oh, I believed this was not related to your table structure, my bad.

As far as I know, there is no way to create a Google Forms starting from AppSheet. The other way is possible though.

If you are keen on programming, you could give it a try using Apps Script.

For reference:

Use the AppSheet Apps Script Examples Project - AppSheet Help

From my point of view, you would benefit from creating a simple Google Form, that will be sent for every job.

[alhazen]

I am so frustrated with this workflow, I haven't any idea:

My app has two tables called "job" and "feedback". The "job" table has columns named "Id", "Description", and "Status". I created a "saved action" form called "add a new row to another table using values from this row" which adds new rows to the "feedback" table. The "feedback" table has columns named "Id", "Job", and "Comment". When a user adds new data to the "job" table, the "Job" column in the "feedback" table will automatically be filled with the value from the "Id" column of the "job" table. Another user can then fill in the "Comment" column using a Google Form, even if they do not have access to Appsheet.

[scott192]

I have done EXACTLY this workflow using Appsheet and a Google Form.

On your google form, you need to create a field to hold something that identifies the specific job that you are requesting feedback on.  This field is filled in automatically using the PreFilledLink option on the Google Form.  Create you form and then get a PreFilledLink.  This will show you a blank form ready for you to fill in some default values.

Go to your identification field and type something in caps to spot easily in the link...INSERTKEYREFHERE sort of thing.

You will get a fully formed URL back to you and you will be able to spot your INSERTKEYREFHERE text amongst the URL.  

You will use a formula in appsheet to concatenate the part of the url up to the INSERTKEYREFHERE section, the ACTUAL data that will replace the INSERTKEYREFHERE section and then the rest of the URL.

Would you like me to continue explaining this or have you understood the concept and execution?

[alhazen]

Hi Scott, thank you for your response. Please continue your detailed explanation. I really need your help. Thank you so much.

[scott192 · Accepted solution]

So, your Google Form is going to have a space on it that will be filled using the prefilled link from Appsheet.

I usually put some text on the google form to indicate to the user filling the form that the value in the field is 'Office Use Only' and they should not edit/change/tamper with the value.  Unfortunately there isn't a way to hide a field on a form used for this purpose (yet!  I have asked for it to be implemented...) so you have to trust your client not to mess with it.

The value you will put in here is going to be the [ID] of your Job...whichever field is holding that uniqueid() you will insert it in here.  I say 'you', but of course I mean your bot.  This will trigger when the [Status] field is set to 'Complete' or whatever value you need that triggers the feedback bot.

The bot will send an email to the user or users that you require to comment on the form.  The email will have some boilerplate text as is usual and will include the concatenated expression that builds the prefilled url out and includes your jobid.

Anybody that clicks that prefilled url will end up with your feedback form with the specific linking field already filled in.

You should get the results of each form submit sent to a sheet and bring that sheet into Appsheet.  You can then set the specific field with your jobid in it as a Ref type back to your Jobs table.

[jaichith]

---

@scott192 wrote:

So, your Google Form is going to have a space on it that will be filled using the prefilled link from Appsheet.

I usually put some text on the google form to indicate to the user filling the form that the value in the field is 'Office Use Only' and they should not edit/change/tamper with the value.  Unfortunately there isn't a way to hide a field on a form used for this purpose (yet!  I have asked for it to be implemented...) so you have to trust your client not to mess with it.

The value you will put in here is going to be the [ID] of your Job...whichever field is holding that uniqueid() you will insert it in here.  I say 'you', but of course I mean your bot.  This will trigger when the [Status] field is set to 'Complete' or whatever value you need that triggers the feedback bot.

The bot will send an email to the user or users that you require to comment on the form.  The email will have some boilerplate text as is usual and will include the concatenated expression that builds the prefilled url out and includes your jobid.

Anybody that clicks that prefilled url will end up with your feedback form with the specific linking field already filled in.

You should get the results of each form submit sent to a sheet and bring that sheet into Appsheet.  You can then set the specific field with your jobid in it as a Ref type back to your Jobs table.

---

Do you have video for this sir?

[scott192]

In case anyone following this thread wonders how I use this in Apps, I use it to replace the File upload feature of Appsheet.  With a Google Form as the File Upload replacement, I can upload multiple files at the same time, restrict filetypes and sizes, send specific files to specific folders etc.  I admit there is a little bit of Appscript in the sheet that turns the multivalue links in the cells (for when users upload more than one file at a time) into 1 record per file.  It works really well, but I'd love to see a more advanced file picker implemented in Appsheet so that there is no need to offload this task to a Google Form.

[alhazen]

Hi Scott It works properly,

I am using this formula:

"https://docs.google.com/forms/d/e/1FAIpQLSf1Xt3QiJfwIjmx6Glli0DBmgCD5xxxwSUrZDHRO17Jx4nVgg/viewform?..."&[job],

My other question: what to do if we have two columns with default values? Let's say [Id] and [job Id]

This is a prefilled link I generated

https://docs.google.com/forms/d/e/1FAIpQLSf1Xt3QiJfwIjmx6Glli0DBmgCD5xxxwSUrZDHRO17Jx4nVgg/viewform?...

Do you have an Idea?

[scott192]

So, your concatenate expression would be ...

Concatenate("https://docs.google.com/forms/d/e/1FAIpQLSf1Xt3QiJfwIjmx6Glli0DBmgCD5xxxwSUrZDHRO17Jx4nVgg/viewform?...",[ID],"&entry.191410138=",[KEYREF])

The above expression has been mangled by the system and I don't know how to make it look like code...it is truncating the first part of the expression!

[alhazen]

EUREKA!!!

Thank you so much, Scott!!!

[scott192]

You're very welcome! 😀

[scott192]

I'm really sorry but I'm struggling so much with this community board trying to change the expression into what it thinks is a URL.  If anybody can show me how to put some formatted code in I'd be greatful!

Concatenate("https://docs.google.com/forms/d/e/1FAIpQLSf1Xt3QiJfwIjmx6Glli0DBmgCD5xxxwSUrZDHRO17Jx4nVgg/viewform?usp=pp_url&entry.1638999132=",[ID],"&entry.191410138=",[KEYREF])

Phew!  Sorted it!

[alhazen]

is there an alternative so that the prefilled column cannot be edited?

[scott192]

I don't know that one.  I did once think that there could be a way by using form sections. Hide the prefilled columns inside a section that could never be navigated to via an answer given on a preceding section.  I can't remember now why I couldn't make that work.

[alhazen]

Thank you for all your effort, Scott. Let me know when you have new updates about it

[Steve]

Awesome contribution, @scott192!

[scott192]

Thank you Steve!