Use return values from webhooks conversion error

[Tsach]

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.

[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.

[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]

..

[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.

[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.

[MCC_Travis]

@Tsach

{ “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 .

[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

1. you send data using POST
2. Store the data in Script Cache
3. make another call using GET
4. 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

[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-id/234370