BUG: Particle to Google Sheets data transfer inaccurate

[agard111]

Procedure: Start a fin session. As fin uploads data, save it from particle console. Run a diff between the google sheets version of the data and the particle version. For some of the packets, a double opening curly brace {{ is omitted when the data is transferred to google sheets. This often results in data that cannot be decoded.

[ntlhui]

Modified FW to upload only {{

Corresponding Particle Console Event:

{
  "name": "Sfin-5d0036001750483553353920-test_msg",
  "data": "{{",
  "published_at": "2022-07-20T06:23:11.363Z",
  "coreid": "5d0036001750483553353920",
  "userid": "5fadb3c935890d26c0b9a2e7",
  "version": 0,
  "public": false,
  "productID": 8977
}

This is sent to https://script.google.com/macros/s/AKfycbwbRirErqHdQbuwnI_DtsxB8k3X0oVS1DGgswKzSeu-HhJuUMWOfaa5gDYltbR--Y6J9g/exec, which is the API for https://docs.google.com/spreadsheets/d/1NRPhBXdc-hcCz2nUmhhywnGOdoVQH0xdsAZc0EjsJJQ/edit#gid=0

AppSheets responded with this:

HTTP/1.1 302 Moved Temporarily
Content-Type: text/html; charset=UTF-8
Access-Control-Allow-Origin: *
Cache-Control: no-cache, no-store, max-age=0, must-revalidate
Pragma: no-cache
Expires: Mon, 01 Jan 1990 00:00:00 GMT
Date: Wed, 20 Jul 2022 06:23:13 GMT
Location: https://script.googleusercontent.com/macros/echo?user_content_key=tt436q-fVPd82_H4u2aXEy9kCbZtdScJVa5QkYYwQ5ObdSUE-dFqd26qG7wlLKAeqZRq7bjukgaKPOITuBPtFzRvBdp6AF3rm5_BxDlH2jW0nuo2oDemN9CCS2h10ox_1xSncGQajx_ryfhECjZEnMW5KRpnclU_1J4UKZIoSAquu0Z6O3y4Mo_QSM7nZTb0fNz4iM2LvklNf6P8Z0wEuI8h39H0GbmsBe5-hOqPw8Ab3dlq0gXm4dz9Jw9Md8uu&lib=MOvMODCsoIaoyKluNvoQ-q3fIv8Qt4ZAQ
X-Content-Type-Options: nosniff
X-Frame-Options: SAMEORIGIN
Content-Security-Policy: frame-ancestors 'self'
X-XSS-Protection: 1; mode=block
Server: GSE
Alt-Svc: h3=":443"; ma=2592000,h3-29=":443"; ma=2592000,h3-Q050=":443"; ma=2592000,h3-Q046=":443"; ma=2592000,h3-Q043=":443"; ma=2592000,quic=":443"; ma=2592000; v="46,43"
Accept-Ranges: none
Vary: Accept-Encoding
Transfer-Encoding: chunked

GQajx_ryfhECjZEnMW5KRpnclU_1J4UKZIoSAquu0Z6O3y4Mo_QSM7nZTb0fNz4iM2LvklNf6P8Z0wEuI8h39H0GbmsBe5-hOqPw8Ab3dlq0gXm4dz9Jw9Md8uu&amp;lib=MOvMODCsoIaoyKluNvoQ-q3fIv8Qt4ZAQ">here</A>.
</BODY>
</HTML>

0

{"result":"success","row":1425,"content":{"postData":{"contents":"event=Sfin-5d0036001750483553353920-test_msg&data=&published_at=2022-07-20T06%3A23%3A11.363Z&coreid=5d0036001750483553353920&userid=5fadb3c935890d26c0b9a2e7&fw_version=0&public=false","length":182,"name":"postData","type":"application/x-www-form-urlencoded"},"queryString":"","contentLength":182,"parameter":{"data":"","public":"false","published_at":"2022-07-20T06:23:11.363Z","coreid":"5d0036001750483553353920","fw_version":"0","event":"Sfin-5d0036001750483553353920-test_msg","userid":"5fadb3c935890d26c0b9a2e7"},"parameters":{"coreid":["5d0036001750483553353920"],"published_at":["2022-07-20T06:23:11.363Z"],"event":["Sfin-5d0036001750483553353920-test_msg"],"userid":["5fadb3c935890d26c0b9a2e7"],"fw_version":["0"],"public":["false"],"dat (truncated)

Note that in the response, "data" is empty.

Particle uses mustache templates to do variable substitution in the webhooks. {{ is a starting delimiter for mustache templates. This is a Particle issue.

[ntlhui]

@SUPScientist this is probably of interest to you.

[ayang923]

@ntlhui Is it possible we replace the { character with some other ascii character in the base85.c file?

[ntlhui]

No, if we stick with base85 encoding, we should not deviate from the base85 specification.

One option might be to instead use a base64 encoding. We would probably want to use RFC4648.5.

Reference https://community.particle.io/t/webhook-data-corruption/62906

[SUPScientist]

Is there any reason to believe this issue also affects the Axiom servers? Or is it possible that just Google Sheets is losing the double opening curly braces? Changing encoding sounds like a substantial amount of effort (and would also require, of course, changes in decoding) and it would be best to avoid if possible.

[ntlhui]

This almost certainly also affects the Axiom servers - this is processing that is done on the Particle servers prior to passing data on to Axiom/Google.

[ntlhui]

Escape characters do not exist for Particle.

Solution will require encoding changes.

[ntlhui]

Switching to base64. Moving this back to smartfin-fw2 for tracking, and creating a parallel issue in smartfin-tools.

[ntlhui]

Developing in cloud_upload. Basic upload is working with proper decoding in base64 branch of smartfin-tools. However, the Particle event name is incorrect.