Hi everyone,
I am trying to parse the response of an AppSheet bot in my GAS web hook on POST, however, I am not getting much of luck.
I am sending the following JSON to my GAS webhook on POST, it gives me the correct Task ID.
{
"Task": [
<<Start: SELECT(Task[Task ID], [Task ID] = [_THISROW].[TASK ID] )>>
{
"Command": "Task.Assign",
"TaskID": "<<[Task ID]>>"
},
<<End>>
]
}
my GAS code is as follows
function doPost(e) {
var returnMessage = cNO_TEXT ;
var strMessage = "Start execution at " + Utilities.formatDate(new Date(), "GMT+8", "dd/MM/yyyy HH:MM:SS");
var data = JSON.parse(e.postData.contents)["Task"] ;
// do some internal switching and call the appropriate function
}
{"Task":{"Command":"Task.Assign","TaskID":"3"}}
var data = JSON.parse(e.postData.contents)
Is correct.
From there, maybe try:
var task = data.Task
-----
Only other thing I see off, is that your array doesn't seem to be coming through.
Are you re-deploying every time you make a code change?
Hi, thanks for the reply.
I am re-deploying my apps scrip after every change.
I tried your suggestion but it's not working, it's turning out blank when I sent it to my email. I am sending e.postData.Contents concatenated with your code, and I am getting the following where undefined is output for data.Task.
{"Task":{"Command":"Task.Assign","TaskID":"3"}} -- undefined
@Marc_Dillon wrote:
var data = JSON.parse(e.postData.contents)
Is correct.
From there, maybe try:
var task = data.Task
-----
Only other thing I see off, is that your array doesn't seem to be coming through.
Are you re-deploying every time you make a code change?
You mean that the array isn't coming through at all? If that was the case, e.postData.contents would have been blank. Or perhaps I have missed your point completely.
Thanks
Kaz
" I am sending e.postData.Contents concatenated with your code, and I am getting the following where undefined is output for data.Task."
Maybe it's just late, but that sentence doesn't make any sense to me.
------------
"You mean that the array isn't coming through at all? If that was the case, e.postData.contents would have been blank. Or perhaps I have missed your point completely."
I mean that you built "Task" as an array in your Bot:
But the content you said you were getting did not have an array in it:
Hi @Marc_Dillon
It was late for both of us, so I guess I didn't explain myself properly, my bad. The following in output of
e.postData.contents and data.Task
The part on left hand side, before -- is result of e.postData.contents, where as the part on right hand side is result of data.Task. I did this to debug & test output side by side.
{"Task":{"Command":"Task.Assign","TaskID":"3"}} -- undefined
My point is, data.Task is not working, that is, not returning me anything.
I saw your point about having any array, and I guess it might come down to it. I have updated the JSON body in my template to following, but I am still not able to get Command or TaskID out of it.
As @Marc_Dillon pointed out, the value of "Task" should be an array...
Not sure if this is the cause of the issue, though.
My test template is almost identical to yours.
{
"mainTable": [
<<Start: Select(main[id], TRUE)>>
{
"id": "<<[id]>>",
"fld1": "<<[fld1]>>"
},
<<End>>
]
}
not sure what is happening with your implementation..
I am not able access the JSON object within GAS. I was expecting to be able to access JSON data as follows in Apps Script but it isn't working.
//This is based on your sample JSON
var data = JSON.parse(e.postData.contents)["mainTable"] ; //
myID = data.id ;
myfld1 = data.fld1 ;
I should also mention that I have tried the following variation of passing the JSON from my bot but it's giving me an error when I test the bot.
{
{
"Command": "Task.Assign",
"TaskID": "<<[_THISROW].[Task ID]>>"
}
}
Error
"Errors": [
{
"$type": "Jeenee.DataTypes.AppError, Jeenee.DataTypes",
"Severity": 0,
"ErrorCode": "ERROR_WORKFLOW_WEBHOOK_BODY_INVALID",
"Description": "Failed to parse JSON due to Invalid property identifier character: {. Path '', line 1, position 1.. Invalid JSON value starts with: {\"Command\": \"Task.Assign\",\"TaskID\": \"4\"}}",
"DetailedErrorMessage": null,
"TargetField": null,
"IsInExperiment": false,
"LearnMoreUrl": null,
"ComponentDescriptor": {
"$type": "Jeenee.DataTypes.AppComponentDescriptor, Jeenee.DataTypes",
"ComponentName": "BEHAVIOR_WORKFLOW",
"EditorPropertyId": "ErrorPaneHeader",
"SchemaPath": null
}
}
]
Interestingly, my test code below works in Apps Script.
Test code where I am creating the test JSON.
var data = {
'Command': 'Task.Assign',
'TaskID': '3
};
var options = {
'method' : 'post',
'contentType': 'application/json',
// Convert the JavaScript object to a JSON string.
'payload' : JSON.stringify(data).trim()
};
var response = UrlFetchApp.fetch(url, options).getContentText() ;
setAppendDebug(response) ;
my doPost(e) function
function doPost(e) {
var returnMessage = cNO_TEXT ;
var strMessage = "Start execution at " + Utilities.formatDate(new Date(), "GMT+8", "dd/MM/yyyy HH:MM:SS");
setAppendDebug(strMessage) ;
var data = JSON.parse(e.postData.contents) ;
var xCommand = data.Command ;
var xTaskID = data.TaskID ;
setAppendDebug(cNO_TEXT.concat(strCommand, " ", data.Command, " ", xCommand, " ", xTaskID));
} ;
Given the JSON is failing when I test the bot, I think I'll stick to the original JSON with Table and select, and test your suggestion to use subscript.
for my implementation you need to do
myID = data[0].id
because data is an array.
Correct, that's what I expect, but I am getting an error.
I have set the JSOB back to
{
"Task": [
<<Start: SELECT(Task[Task ID], [Task ID] = [_THISROW].[TASK ID] )>>
{
"Command": "Task.Assign",
"TaskID": "<<[Task ID]>>"
},
<<End>>
]
}
And, my GAS code in doPost(e)
var data = JSON.parse(e.postData.contents) ;
var xCommand = data[0].Command ;
var xTaskID = data[0].TaskID ;
Bot monitor shows me the following error. Line 31 is var xCommand = data[0].Command in the above code
TypeError: Cannot read property 'Command' of undefined (line 31, file..
When I was testing, I struggled first and wrote out typeof results to a gSheet to see what I was getting.
What do you get for
typeof e.postData.contents <= I got "string"
typeof JSON.parse(e.postData.contents) <= I got "object"
?
And just to make sure, this is my test Webhook settings for comparison
Basically all default values..
Mine looks exactly the same - default values mostly, and web hook endpoint, table name and JSON.
@TeeSee1 wrote:When I was testing, I struggled first and wrote out typeof results to a gSheet to see what I was getting.
What do you get for
typeof e.postData.contents <= I got "string"
typeof JSON.parse(e.postData.contents) <= I got "object"
I got the below - very interesting the result of typeof(json.parse) (2nd line) but I am getting it as an object value when I tried to write json.parse result (4th line) in the sheet.
type of e.postData.contents -->string
type of JSON.parse(e.postData.contents) -->string
e.postData.contents -->{"Task": [{"Command": "Task.Assign","TaskID": "3"}]}
JSON.parse(e.postData.contents) -->[object Object]
I am completely lost..
Have you tried contacting AppSheet official support?
Both are services provided and supported by Google 😁
You're right. I shall do it now.
I was curious, so I ran a little test of my own.
If you have not figured this out, here are my results.
function doPost(e) {
const sh = ss.getSheetByName('test');
const contents = e.postData.contents;
sh.getRange(sh.getLastRow() + 1, 1).setValue(contents); //This shows the whole JSON object
try {
const j = JSON.parse(contents);
sh.getRange(sh.getLastRow() + 1, 1).setValue(Array.isArray(j["mainTable"])); //TRUE
sh.getRange(sh.getLastRow() + 1, 1).setValue(j["mainTable"]); //This displays ONLY the first element of the array in mainTable
j["mainTable"].forEach((e) => { //this lists every element
sh.getRange(sh.getLastRow() + 1, 1).setValue(e);
})
sh.getRange(sh.getLastRow() + 1, 1).setValue(j["mainTable"][3]["id"] + ' ' + j["mainTable"][3]["fld1"]); // Display each value in the object of an array element
} catch (err) {
sh.getRange(sh.getLastRow() + 1, 1).setValue(err);
}
}
So in your case,
JSON.parse(e.postData.contents)["Task"] is returning an array (this is how you set up in your bot).
If you want to access individual array elements, you have to use a subscript.
Hi @TeeSee1 , thanks for taking the time to test this out. Let me test your idea.
Something is definitely off at JSON body for the bot.
My use case is, I want to be able to execute different actions for different views in AppSheet. Hence, I have come up with an idea that say, at the Task Assignment section, I am sending Task.Assign command for the particular task, for status update from another view, I'll set Task.StatusUpdate for a particular task.
Is there a better way of handling this?
Thanks
Kaz
I do not know for sure if it is feasible but can't you do those tasks within AppSheet rather than by GAS?
What are you specifically trying to do with these actions?
My GAS code consists of two parts,
I could do 1 in AppSheet, but from what I have read so far, I'll have to handle 2 on GAS side.
Thanks
Kaz
Well, it seems you need to use GAS to perform 2...
@TeeSee1 wrote:I was curious, so I ran a little test of my own.
If you have not figured this out, here are my results.
function doPost(e) {
const sh = ss.getSheetByName('test');
const contents = e.postData.contents;
sh.getRange(sh.getLastRow() + 1, 1).setValue(contents); //This shows the whole JSON object
try {
const j = JSON.parse(contents);
sh.getRange(sh.getLastRow() + 1, 1).setValue(Array.isArray(j["mainTable"])); //TRUE
sh.getRange(sh.getLastRow() + 1, 1).setValue(j["mainTable"]); //This displays ONLY the first element of the array in mainTable
j["mainTable"].forEach((e) => { //this lists every element
sh.getRange(sh.getLastRow() + 1, 1).setValue(e);
})
sh.getRange(sh.getLastRow() + 1, 1).setValue(j["mainTable"][3]["id"] + ' ' + j["mainTable"][3]["fld1"]); // Display each value in the object of an array element
} catch (err) {
sh.getRange(sh.getLastRow() + 1, 1).setValue(err);
}
}
So in your case,
JSON.parse(e.postData.contents)["Task"] is returning an array (this is how you set up in your bot).
If you want to access individual array elements, you have to use a subscript.
I tried your solution with different variations, but I am just not able to get any value for Task. It identifies that Task is an array with length = 1, but everything else after that is simply not accessible.
here is my code, followed by output of each step;
code
var strMessage = "Start execution at " + Utilities.formatDate(new Date(), "GMT+8", "dd/MM/yyyy HH:MM:SS");
setAppendDebug(strMessage) ;
const data = JSON.parse(e.postData.contents) ;
setAppendDebug(cNO_TEXT.concat("type of e.postData.contents -->", typeof(e.postData.contents).toString()));
setAppendDebug(cNO_TEXT.concat("type of JSON.parse(e.postData.contents) -->", typeof(JSON.parse(e.postData.contents)).toString()));
setAppendDebug(cNO_TEXT.concat("e.postData.contents -->", e.postData.contents));
setAppendDebug(cNO_TEXT.concat("JSON.parse(e.postData.contents) -->", JSON.parse(e.postData.contents)));
setAppendDebug(cNO_TEXT.concat("Array.isArray(data[Task]) -->", Array.isArray(data["Task"]))) ;
setAppendDebug(cNO_TEXT.concat("Task array length) -->", data["Task"].length)) ;
data["Task"].forEach((tsk)=>{
//setAppendDebug("tsk type:", getObjType(tsk));
setAppendDebug("tsk --> ", (tsk)) ;
setAppendDebug("JSON.stringify(tsk) --> ", JSON.stringify(tsk)) ;
});
}
Output
Start execution at 08/03/2022 23:03:20
Response of request:TextOutput
type of e.postData.contents -->string
type of JSON.parse(e.postData.contents) -->string
e.postData.contents -->{"Task": [{"Command": "Task.Assign","TaskID": "3"}]}
JSON.parse(e.postData.contents) -->[object Object]
Array.isArray(data[Task]) -->true
Task array length) -->1
tsk -->
JSON.stringify(tsk) -->
I have also tried the following but got error
setAppendDebug("tsk[Command] --> ", tsk[0]["Command"]) ; //error: TypeError: Cannot read property 'Command' of undefined
setAppendDebug("tsk.Command --> ", tsk[0].Command) ; //TypeError: Cannot read property 'Command' of undefined
I was facing the same problem while trying to access the values inside the parsed object that returned from doPost(e).
Maybe you can try to output the following to get the keys and values of the parsed object.
Object.keys(data);
Object.values(data);
In my case, I discovered that my object has length of 1 and its key is '0'. So I just accessed the values as follows:
myData.0.myKey
Hope it helps.
This will make the trick:
function doPost(e) {
const datos = JSON.parse(e.postData.contents);
var key = (datos.Key);
Hope it works for you! it did to me!
That's bad advice. Webhook payloads are transmitted as a string, so you're stringifying a string. If that somehow works for you, then the payload probably wasn't built properly in the first place. Check out these results:
You were right, I've just edited! Thanks
are you try
var data = JSON.parse(e.postData.contents.["Task"]) ;
User | Count |
---|---|
15 | |
12 | |
9 | |
8 | |
4 |