CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Roman Cihlar
Roman Cihlar

Reputation: 13

(Google Sheets script) Clear Specific cells

I have been tasked by my company to make an update to our sheet. The script needs to clear certain cells in a row based on the value of another cell. I have managed to find a script that deletes the whole required row, however that causes an issue with the rest of the document.

   function deleteRows() {
  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[14] == '' && row[6] == 'PREBOOKED') { // This searches all cells in columns A (change to row[1] for columns B and so on) and deletes row if cell is empty or has value 'delete'.
      sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
      rowsDeleted++;
    }
  }
};

Is it possible to modify this to instead of deleting the row it just clears the content of the cells D - P?

Upvotes: 1

Views: 1136

Answers (2)

Roman Cihlar
Roman Cihlar

Reputation: 13

i was able to do what i need with the following:

       function deleteRows() {
  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[14] == '' && row[6] == 'PREBOOKED') { // This searches all cells in columns A (change to row[1] for columns B and so on) and deletes row if cell is empty or has value 'delete'.
      sheet.getRange((parseInt(i)+1), 6).clearContent();
      sheet.getRange((parseInt(i)+1), 7).clearContent();
      sheet.getRange((parseInt(i)+1), 10).clearContent();
      sheet.getRange((parseInt(i)+1), 11).clearContent();
      sheet.getRange((parseInt(i)+1), 12).clearContent();
      rowsDeleted++;
    }
  }
};

thanks for your ideas folks!

Upvotes: 0

ra89fi
ra89fi

Reputation: 1245

You can use below code with modifications if necessary.

function deleteRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = sheet.getDataRange().getValues();

  values.forEach(function(v, i) {
    // get row index
    var row = i+1;
    // look for required values in cells
    if (v[14] == '' && v[6] == 'PREBOOKED') {
      // might have to modify this line based on Sheets API
      sheet.getRange('D'+row+':P'+row).clearContent();
    }
  });
}

Upvotes: 1

Related Questions