Google Forms Integration is Now Generally Available!

[Rachelgmoore]

Hello AppSheet Community,

We are thrilled to announce that AppSheet's integration with Google Forms is now generally available (GA)! This powerful integration allows you to seamlessly connect your Google Forms with AppSheet, unlocking a world of possibilities for automating workflows and streamlining data collection.

Why It Matters

Google Forms is a popular tool for gathering information, but its capabilities are limited when it comes to complex workflows and automations. While some members be familiar with AppSheet’s older add-on to collect data using Google Forms,  this new integration offers built-in support for Forms as a full AppSheet data source and automation trigger.

AppSheet's integration with Google Forms makes it possible to:

• Trigger AppSheet automations directly from Google Forms submissions. This means you can automatically send notifications, update data in other systems, or even kick off entire workflows based on form responses.
• Use Google Forms as a data source for your AppSheet apps. This allows you to easily collect data from forms and use it to power your custom applications.
• Create apps quicker than ever. AppSheet creators can use a Google Form as the front end of their application, allowing them to spin up simple data collection apps in minutes.

What’s new

Thanks to your feedback on our earlier post, we’ve added support for Google Forms file upload questions. File upload questions map to Image, Video or File column types in AppSheet based on the Google Forms file type restrictions. Now that this integration is generally available it’s ready and waiting for your production workloads!

How to get started

Follow these steps to get started using the Google Forms integration:

1. Create a new app or open an existing one in AppSheet.
2. Add a Google Forms data source.
3. Configure a bot or automation to trigger on form submissions.
4. Start automating your workflows!

For detailed instructions and additional information, please refer to our help center article: Build an automation using Google Forms. You can also use a Google Form as the “front end” of your application - see Create an app using Google Forms to learn more.

Additional resources

• Quick start: Build your first app and automation using Google Forms
• Google Forms training and help
• Workspace Updates Blog: Build AppSheet automations using Google Forms

We encourage you to share your feedback and experiences with us in the comments below. Your input is invaluable as we continue to enhance and improve this integration.

With gratitude,

The AppSheet Workspace Team

[scott192]

Many thanks for the file upload question type being supported!

[takuya_miyai]

Hi @Rachelgmoore 

This is a great Update.🤩

Just specify the form as the data source and we can access the uploaded file from our AppSheet application

On the other hand, Form supports multiple file uploads, but when specified as a data source in AppSheet, it appears that only the first one can be referenced.

Sheet exported from Form

Form data source on AppSheet

I would prefer to have this disclosed as one of the limitations in the help or other documentation.

@Koichi_Tsuji 

[markcollins]

You are correct about the 1 file limitation. The existing AppSheet file column types only support a single file. This is documented in Create an app using Google Forms.

This note could be added elsewhere in the documentation if there is a place you were looking for this information and didn't find it.

[Koichi_Tsuji]

@markcollins 

Google forms has a native functionality where the user can upload multiple files into a single input field, however, AppSheet only recognize the single (first) file alone, which would cause the issue and reduce the flexibility to use this functionality.  This "gab" must be adjusted by improving the functionality I would recomend.

When we use the Google Sheet/Forms add-on (AppSheet event) , the files loaded to the single fields would be returned as "array (list) of URL" to reach out to the files.  Can you consider to do the same with this new function?   Once we set Google form to allow only one file per File input field, it is fine and stay as we see now. However, the users set the Forms settings to allow the multiple files to single file field, then the table/column generated from the Form turns to be "Enulist" column instead of "File" type column, somthing like 

DocId=xxxx , DocId=yyyy, DocId=zzzz

Once we get those list of id path, then we can easily manipulate the list onto the standalized data using AppSheet Loop Action technique.

Then we can stay with the Google Forms full functionality to allow user to upload the multiple files into the single field.

The new AppSheet/Google Form integration is posting new problems to us, i.e. the table is READ-ONLY. For some use cases it would be fine, but most of our use cases, the table must be editable. So our current workaround is to create new BOT and physical table.  Once the new form is submitted, the bot is triggered and clone form data to other table by using Add new row to another table action. Then we keep using this cloned data for data manipulation and further app functionalties.   

If we could the list (array) of the file path as I suggested, then we can chain Automation (Bot) to split the path to another table to store file path per row for that table.

This is what End users need. It is not a good idea to  limit and placed a constrain to ask the user "you can upload only single file per field"

@Rachelgmoore 

@takuya_miyai 

[takuya_miyai]

Hi @markcollins 

Thank you. I have confirmed that it is mentioned in the English help page.
I was looking at the Japanese help.

Also, as @Koichi_Tsuji -san pointed out, it would be great if you could support multiple file uploads.

End users often request the ability to upload multiple files via drag-and-drop.
A spreadsheet linked to Form can make this happen, but it would be better to be able to do that with the native Form data source.

@devingu 

I'm sure your team will be working on this from now on, but regarding the Google Forms integration, the Japanese page does not yet contain a message that multiple file uploads are not supported.

https://support.google.com/appsheet/answer/14824665?hl=ja

My hope is that this will be fixed as soon as possible.

[takuya_miyai]

Hi @devingu 

Two months have passed since the integration with Google Forms was GA, but the Japanese help pages still have pre-GA descriptions.

@markcollins 

[devingu]

@lizlynch FYI!

[lizlynch]

@devingu @takuya_miyai - Thanks for letting me know! I'll get this addressed. 

[takuya_miyai]

Hi @Rachelgmoore @markcollins 

Multiple file uploads would allow us to develop such an application.

This is a demo application that we are implementing using Sheets linked from traditional Forms.

Thanks😄

@Koichi_Tsuji 

[markcollins]

If we were to add support for reading multiple uploaded files from the form response, would the preference be for Drive URLs as they are exported to Sheets, or for a format with a list of file IDs like @Koichi_Tsuji referenced above?

DocId=xxxx , DocId=yyyy, DocId=zzzz

By default, file upload questions will still be converted as one of the file column types (File, Image Video), but column type can be changed to Text which would allow us to store the list as a string without breaking the AppSheet file handling.

[takuya_miyai]

@markcollins 
Thanks for the reply.🤗

If multiple file uploads are to be added, I would prefer if the DocId can be retrieved in the Enumlist.

> DocId=xxxx , DocId=yyyy, DocId=zzzz

The fact that we can now specify Forms as the data source and get it by DocId was a welcome update for us.
We can now develop smart applications.

[Koichi_Tsuji]

Yes I follow what Takuya confirmed earlier. I also prefer DocId=xxxx , DocId=yyyy, DocId=zzzz style instead of list of full URLs.

I hope you can make this happen!

@markcollins 

@takuya_miyai 

[Koichi_Tsuji]

Hi @markcollins 

After testing new Google Forms integration thoroughly, we came up with one important suggestion for your team.  Once we select the Google Forms as Automation Event trigger, firstly we assumed it is just being triggered by "Submit New forms" type event. In other words, the BOT is triggered as if "Add new row" event in terms of data change event.  However, it was not.  

Google Forms has option to let the responder to edit his/her response.  We tested with our app, where the responder edit the form originally submitted and saved the form. Eventually the BOT triggered to initiate the process.  This tells the Form as Event is triggered by "Add" Or "Update" interpreted as Data Change Event.

Under the current UX, the users do not have option to customize to 1) Trigger bot only when new form is submitted 2) Trigger bot only when existing form was edited 3) Both of 1) and 2)

Likewise APP as event, we should have option to select either from 1) On Submit New Form 2) On Edit existing Form.  

This is pretty much important point for everyone and every AppSheet creators.

Thank you.

@takuya_miyai 

[takuya_miyai]

@markcollins 
I think this is a very important point.

I would like to consider it in one use case.

1. I, the App Creator, was asked by the Forms creator to create an app with Forms as the data source.
2. I set up an Automation that would only run on the first response if there was a response in Forms.
3. However, the Forms creator decided to turn on the option to edit responses.

At this time, App Creator need to add a Condition expression that understands that I am changing the Automation settings and also determines if it is a new record.

This would be a challenging change for the citizen developer.

As @Koichi_Tsuji pointed out, it would be very easy to set up if there was an option to specify Update.

[QFamily]

If possible, i think it would be helpful if in the multi-selector column type options, you had to specify a child table and column to map each photo to. That would allow Appsheet to auto-create child rows and map the photos to the new rows upon form submission. That would keep table structures the same for objects that require multiple child photos. In addition, I think this option is the easiest to visualize and report on.

[baba_sawane]

Hi takuya,

Did you find any way to retrieve the file name in appsheet ?

[JPAlpano]

I just tried this today, and unfortunately, Bots are not being triggered for new Form Responses.
No logs registering in the Bot's monitor either.   I tried executing the bot manually using the "test" function, and it works.  But by submitting a new Google Form Response, the bots do not catch it.

I reported this to support with Case # 2-4499000036743.

I was told that this will be escalated to the specialists.   
I'm assuming this is from your team, @Rachelgmoore ?

[cts-mhawksey]

I've seen Forms submissions not triggering before because your Google Workspace Admin has turned on AppSheet Core Security. Could this explain your situation?

[JPAlpano]

Yes it is.  Kinda funny that I am told to turn off Core Security to make this work.

They need to whitelist Google Form and Events Add-On for GSheets as an internal connection.

Why would we want to turn off Core Security settings to use this?  We need the Core Security Settings to cover risk exposures of data.

[nathanemg]

The Form Submission automation stopped working with no indicators as to why in our app. It is not the Core Security issue that someone else had either as our is setup to allow external connection. Can you escalate our case @Rachelgmoore ? #2-9952000036248

[markcollins]

Thanks for reporting this. We have tracked down an issue where Forms events subscriptions were failing to renew and this was causing events to no longer be sent to AppSheet. This issue was resolved yesterday (July 30) and automation should be firing as expected now. Sorry this wasn't caught sooner.

[nathanemg]

Yes, I noticed it started firing again yesterday. Thanks for resolving. It did suspiciously seem like it occurred 30ish days from the prior incident, so the explanation makes sense.

[nathanemg]

It stopped firing again last week so it definitely doesn't seem resolved. It almost seems like this time it was 2 weeks after the last time it stopped firing if that helps with any internal re-authentication timing, etc.

We moved to appscript on the form results which we had used prior to the Form Bot Automation as it's just too unreliable and silent failures are one of the worst ways for things to fail.

[Koichi_Tsuji]

@markcollins @Rachelgmoore 

Using the tips and tricks Takuya introduced to all within this thread, we can create a functionality to upload the multiple files at once by using Google Forms/AppSheet integrations.

https://www.googlecloudcommunity.com/gc/Announcements/Google-Forms-Integration-is-Now-Generally-Avai...

However the another constrains are given from Google Forms.  Max number of the files are limited to just "10"....

Earlier days, I posted this to the GWS community place.

https://www.googlecloudcommunity.com/gc/Feature-Ideas/Increase-max-allowed-file-number-for-Google-Fo...

We would appreciate if you both take this up internally and have a dialogue with Google Forms to team to lift this constrains so that the user can upload more files from Google Forms which would definitely bring additional values to the AppSheet users.

Thanks you for listening.

@takuya_miyai 

[scott192]

I have been making use of Google Forms and have come across some problems.

The Response ID that is recorded for each form submission is used as the KEY to each submission in the form.  However, it seems that this key is the one that is displayed in the forms interface and URL when stepping through the individual responses.  This KEY cannot be used in the FormApp service and the method form.getResponse(Response ID) to target individual responses and extract data.

Does anyone know of a way to target the actual reponse id (invisible) based on data that appsheet gets from the individual (visible) id?

[scott192]

So I can get to the actual form response using Appscript by grabbing all responses from the form (really wanted to avoid this though) and using the Timestamp from Appsheet to filter out the correct response.  I can use other metadata as well to make sure I have the correct one should 2 or more people submit a response at the exact same time...unlikely, but still!

I wonder why the Appsheet team used that Response ID rather than the more specific and usable in Appscript Response ID?

[markcollins]

As you noted, the response ID used in AppSheet is the public response ID from Forms as exposed in Forms URLs and the Forms API.

It appears that Apps Script came up with their own encoding of the same ID which is not compatible. AppSheet has no access to the response IDs generated by Apps Script and no way to convert the public Response ID to the Apps Script Response ID.

[scott192]

Thanks for replying and the explanation @markcollins .  I can use the timestamp as a filter with form.getResponses() 😊

[Koichi_Tsuji]

@markcollins @scott192 

We experienced the same problems before.  AppSheet Google Forms integration returns "ResponseID". However, once we run GAS to get the response ID, they are totally different.  It does not make sense for us the same Google Product returns the different ID for single response. Yes, we can use the timestamp or other response column to get the PROPER response ID (not Appsheet returns) to do bits and pieces using GAS.  In the perfect world, the AppSheet should return the ACTUAL response ID to make our life far more easier.