I have tried to follow the steps given in this Particle documentation for Google Sheet integration
I found that the process at Google has changed substantially from the Particle documentation. As a start, Script Editor does not appear under the Tools menu.
I found many other problems with the documented way of doing this. Someone could try following the instructions and I think the issues will be obvious.
As part of the community integrations, it's not updated as frequently as regular documentation would be, but the script editor is available in the Extensions menu as Apps Script.
There is some very important info missing, from what I know now. Since the Apps Script is not associated with any particular Google Sheet, the script now has to specifically reference the URL of the target sheet. Also, app deployment is quite different. There are a couple of key choices to make (that are not obvious to the noob) and the deployment of new app code also requires a few not obvious choices.
It's a great integration to use once you figure out how to do it. The current documentation is just a teaser. If that page was community editable then I'd make changes to it.
function test() {
var e = {};
e.parameter = {};
e.parameter.event = 'sheetTest1';
e.parameter.data = 'Any Ki{nd & of te,st \r\n data "can ] go: here"';
e.parameter.coreid = '1f0030001647ffffffffffff';
e.parameter.published_at = new Date().toISOString();
doPost(e);
}
function doGet(e) {
var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1SDjUns8Wz5dlvmfxxxxxxxxxxxxxxxxxxxx/edit?gid=0#gid=0");
var sheet = ss.getSheetByName("NewData");
addLogRow(e,sheet);
}
function doPost(e) {
var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1SDjUns8Wz5dlvmfxxxxxxxxxxxxxxxxxxxx/edit?gid=0#gid=0");
var sheet = ss.getSheetByName("NewData");
addLogRow(e, sheet);
}
function addLogRow(e, sheet) {
// time from the Google cloud - useful for debugging
var now = new Date();
var timePST = Utilities.formatDate(now, "America/Los_Angeles", "yyyy-MM-dd' 'HH:mm:ss");
try{
// unpack parameters from Particle cloud integration
var ev = e.parameter.event;
var coreid = e.parameter.coreid;
var timeParticle = e.parameter.published_at;
var deviceData = e.parameter.data;
// add a row to the sheet
sheet.appendRow([timePST, coreid, timeParticle, deviceData] );
} catch(error) {
sheet.appendRow(["Error in GApp: " + error, "PostData: " + JSON.stringify(e.postData)]);
}
// keep the number of rows within bounds by deleting the oldest entries
cleanUpSheet(sheet);
return 0;
}
function cleanUpSheet(sheet) {
const MAX_ROWS = 3000; // delete some rows if sheet has more than this
const ROWS_TO_DELETE = 500; // number of oldest rows to delete in a cleanup operation
var lastRow = sheet.getLastRow();
if(lastRow >= MAX_ROWS) {
sheet.deleteRows(2, ROWS_TO_DELETE); // keep header row
}
}
Copy the URL of the sheet from your browser and paste it into the script in the places where the URL is
Now click Deploy; you get a pop up. Choose web app. Let it run as your user. Make it available to anyone with the URL.
Then the pop up will provide a URL copy that.
Paste the URL into your Particle integration (web hook)
Click Test on the integration and add some random data. See that it appears in your google sheet. Ta-da.
When you make changes to your Google app script, save it. Then click Deploy but select Manage Integrations. Click the pencil icon and in the top drop down box pick New Version. This will preserve your Google App URL.
The text was wrong as the console changed. It's now:
If you left the Device ID field in the Test Parameters window blank, column A will be api.
Column B is the date and time the event was received by the cloud.
Columns C and to the right have the data that you entered in the event data box (if it was valid JSON).
Great. Yesterday I implemented the mapping of coreID to spreadsheet idea. Works great and simplifies our workflow (for our team of two!). Thanks very much for this.