Re: Use return values from webhooks conversion err...

Tsach · 07-03-2024 04:03 PM

Hi AppSheet Community,
Im encountering an issue with a webhook data action in my AppSheet app. When I trigger a webhook to update certain fields in my table, I receive the following error:

Error encountered in step with name [New step]: Error: 'Set Column Values' Data action ''Set Column Values' Data action 'New step Action - 1'' failed with exception 'Set Column Values' Data action 'New step Action - 1' failed because field '[Field Name]' in table '[Table Name]' could not be computed due to exception Error: Failed Webhook type translation: Conversion error.
The fields that I'm trying to update are of the Price type. The webhook successfully triggers a Google Apps Script function, which processes an image and returns a JSON response. However, it seems there's an issue with converting the returned data to the Price type fields in AppSheet.

Here is the structure of my webhook response:

{ "Field1": 468, "Field2": 60, "Field3": 9565, "Field4": 273, "Field5": 30, "Field6": 5870, "NetSales": 575.6 }
Each field in the response is intended to update a corresponding Price type field in my table.

Has anyone encountered a similar issue, or does anyone have suggestions on how to resolve this conversion error? Any help would be greatly appreciated.

Thanks!

TeeSee1

Not sure what is going on. The object seems fine.

What do you see in the task properties of the webhook?

This is a test Apps Script function I used.

function retASimpleObject() {
  return {"a": 10, "b": 20};
}

which gives me this monitor result in Step01

EDITED: Note I successfully updated a price column [c_price] with the expression [Step 01].[a] + [Step 01].[b] which totals to 30. with the above setting.

Tsach

well , i tried to simplyfy things and still have a problem

What am i missing ?

TeeSee1

What do you see under callGPT?

TeeSee1

One thing I noticed which may or may not matter (should not...) is that I use Call a script type rather than Call a Webhook..

Tsach

Well , that requires license for Appsheet Core , which I dont have yet.
However i have a few apps script runinng from webhook , without any issues , but did not return a value yet to Appsheet.

Tsach

..

TeeSee1

I do not know the technical details but here is what works..

Use Get method instead and create a return value using ContentService (this is required according to Google Document) like

function doGet(e) {
  const output = ContentService.createTextOutput(JSON.stringify({"a":100, "b": 200}));
  output.setMimeType(ContentService.MimeType.JSON);
  return output
}

Well this topic is a bit above my pay grade..

Hope someone can enlighten me on how to write code using doPost.

Appendix:

According to the monitor log, AppSheet Webhook is not handling the redirect, which is an expected behavior, of the response from doPost

If you just do

return {//JSON here}

I get the same error as you do but this returns data in a format different from what AppSheet expects and cannot parse it.

Tsach

Thanks alot , that worked for me as well , however , the Get call doesnt send any row data with it .

TeeSee1

https://stackoverflow.com/questions/25113456/sending-data-to-server-using-get-request-header

This could work for you....

Tsach

thanks , not sure how to implement it on appsheet.
I tried adding the row number only on http headers and then figure all the rest from there , but seems like apps script is not getting it.

{"contextPath":"","queryString":"","parameter":{},"contentLength":-1,"parameters":{}}

TeeSee1

It seems Apps Script's doGet/doPost do NOT support headers.

So what I tried and worked is that

you send data using POST
Store the data in Script Cache
make another call using GET
retrieve the stored data in the cache, do whatever you need to do and return whatever you need to return

Tsach

interesting , how do you do that ?

TeeSee1

This is the GAS

function doPost(e) {
  let cache = CacheService.getScriptCache();
  cache.put('postData', e.postData.contents);
  return true; // this does not matter cause no return value is used from the POST step
}

function doGet(e) {
  let cache = CacheService.getScriptCache();
  const output = ContentService.createTextOutput(cache.get('postData')); //here the content of the cache is read
  output.setMimeType(ContentService.MimeType.JSON);
  return output
}

code I tested with.

https://developers.google.com/apps-script/reference/cache

MCC_Travis

I would assume your data needs to be formatted in the same way Google formats currency before it is returned in the JSON response. Clark over in this Groups thread shows how to do the formatting in GAS. Not sure if this will fix it, but it's where I would start.

MCC_Travis

@Tsach wrote:
{ "Field1": 468, "Field2": 60, "Field3": 9565, "Field4": 273, "Field5": 30, "Field6": 5870, "NetSales": 575.6 }

Silly question, but have you tried sending the data as a string rather than an integer in your JSON response?

{ "Field1": 468, "Field2": 60, "Field3": 9565, "Field4": 273, "Field5": 30, "Field6": 5870, "NetSales": 575.6 }

to

{ "Field1": "468", "Field2": "60", "Field3": "9565", "Field4": "273", "Field5": "30", "Field6": "5870", "NetSales": "575.6" }

I'm not sure how the AppSheet parcer is designed to handle strings vs integers, but it should be worth a try at this point.

Tsach

yes of course tried.
Its a real mystery how appsheet parses the reponses from post call .

SebastianS

As far as I understand, you cannot call a webhook endpoint that requires redirect, as the App Sheet will not follow redirects. Unfortunately, that rules out Apps Script get/post endpoints.

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Return-Values-from-API-call/m-p/698692/thread-i...

Use return values from webhooks conversion error