Google Sheets function runs but does not affect sheet content

Question

I'm working on a Google Sheets script to perform the following:

iterate through a specified sheet, finding rows that do not contain a specified value within a specified column
store these rows as a range in a variable
remove all other rows
replace the sheet content with the stored rows

The script runs without error, but does not affect sheet content. I'm not sure what the issue is - any feedback is appreciated.

Here's the script:

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Clear OI')
      .addItem('Clear OI', 'removeZeroOI')
      .addToUi();
}

function removeZeroOI() {

  // Deleting Rows Based on Cell Values
 
  //GLOBALS
  
  var SS = SpreadsheetApp.openById("mySheetID");
  var SHEET = SS.getSheetByName("mySheetName");
var RANGE = SHEET.getDataRange();
  
  
  var DELETE_VAL = 0;
  var COL_TO_SEARCH = 13; //column N
  
  function main() {
    
    var startTime = new Date().getTime();
    
    var deleteSelectedRows = removeFilterSetVals();
    
    var runTime = (new Date().getTime() - startTime)/1000;
    Logger.log("Runtime is: "+runTime + " seconds");
  };
  
  function removeFilterSetVals(){
    var rangeVals = RANGE.getValues();
    
    var filteredRange = rangeVals.filter(function(val){
      
      return val[COL_TO_SEARCH] != DELETE_VAL ;
    });
    
    RANGE.clearContent();
    
    var newRange = SHEET.getRange(1,1,filteredRange.length, filteredRange[0].length);
    newRange.setValues(filteredRange);
  };
  
}

Tanaike Tanaike · Accepted Answer · 2021-06-03T23:30:00

If you are actually using your showing script, I think that in your script, when the function of removeZeroOI() is run, main() and removeFilterSetVals() are not run. By this, the script doesn't work while no error occurs.

When you want to run main() and removeFilterSetVals(), how about the following modifications?

Modified script:

function removeZeroOI() {
  var SS = SpreadsheetApp.openById("mySheetID");
  var SHEET = SS.getSheetByName("mySheetName");
  var RANGE = SHEET.getDataRange();
  var DELETE_VAL = 0;
  var COL_TO_SEARCH = 13; //column N

  main(); // Added

  function main() {
    var startTime = new Date().getTime();
    var deleteSelectedRows = removeFilterSetVals();
    var runTime = (new Date().getTime() - startTime) / 1000;
    Logger.log("Runtime is: " + runTime + " seconds");
  };

  function removeFilterSetVals() {
    var rangeVals = RANGE.getValues();
    var filteredRange = rangeVals.filter(function(val) {
      return val[COL_TO_SEARCH] != DELETE_VAL;
    });
    RANGE.clearContent();
    var newRange = SHEET.getRange(1, 1, filteredRange.length, filteredRange[0]
      .length);
    newRange.setValues(filteredRange);
  };
}

And,

function removeZeroOI() {
  var SS = SpreadsheetApp.openById("mySheetID");
  var SHEET = SS.getSheetByName("mySheetName");
  var RANGE = SHEET.getDataRange();
  var DELETE_VAL = 0;
  var COL_TO_SEARCH = 13; //column N

  var startTime = new Date().getTime();

  // Script of "removeFilterSetVals()" is put here.
  var rangeVals = RANGE.getValues();
  var filteredRange = rangeVals.filter(function(val) {
    return val[COL_TO_SEARCH] != DELETE_VAL;
  });
  RANGE.clearContent();
  var newRange = SHEET.getRange(1, 1, filteredRange.length, filteredRange[0]
    .length);
  newRange.setValues(filteredRange);

  var runTime = (new Date().getTime() - startTime) / 1000;
  Logger.log("Runtime is: " + runTime + " seconds");
}

Google Sheets function runs but does not affect sheet content

1 Answers

Modified script: