Modify Code From Delete to Clear Contents - Google Sheets Apps Script

user_11218797 0
Viewed 68

I have the below code that I would like to change from deleting to clear Contents

function DeleteAnytrue() {

var sheet = SpreadsheetApp.getActiveSheet();
var rows = sheet.getDataRange();
var numRows = rows.getNumRows();
var values = rows.getValues();

var rowsDeleted = 0;
for (var i = 0; i <= numRows - 1; i++) {
var row = values[i];
if (row[2] == true) {

// tried adding this line below but no luck
sheet.clearContents((parseInt(i)+1) - rowsDeleted);

//sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
rowsDeleted++;
}
}
};
google-apps-script google-sheets
3 Answers

Try this:

function DeleteAnytrue() {
  var sh = SpreadsheetApp.getActiveSheet();
  var vs = sh.getDataRange().getValues();
  vs.forEach((r,i)=> { 
    if (r[2] == true) {
      sh.getRange(i+1,1,1,sh.getLastColumn()).clearContent()
    }
  });
};
user_72150910

I believe your goal as follows.

  • You want to clear the rows when the column "C" is true using Google Apps Script.

Modification points:

  • About sheet.clearContents((parseInt(i)+1) - rowsDeleted);, unfortunately, the method of clearContents() of Class Sheet has no arguments and in this case, all cell values are cleared.
  • In your situation, I would like to propose to use the method of clearContent() of Class RangeList. When this method is used, it can be used outside of the loop.
  • In this modification, I would like to propose the following flow.
    1. Search rows which have true in the column "C" using TextFinder and create an array including A1Notations.
    2. Clear the cell values using clearContent() of Class RangeList.

When above points are reflected to your script, it becomes as follows.

Modified script:

function DeleteAnytrue() {
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // 1. Search rows which have `true` in the column "C" using TextFinder and create an array including A1Notations.
  var a1Notations = sheet
    .getRange("C1:C" + sheet.getLastRow())
    .createTextFinder("TRUE")
    .matchEntireCell(true)
    .findAll()
    .map(r => {
      var row = r.getRow();
      return `A${row}:${row}`
    });
  
  // 2. Clear the cell values using `clearContent()` of Class RangeList.
  if (a1Notations.length > 0) sheet.getRangeList(a1Notations).clearContent();
}

References:

user_71086530
user_152458890
Related