How to call an external URL to validate form data before saving event in edit/add forms?

AppSheet AppSheet Q&A
, ,
1

Hi there! I have a view with a form where an input needs to be checked using another external system to see if its a valid value. Currently I do not find any way to accomplish this. Could you provide me of some guidance?

· What I want:
User fill the form and press save button. A blocking action is triggered before “Saved” event is triggered. That blocking action calls a google app script that internally calls the external system to check if the field is valid.

Alternatives that I think it will be ok: Call the REST API using fetch URL or maybe run a predefined bot process (that calls a google app script).

· What I have been able to understand reading the docs, the app, and the forum Q&A:

  1. Calling a google app script is only possible within a bot execution in a specific event. Here there is no way to choose a blocking save event to prevent any modification. I understand the bot runs after the data is consolidated if I choose event type “Data change”.

  2. Custom actions “Do this(type of action to perform)” does not have any option like these: “External: fetch data from external url” or something like “External: run google app script” or “External: run process (bot process)”.

  3. In the Form view the only possible event to assign a custom action is “Form saved”. It should be something like “Before Form saved”. So this way we could assign a custom action that do whatever we like.

Thank you for your time!

2

There’s no way to perform any action or bot in the middle of a form editing session.

3

So you can potentially trigger a bot sort of how you want by using a detail view with some quick edit columns. The bot will presumably talk to the external source, then write something to the spreadsheet. But the issue is Appsheet doesn’t have a ‘live’ link to the spreadsheet so it will be unaware of this update. You could create a GAS that calls the Appsheet API to pop up a notification on screen. But unless the user does a sync they won’t ‘see’ the update.

Simon, 1minManager.com

4

Thank you both for your replies @Marc_Dillon , @1minManager . The more I think of it I guess the only way is to connect my AppSheet app to the external data source via AppSheet → Data → Add new data source. By now there are few cloud data sources where we can choose from, but there is no “REST” data source, just ODATA. I could use some of those services as a wrapper or so to call the REST service.

I did a quick prototype with a GAS that retrieves the data in a spreadsheet or so in a scheduled manner. Then use this spreadsheet as a data source in the app. And finally add some Lookup expression to the field that I want to validate before saving the form.

Im been able to test it succesfully using this expresion as example:

ISNOTBLANK(LOOKUP([FORM_COLUMN_TO_VALIDATE],"SHEET_DATA_SOURCE_NAME","COLUMN_TO_LOOKUP","COLUMN_TO_RETRIEVE"))

Thanks.