CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Nicholas
Nicholas

Reputation: 1

Autodelete over multiple sheets in google sheets

I have the following script written to auto delete rows in my sheet when the date in column K are less than the current date. I have recently added some new tabs within my sheet and cannot get the code to delete those new tabs. The date remains in column K on the new tabs. Here is the script. What adjustments do I need to make to make this work? 

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Clear Carry Overs",
    functionName : "Update"
  }];
  spreadsheet.addMenu("Script Center Menu", entries);
};

function Update(){

  var sheet = SpreadsheetApp.getActiveSheet();  RIGHT HERE IS WHERE I CHANGED THE CODE TO NOW READ:
  var sheet = SpreadsheetApp.getActiveSpreadsheet().
  getSheets();
   for(i in sheets)  {
      var sheet = sheets[i];}
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var numColumns = rows.getNumColumns();
  var values = rows.getValues();
  var date = new Date();

  for (var i = 5; i <= numRows - 1; i++) {

    var row = values[i];
    //Logger.log(row[1])
    if(row[1] != ''){
      var sheetDate = new Date(row[10]);
      Sdate=Utilities.formatDate(date, 'CST', 'MM/dd/yyyy')
      SsheetDate=Utilities.formatDate(sheetDate, 'CST', 'MM/dd/yyyy')
      if (Sdate.valueOf() > SsheetDate.valueOf()){
        for(j=2; j < numColumns; j++){
          var valuee = sheet.getRange(i+1, j).clearContent();
        }
        Logger.log("Row " + (i+1) + " was deleted")
      } 
    }
  }
}

new code:

Upvotes: 0

Views: 377

Answers (1)

ScampMichael
ScampMichael

Reputation: 3728

Replace:

var sheet = SpreadsheetApp.getActiveSheet();

with:

var sheets= SpreadsheetApp.getActiveSpreadsheet().getSheets();
 for(i in sheets) {
    var sheet = sheets[i];

then add a closing curly brace } at the bottom.

function Update(){
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
   for(i in sheets) {
     var sheet = sheets[i];
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var numColumns = rows.getNumColumns();
  var values = rows.getValues();
  var date = new Date();

  for (var i = 5; i <= numRows - 1; i++) {

    var row = values[i];
    //Logger.log(row[1])
    if(row[1] != ''){
      var sheetDate = new Date(row[10]);
      Sdate=Utilities.formatDate(date, 'CST', 'MM/dd/yyyy')
      SsheetDate=Utilities.formatDate(sheetDate, 'CST', 'MM/dd/yyyy')
      if (Sdate.valueOf() > SsheetDate.valueOf()){
        for(j=2; j < numColumns; j++){
          var valuee = sheet.getRange(i+1, j).clearContent();
        }
        Logger.log("Row " + (i+1) + " was deleted")
      } 
    }
  }
}
}

Upvotes: 1

Related Questions