Google Script - Clear Range except one specific value-CodePudding

https://docs.google.com/spreadsheets/d/1v4SrxT5voa94gXxjlEtNm69TNTq3cCbRlHymO_5jjic/edit?usp=sharing

I'm having a hard time writing a simple apps script in Google Sheets that clears all values except a specific value. Sample sheet above. The ideal solution would clear all of row "C" or at minimum clear values "C4:C" or "C4:C100" except cells with the word/value "DELIVER". The script below has been working, the problem is that the script needs constant updating as the rows are not fixed and change based on business needs. So a fixed set of ranges is not ideal. Can anyone please point me in the right direction?

function ClearAztecaFOODForm() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRangeList(['C4:C16', 'C19:C28', 'C31:C42']).activate()
  .clear({contentsOnly: true, skipFilteredRows: true});
};

CodePudding user response：

Description

You could get column C and replace any value that is not DELIVER with blank. In my example script I get all values from column C starting from row 4 and use an Array method forEach() to replace the values. Then I put column C back.

Code.gs

function ClearAztecaFOODForm() {
  try {
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spread.getSheetByName("COPYAztecaFOOD");
    let range = sheet.getRange(4,3,sheet.getLastRow()-3,1)
    let values = range.getValues();
    values.forEach( row => { if( row[0] !== "DELIVER" ) row[0] = "" } );
    range.setValues(values);
  }
  catch(err) {
    SpreadsheetApp.getUi().alert(err);
  }
};

Reference