Via Particle.publish statements and a webhook of type script.google.com, my application sends two values to a google sheet. The resulting entries in the sheet also automatically include a required date and time.
However, I want to create a Google script that automatically deletes all entries in the sheet older than 3 days. I created a script (see below) but it does not work because the date and time stamp is stored as a single plain text string (see screenshot).
Can anyone suggest how my webhook can be modified to send actual (and separate) date and time objects rather than plain text?
function deleteOldRows() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheetName = "Sheet1"; // Change to your sheet name
const sheet = ss.getSheetByName(sheetName);
if (!sheet) {
Logger.log("Sheet not found: " + sheetName);
return;
}
const dateColumnIndex = 1; // Column A is 1, B is 2, etc. (adjust to your date column)
const daysToKeep = 3; // Number of days to keep the data (adjust as needed)
const today = new Date();
const cutoffDate = new Date(today.setDate(today.getDate() - daysToKeep));
const lastRow = sheet.getLastRow();
if (lastRow <= 1) { // Check if there are any data rows (assuming header in row 1)
Logger.log("No data rows to process.");
return;
}
for (let i = lastRow; i > 1; i--) { // Iterate from bottom up to avoid index issues
const cellDate = sheet.getRange(i, dateColumnIndex).getValue();
if (cellDate instanceof Date && cellDate < cutoffDate) {
sheet.deleteRow(i);
}
}
}
