JavaScript Google app script spam control

[mountaineer03]

I am trying to do a honeypot script to delete submissions in google sheets. I don't know javascript that well but attempting this as a solution.

my header is as follows: Date, Name, Email, hidden field and message.
my sheet name is: leads

function deleteSpamRows() {
  // Get the sheet named "leads"
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("leads");
 
  // Get all data from the sheet
  var data = sheet.getDataRange().getValues();
 
  // Array to store row numbers to delete
  var rowsToDelete = [];
 
  // Start from i = 1 to skip the header row (row 1)
  for (var i = 1; i < data.length; i++) {
    var row = data[i];
    var honeypotValue = row[3]; // Column D (index 3) is the honeypot field
    if (honeypotValue != "") {
      rowsToDelete.push(i + 1); // Add the sheet row number (1-indexed)
    }
  }
 
  // Delete rows from bottom to top to avoid shifting issues
  for (var i = rowsToDelete.length - 1; i >= 0; i--) {
    sheet.deleteRow(rowsToDelete[i]);
  }
}

One last thing I don't see anything like GO LIVE or RUN script. Does it just auto save like all of the other google products.
So far it isn't working.

thanks

 

[EkBass]

This is far from my expertise, but few hints I can give.

1. You do not need to store lines to delete in array first and then loop it again. You can just filter the unwanted rows.
2. Doing 2 loops is not required.

Here is my suggestion. It may have some bugs, as said, Im not too familiar with google sheets. But you get the idea atleast if it does not work.

function deleteSpamRows() {
 
    // Your sheet
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("leads");

    // Data from sheet to "data"
    var data = sheet.getDataRange().getValues();
 
    // We can use filter method, no need to loop.
    var filteredData = data.filter(function(row, index) {
        return index === 0 || row[3] === ""; // returns only data, where honeypot is empty.
    });

    sheet.clearContents();  // clears the whole sheet
   
    // Now we write filtered data to sheet
    sheet.getRange(1, 1, filteredData.length, filteredData[0].length).setValues(filteredData);
}

Edit: If your sheet gets huge, like tens of megabytes the it is better to read data in pieces. Even better, share it in different sheets when receiving data. Like every 5K responses a new sheet so the size of sheet does not get stupid big.

 

[simong1993]

From my experiance cant you turn on Google login so people cant do a submission without loggin in first?