I have been teaching myself Appsheet for about 6 months now and have found it very useful. I am just starting to use the API features and have gotten stuck using return values from one step in the following steps API call. The idea is to use a return value from the newly created row in the first table as an input value in a secondary API add call to a secondary table. The first table is the main table where purchase orders are created. The second table holds the details of the purchase order, in this case the different items in the order.
I can't figure out how to properly extract the value I need from the first step to use in the second step.
I was able to do it using Apps Script and URLFetch. There I use standard javascript array notation to access the first and only row being returned. This is the return statement from the Apps Script code. In Appsheet these return values can be used properly in a following step which is an Appsheet API call to the secondary table.
I can successfully add a new row in the first table with the first API call, but
However I can not succeed in accessing the key value from the return values in the first step, using them in the second step.
The notation explained in the Appsheet docs [Step name].[return value] returns this error:
"Error encountered in step with name [SReq Details]: Error: Failed Webhook type translation: Key 'Rows.req_key' not found in returned object"
These are the task properties the first step returns, which I understand to be the return values.
I can see that req_value is being returned but its inside another array called Rows and I can not figure out how to access the value inside Rows?
What am I missing?
Solved! Go to Solution.
@OMarciano wrote:
in general I wonder if its possible to extract values from an array as
return values from an API call.
This is how you access data in an array in a returned value. But return values from AppSheet API use a 'complex' format and I am afraid it is not supported at this moment.
@OMarciano wrote:
When I do everything from Apps Script with the Appsheet API and URLFetch I
can access the return value array with no problems to make the subsequent
API calls to the child the table. I wonder if its just best to do it all
there then?
I see no reason why not to go with this. Whatever works using supported functionality sounds fine to me. Apps Script is also more flexible (fully programmable 😁)
Confirmation on your set up.
1. Create a row on the parent table to its child, template of products, this causes a row created event which triggers 2.
2. Create a row in Simple Reqs (= Internal Orders), now done by calling an AppSheet API
3. You want child records added to a newly created Internal Order based on the template of products rows associated with the parent row in 1?
If this is correct, you can actually create another bot which fires on data creation events on Simple Reqs. and do 3 based on data created in step 2. Not sure if this will work for you though depending on whether you have all the information available in the row created in step 2 to perform 3. If not, I would definitely go with Apps Script all the way!
I do not think this type of response is supported
If you want to populate rows to a child table, you can create a parent row without using a Webhook and call an AppSheet API on a row created event so you can pass the parent row's key.
Is there a reason you have to use a Webhook to create a parent row in the first place?
@OMarciano wrote:
in general I wonder if its possible to extract values from an array as
return values from an API call.
This is how you access data in an array in a returned value. But return values from AppSheet API use a 'complex' format and I am afraid it is not supported at this moment.
@OMarciano wrote:
When I do everything from Apps Script with the Appsheet API and URLFetch I
can access the return value array with no problems to make the subsequent
API calls to the child the table. I wonder if its just best to do it all
there then?
I see no reason why not to go with this. Whatever works using supported functionality sounds fine to me. Apps Script is also more flexible (fully programmable 😁)
Confirmation on your set up.
1. Create a row on the parent table to its child, template of products, this causes a row created event which triggers 2.
2. Create a row in Simple Reqs (= Internal Orders), now done by calling an AppSheet API
3. You want child records added to a newly created Internal Order based on the template of products rows associated with the parent row in 1?
If this is correct, you can actually create another bot which fires on data creation events on Simple Reqs. and do 3 based on data created in step 2. Not sure if this will work for you though depending on whether you have all the information available in the row created in step 2 to perform 3. If not, I would definitely go with Apps Script all the way!
Thank you for the detailed response! The solution you are sharing for "Use a value in an array from the response" would theoretically work for other API responses that don't use the "complex" format which Appsheet API does use? That at least gives me some closure as to why it wasn't working and it wasn't just an error on my end.
I have continued developing the Apps Script solution and you are right that its the best way to do. It is working very well now that I have gotten it right. As you mentioned at the end on your response only Apps Script will work if I can't parse the Appsheet API response. I am able to parse it perfectly in Apps Script.
I can confirm and would recommend this solution to anyone else.
User | Count |
---|---|
36 | |
9 | |
3 | |
3 | |
2 |