Howdy,
I just did this again yesterday. I had no problems but I did have to log in as usual. Considering that I have about half a dozen google sheets with this code it may take a bit when doing it the first time. I remember when I did it for the very first time I had to futz with it. Meaning saving, deploying, hitting the test button over and over again until it just worked. One time I also had to switch the Webhook from Webform to JSON and then back to Webform. I would give it a few hours then try it again.
I know this sounds repetitive but you could try a new sheet and follow the instructions over again. Just to make sure there was not a missed step. New Sheet, New Script, New Webhook. Do it all over again. Once you get it you will be all set.
// 1. Enter sheet name where data is to be written below
var SHEET_NAME = "mesh-heatmap";
// 2. Run > setup
//
// 3. Publish > Deploy as web app
// - enter Project Version name and click 'Save New Version'
// - set security level and enable service (most likely execute as 'me' and access 'anyone, even anonymously)
//
// 4. Copy the 'Current web app URL' and post this in your form/script action
//
// 5. Insert column names on your destination sheet matching the parameter names of the data you are passing in (exactly matching case)
var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service
// If you don't want to expose either GET or POST methods you can comment out the appropriate function
function doGet(e){
return handleResponse(e);
}
function doPost(e){
return handleResponse(e);
}
function handleResponse(e) {
// shortly after my original solution Google announced the LockService[1]
// this prevents concurrent access overwritting data
// [1] http://googleappsdeveloper.blogspot.co.uk/2011/10/concurrency-and-google-apps-script.html
// we want a public lock, one that locks for all invocations
var lock = LockService.getPublicLock();
lock.waitLock(30000); // wait 30 seconds before conceding defeat.
try {
// next set where we write the data - you could write to multiple/alternate destinations
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(SHEET_NAME);
// we'll assume header is in row 1 but you can override with header_row in GET/POST data
var headRow = e.parameter.header_row || 1;
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var nextRow = sheet.getLastRow()+1; // get next row
var row = [];
var json_obj = JSON.parse(e.postData.contents);
// loop through the header columns
for (i in headers){
if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column
row.push(new Date());
} else { // else use header name to get data
row.push(json_obj[headers[i]]);
}
}
sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
// return json success results
return ContentService
.createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
.setMimeType(ContentService.MimeType.JSON);
} catch(e){
// if error return this
return ContentService
.createTextOutput(JSON.stringify({"result":"error", "error": e}))
.setMimeType(ContentService.MimeType.JSON);
} finally { //release lock
lock.releaseLock();
}
}
function setup() {
var doc = SpreadsheetApp.getActiveSpreadsheet();
SCRIPT_PROP.setProperty("key", doc.getId());
}
I had this same error code before. I tried google searches and forums but still could not find anything on that error code. So what I did was delete the sheet completely, start a new sheet. Re-copy the script code from the tutorial and again start over. I ran into a number of problems on the first try. Trust me, It will work!
Cheers,
// 1. Enter sheet name where data is to be written below
var SHEET_NAME = "Sheet1";
// 2. Run > setup
//
// 3. Publish > Deploy as web app
// - enter Project Version name and click 'Save New Version'
// - set security level and enable service (most likely execute as 'me' and access 'anyone, even anonymously)
//
// 4. Copy the 'Current web app URL' and post this in your form/script action
//
// 5. Insert column names on your destination sheet matching the parameter names of the data you are passing in (exactly matching case)
var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service
// If you don't want to expose either GET or POST methods you can comment out the appropriate function
function doGet(e){
return handleResponse(e);
}
function doPost(e){
return handleResponse(e);
}
function handleResponse(e) {
// shortly after my original solution Google announced the LockService[1]
// this prevents concurrent access overwritting data
// [1] http://googleappsdeveloper.blogspot.co.uk/2011/10/concurrency-and-google-apps-script.html
// we want a public lock, one that locks for all invocations
var lock = LockService.getPublicLock();
lock.waitLock(30000); // wait 30 seconds before conceding defeat.
try {
// next set where we write the data - you could write to multiple/alternate destinations
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(SHEET_NAME);
// we'll assume header is in row 1 but you can override with header_row in GET/POST data
var headRow = e.parameter.header_row || 1;
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var nextRow = sheet.getLastRow()+1; // get next row
var row = [];
// loop through the header columns
for (i in headers){
if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column
row.push(new Date());
} else { // else use header name to get data
row.push(e.parameter[headers[i]]);
}
}
// more efficient to set values as [][] array than individually
sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
// return json success results
return ContentService
.createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
.setMimeType(ContentService.MimeType.JSON);
} catch(e){
// if error return this
return ContentService
.createTextOutput(JSON.stringify({"result":"error", "error": e}))
.setMimeType(ContentService.MimeType.JSON);
} finally { //release lock
lock.releaseLock();
}
}
function setup() {
var doc = SpreadsheetApp.getActiveSpreadsheet();
SCRIPT_PROP.setProperty("key", doc.getId());
}
hey, I meant line 44 of the script. You posted the code, however sometimes if I copy paste it in a local editor from something you copy-pasted from Google into this forum, it may or may not be the same line number. That is the reason why I was asking for a screenshot from the script right into the Google Docs interface where is declared.
But as @GasGen mentioned, copy pasting the script again does not hurt...
Thanks and I hope you are closer!
Gustavo.
@GasGen@gusgonnet Thanks both for the encouragement. I used the script provided by @GasGen and left the Content-Type as default and it started to work.
Not quite what I was expecting though - maybe this is the script?
I was expecting just the field value - a number rather than all this {"NumBlobs":"{{{77}}}"}
It’s not the script that is affecting your data its the way you are publishing it or matching it to your sheet. Now with that said I am only guessing as others here have much more knowledge than I do. Here is how my data gets published and a screen shot of the matching webhook to google sheets.
See these examples.
if (Particle.connected()) {
publishStateString[0] = '\0'; // make sure the string is reset
if (millis() - lastpub2 > 600000) { // time for a full report?
lastpub2 = lastpub = millis(); // reset BOTH timers as the full report also features the short report data
snprintf(publishStateString // use this for losant format, removed ADC
, sizeof(publishStateString)
, "{ \"cf\":\"%.1f\", \"v\":\"%.f\", \"c\":\"%.2f\"}"
,flow, soc, fuel.getVCell() );
}
else if (millis() - lastpub > 60000) { // time for an short report
lastpub = millis();
snprintf(publishStateString
, sizeof(publishStateString)
, "{ \"cf\":\"%.1f\"}"
, flow );
}
if (publishStateString[0]) // if the string was populated send it
Particle.publish("POU", publishStateString, 60, PRIVATE, NO_ACK); //NOTE THIS IS FOR WEBHOOK POU
Now the screenshot of the matching google sheets webhook.
Super - works exactly how I was hoping it would. Thanks for sharing your script and settings - main change was using the default form fields and extra rows for own data and also the format of the published message.
Just need to solve a google sheets challenge of turning a base64 encoded binary array back into a binary array. In Excel it requires a VBA subroutine so possible it can done in the same POST function script?
And this is just the data collection part to be able to perform machine learning!
Awesome. I knew you would get it. I actually can’t take any credit for the help. I would still be lost if it wasn’t for the help of @ScruffR, @RWB, @gusgonnet, and @peekay123. They saved me from myself, many times. @ScruffR actually wrote that publishing structure to help me get things going. Side note and major bonus, if you want to try ubidots the publish is already formatted to be read by their side. All I had to do was follow the short instructions, include the library, and create a ubidots webhook with the same name as the google one. Now I have data going to google sheets for backup and easy download, along with some fancy widgets over on Ubidots. I think I read there is a script for Ubidots to Google Sheets, but I like having redundant data backups and or collection.
Classification and Pattern Recognition - I have a number of AMG8833 thermopile array sensors. They are there to detect human presence in a room space. I am trying to work out whether to do more analysis on the device or in the cloud - I am sending a Base64 encoded binary image to help with the classification plus analysis of the image with blob detection (essentially mass and location) plus other features - not sure yet what is significant. Also interested in @bsatrom project to port TensorFlow Lite Micro to Particle.
HTTP 409 would indiacte a request conflicting with the current state of the server.
What is your webhook doing?
Is the respective resource (e.g. file) also used by others or otherwise?
Hi @ScruffR the webhook is sending json data to the google sheet and is being received by the script noted in this thread. I commented out the wait lock for the time being and I’m no longer getting the 409 errors, but now I’m getting a clock symbol next to the event logs under that integration. Do you know what that might mean? Here is what it looks like.
better get going!
