Hi, I'm not able to receive a webhook response on my boron from Google Sheets. My payloads are being received and consumed by Google, no issue. This is the relevant code from my app script to send the response:
// Step 7: Return a response based on the success of the operation
if (success) {
return ContentService.createTextOutput(JSON.stringify({result: 'success'})).setMimeType(ContentService.MimeType.JSON);
} else {
return ContentService.createTextOutput(JSON.stringify({result: 'error', message: 'Failed to append data'})).setMimeType(ContentService.MimeType.JSON);
}
On the integration, my response template looks like this:
{
"{{{response.result}}}"
}
in the console log, here is snip of what an event response looks like. I see the expected response:
{"result":"success"}
Here's the complete response:
Here is the code I'm running on my boron:
void webhookHandler(const char *event, const char *data) {
Serial.println("Webhook response received: ");
Serial.println(data);
if (strstr(data, "\"result\":\"success\"") != NULL) {
uploadConfirmed = true;
} else {
uploadConfirmed = false;
}
}
my Serial.println() statements are producing this from the serial port:
Webhook response received:
{
""
}
What silly mistake am I making? I just want a confirmation that the Sheet processed my payload correctly? Without a true response, I can't be certain my payload was received and my boron won't delete the payload from FRAM. Any suggestions / recommendations are very much appreciated. I'm still learning! Thanks
The device sees an empty response because Google is returning a 302 moved temporarily response to the request. I think I've seen that before.
The problem is that it also inserts stuff before the response that's not JSON, which prevents it from being parsed by the webhook response handler. The JSON response should begin right after the blank line after the Transfer-Encoding line.
If the response is small enough you could send the whole response in your hook-response which would allow you to do the parsing on the device. This will use more data, but still only one data operation. It looks small enough in the screenshot, but I'm not positive it is.
Thanks Rick. That makes sense.
What do I put in the Response Template to allow the entire hook response to flow to the device?
Also, you say "It looks small enough in the screenshot" which suggests the console shows what the hook response is. Where can I see that?
Good news. I am now getting the hook response on my device and I'm able to parse the response. But I think there is room for improvement. This is the code I'm using to receive the hook response. I worry it may be blocking but I need a timer to ensure the device doesn't lock up. I've also noticed a lot of variability in the time to receive the hook response. It varies from 1-18 seconds. That is a wide range. Is that normal? Or, is my code the reason? Thanks
// Wait for webhook response confirmation with timeout
unsigned long startTime = millis();
const unsigned long timeout = 20000; // 20 seconds timeout
while (!uploadConfirmed && (millis() - startTime < timeout)) {
delay(5); // Check every 5ms
}
if (!uploadConfirmed) {
Serial.println("Webhook response not received within timeout period.");
return false;
}
uploadConfirmed = false; // Reset the flag for the next upload
// Disconnect from the Particle Cloud to save power if requested
if (disconnectAfterSend) {
disconnectFromParticleCloud();
}
return true;
Update: I'm not sure if it's my code or my app script or something else, but the end result which is in google sheets, is not good. Only about 85% of my data is making it into the sheet and about 20% of the data is duplicated. Looking at the console, my payloads are being properly received and the json is correct. the data in each payload is correct. everything on the device and particle side looks like it is working correctly. and I don't see any evidence that my webhooks are being sent multiple times. A few webhooks did produce an ESOCKETTIMEOUT error. Is that being caused by the google side? Could this cause the webhook to be resent multiple times?
I have tried various experiments: sending payloads every 5 seconds, sending payloads then waiting for a hook response before sending the next, etc. but the data is just not getting into the sheet.
I am beginning to think the issue might be with google and more specifically with my app script. Yes, my app script.
Next steps: I am seeking help in the Google community to see if my app script is or part of the issue.
Yes, ESOCKETTIMEOUT timeout occurs when the server (Google apps script) does not return a success or failure code within the 20 second of accepting the connection. Because of the way the scripts work, if your script takes more than 20 seconds to run to completion, this error will occur.
This can result in duplicates if the script continues to run to completion after the webhook times out, because the Particle webhook server will retry, which will result in a duplicate.
Once you start receiving errors, the connection may end up being throttled to avoid overloading the server. Watch for sleep in addition to error in the webhook logs to see if this is occurring. If you fix the underlying cause for the error, the sleep and error should stop occurring. Events that caused sleep are never retried and are discarded.
That makes sense. In testing, I sent 10 payloads spaced out by 5 second delays. Three of the payloads produced an ESOCKETTIMEOUT error on the particle console. On the app script side, in Executions, three of the executions took greater than 20 seconds to complete.
What do you think might be the underlying cause? my app script? In any case, i am investigating my app script with the google community trying to find the underlying cause.
Thanks
