I’m trying to use google sheets as an IOT datalogger, based on an article in IOTDesignPro
What I’d like to do is have the row reference to wrap around to the second row at the end of x writes. For example, after logging the 6th row, I’d like to have the next row of data to be recorded back at row 2 (creating a circular buffer of sorts).
I’ve tried this, and multiple variation on this, but can get it to work for me.
var sheet_id = "1SrNCSoqTT2wG__RKSHn51kYcknz8GWy7W9_MTecpKYI";
var sheet_name = "Datalog";
function doGet(e){
var ss = SpreadsheetApp.openById(sheet_id);
var sheet = ss.getSheetByName(sheet_name);
var sensor1 = Number(e.parameter.sensor1);
var sensor2 = Number(e.parameter.sensor2);
var date = String(e.parameter.date);
var lastrow = sheet.getRange(sheet.getLastRow(),1).activate
if (lastrow >= 6){
sheet.getRange(1,1).activate() ;
sheet.appendRow([date,sensor1,sensor2]);
}
else{
sheet.appendRow([date,sensor1,sensor2]);
}
}
Not being able to figure out how to use the debugger in google scripts, triggered off of the HTTP get hasn’t helped. I can’t see if the issue is the determination of the last row or something else.
Ok, I realized that although I was wrapping around, the number of rows was never changing; they were still there because I was just reindexing. Below is how I got it to work for anyone running into a similar thing…
var sheet_id = "1SrNCSoqTT2wG__RKSHn51kYcknz8GWy7W9_MTecpKYI";
var sheet_name = "Datalog";
function doGet(e){
var ss = SpreadsheetApp.openById(sheet_id);
var sheet = ss.getSheetByName(sheet_name);
var sensor1 = Number(e.parameter.sensor1);
var sensor2 = Number(e.parameter.sensor2);
var date = String(e.parameter.date);
var lastrow = sheet.getLastRow();
if (lastrow < 6){
sheet.appendRow([date,sensor1,sensor2]);
}
else{
sheet.deleteRows(2,lastrow-1)
sheet.appendRow([9,99,999]);
}
}
appending 9,99,999 is what made me realize what was going on — in the actual version, it is replaced by “date, sensor1, sensor2” just like the other rows…