CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
jivers
jivers

Reputation: 1000

Google Sheets function runs but does not affect sheet content

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

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);
  };
  
}

Upvotes: 0

Views: 98

Answers (1)

Tanaike
Tanaike

Reputation: 201338

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");
}

Upvotes: 2

Related Questions