Seeking Advice: Handling API Errors and Providing Real-Time User Feedback

[mwit000]

Hello!

I'm currently working on an AppSheet app that submits data via a form to a Google Sheet.

It's set up in the way AppSheet appears to have intended: the data source is Google Sheets with specific columns, the users are able to submit data via a form view. This works as intended - a new record is added. From there, an automation is triggered (on adding a new row to our sheet), which sends this data to an external API, which then processes the submission and updates our backend system. The integration is working well overall.

The Challenge:

However, I’m running into an issue with handling errors from the API and providing real-time feedback to the user. Specifically:

• If the API returns an error (e.g., 500 server error) or if the user attempts an action that is "illegal" (based on the API logic), there’s currently no way for the user to know that something went wrong.
• Since the API call is handled by an automation after the data is submitted to the Google Sheet, the user doesn’t receive immediate feedback. They assume everything went fine, even if the API failed.

What I’m Looking For:

I'm trying to figure out a way to:

1. Notify the user in real-time if the API submission failed or was rejected.
   
   
2. Display a status (e.g., "Success," "Failed," "Pending") in the AppSheet app after the form is submitted, based on the response from the API.

I’ve considered:

• Adding a status column in the Google Sheet to be updated based on the API response, but this doesn’t seem to provide real-time feedback in the app, as the column is updated after some time by an automation, possibly after being read by AppSheet.
  
  
• Redirecting users to an intermediary view that could show the status, but integrating the API response into this flow in real-time is challenging. Also, again - there are no guarantees that it will be displayed after it gets updated and not before.
  
  
• Using some automation configuration magic: I suppose I could just send a notification to the user that he submitted invalid data - but is that my only option?

Has anyone tackled a similar issue or found a creative workaround to handle API errors and provide real-time feedback to the user within AppSheet? Any advice, suggestions, or best practices would be greatly appreciated!

Thanks in advance for your help!

[WillowMobileSys · Accepted solution]

AppSheet does not presently support Request/Response type interactions directly with external API's.  It can only send the data TO the external API but has no way to capture and process any responses.

EDITED:  It has been pointed out below that AppSheet DOES support return payloads.  Not sure how I missed this.  I don't know how it works or exactly the limitations but will get familiar with it soon!

You can also implement a Google App Script which returns a response.  This is supported.  So your automation calls the script, the script make the external API call, grabs the response and parses that into a return value to AppSheet.  You can then have subsequent steps in this automation that react the returned value from the script.

The next best option is to have the external API submit its response to the AppSheet API.  But this requires you to be able to implement that ability on that external service which is typically not the case.

A third option it to use an integrator service such as Make.  These services have connectors to many API's.  If one of them has a connector to the external API, then you may be able to build a process that can submit to the external API, parse the response and then submit to AppSheet API.  I do know that Make has integrations with AppSheet API.

[WillowMobileSys · Accepted solution]

AppSheet does not presently support Request/Response type interactions directly with external API's.  It can only send the data TO the external API but has no way to capture and process any responses.

EDITED:  It has been pointed out below that AppSheet DOES support return payloads.  Not sure how I missed this.  I don't know how it works or exactly the limitations but will get familiar with it soon!

You can also implement a Google App Script which returns a response.  This is supported.  So your automation calls the script, the script make the external API call, grabs the response and parses that into a return value to AppSheet.  You can then have subsequent steps in this automation that react the returned value from the script.

The next best option is to have the external API submit its response to the AppSheet API.  But this requires you to be able to implement that ability on that external service which is typically not the case.

A third option it to use an integrator service such as Make.  These services have connectors to many API's.  If one of them has a connector to the external API, then you may be able to build a process that can submit to the external API, parse the response and then submit to AppSheet API.  I do know that Make has integrations with AppSheet API.

[scott192]

You have been able to get responses back from webhook calls for a while now...

https://support.google.com/appsheet/answer/12738438?visit_id=638609773263181863-2358992568&p=webhook...

[WillowMobileSys]

Oh Wow!  How have I missed this?  Thank you for pointing it out.  I'll have to play around to see how it works!!

[Suvrutt_Gurjar]

To add to @scott192 's guidance, if you are using Google Apps Script (GAS) , I believe you could try to use the following method for example to get the status code.

Class HTTPResponse  |  Apps Script  |  Google for Developers

The below very simple implementation runs a minor GAS script to fetch the status code from the NASA public API APOD ( Astronomy picture of the day) I could not produce error condition such as giving a wrong date etc. So teh returned code was always 200. But I hope you will get the idea.

The following GIF shows the Status code being populated in the App's card view.

The GAS code is as follows that I called through a "Call a script" automation in AppSheet app. 

function callAPOD(apodDate) {
  // The APOD URL

const url = "https://api.nasa.gov/planetary/apod?api_key=API_KEY&date=";
     

  const endpoint = url+ apodDate

  var response = UrlFetchApp.fetch(endpoint);
 
  Logger.log(response);

 
  var responseCode = response.getResponseCode();
  Logger.log("response code: "+responseCode);

    return responseCode
   
}

The bot part looks like below

Show More

Overall bot:

GAS calling step:

Return code updating step:

[mwit000 · Accepted solution]

Basically I decided to more or less go with what @WillowMobileSys  and @scott192 have pointed out - I grab a response from the API and, if an error happens, proceed to send a notification to the user's device from the automation. This does introduce a bit of a delay between the error actually happening and the user being notified - which might not be a perfect solution for more fast-paced scenarios, but for my use case gets the job done.

Thanks for your replies.

[WillowMobileSys]

---

@mwit000 wrote:

This does introduce a bit of a delay between the error actually happening and the user being notified - which might not be a perfect solution for more fast-paced scenarios

---

For what it's worth, when you are dealing with external services in ANY platform, the best that can be achieved is "near-real time" management of the responses.  Because the communications happen over cellular or WiFi, there are inherent delays from the start.  Then add processing time on top of that.  The delays could be seconds, could be minutes or might not happen at all if there is any communication failures.

If a system MUST have responses as fast as possible for immediate user interaction, then using a cloud-based solution is NOT the answer.   A more custom, targeted solution would be required.