Is it possible to delete the content of specific rows by a macro in Google Sheets?

Question

I have two spreadsheets, one is called "Input data" and the other is called "Assignment". If a value/content(in my case a name) in column C of "Assignment" does not occur in column C of "Input data", the specific row gets deleted by this formula that works completely fine:

function checkName() {


  var s1 = SpreadsheetApp.getActive().getSheetByName("Input Data");
  var col1 = s1.getRange("C3:C").getValues();

  var s2 = SpreadsheetApp.getActive().getSheetByName("Assignment");
  var col2 = s2.getRange("C4:C").getValues();

  for (var i=col2.length-1; i>=0; i--) {
    var found = false;
    for (var j=0; j<col1.length; j++) {
       if (col1[j][0] == col2[i][0]) {
         found = true;
         break;
       }
    }

    if (!found) {
      s2.deleteRow(i+4);
    }
  }
}

Is it possible to delete only the contents of these specific rows or to delete only the content in specific columns of these specific rows? Like only column A-F of row 30 for example (if it fits to the criteria that I have described above). I tried s2.deleteContents(i+4) but that does not really work... Could you help me please?

Answer 1

• When the values of "C4:C" in the sheet of "Assignment" are not found in the values of "C3:C" in the sheet of "Input Data", you want to clear the content of the row of column "A" to "F" for the sheet of "Assignment".
• You want to achieve this using Google Apps Script.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Pattern 1:

In this pattern, your script was modified. Please modify your script as follows.

Modified script

From:

s2.deleteRow(i+4);

To:

s2.getRange(i+4, 1, 1, 6).clearContent();

• In this case, the content of the column "A" to "F" the row is cleared.

Pattern 2:

In this pattern, at first, the ranges for clearing are retrieved. And the range list is cleared by clearContent(). The process cost might be able to be reduced a little.

Modified script

function checkName() {
  var s1 = SpreadsheetApp.getActive().getSheetByName("Input Data");
  var col1 = s1.getRange("C3:C" + s1.getLastRow()).getValues().map(function(e) {return e[0]});
  var s2 = SpreadsheetApp.getActive().getSheetByName("Assignment");
  var col2Range = s2.getRange("A4:F" + s2.getLastRow());
  var rangeList = col2Range.getValues().reduce(function(ar, e, i) {
    if (col1.indexOf(e[2]) == -1) ar.push("A" + (i + 4) + ":F" + (i + 4));
    return ar;
  }, []);
  // if (rangeList.length > 0) rangeList.push("A30:F30");
  s2.getRangeList(rangeList).clearContent();
}

• About Like only column A-F of row 30 for example, if you want to also clear the content of "A30:F30", please remove // of the line of // if (rangeList.length > 0) rangeList.push("A30:F30");. By this, when above condition occurs, the content of "A30:F30" is also cleared. But about this, I'm not sure whether I could correctly understand about your goal. I apologize for this.

References:

• getRange(row, column, numRows, numColumns)
• clearContent()
• Class RangeList

If I misunderstood your question and this was not the result you want, I apologize.

Added:

• You want to use the modified script of pattern 1.
• You want to use Michael, Ballack and Michael, BALLACK as the same value.

From your replying, I could understand about that. For above case, how about the following modification? In this case, using toUpperCase(), both values from Input Data and Assignment are compared.

Modified script:

function checkName() {
  var s1 = SpreadsheetApp.getActive().getSheetByName("Input Data");
  var col1 = s1.getRange("C3:C").getValues();
  var s2 = SpreadsheetApp.getActive().getSheetByName("Assignment");
  var col2 = s2.getRange("C4:C").getValues();
  var found;
  for (var i=col2.length-1; i>=0; i--) {
    found = false;
    for (var j=0; j<col1.length; j++) {
       if (col1[j][0].toUpperCase() == col2[i][0].toUpperCase()) {  // Modified
         found = true;
         break;
       }
    }
    if (!found) {
      s2.getRange(i+4, 1, 1, 6).clearContent();  // Modified
    }
  }
}