CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Yvan L
Yvan L

Reputation: 333

Google Sheets + Apps Script: How to delete a sheet by name based on the value of a cell

Hi I'm using this code to delete the row if the value in column "A" is older then 30 day and it's working good. What I'm looking for is to also be able to get the value from column "B" and delete the sheet with the matching name before deleting the row.

Thanks

function deleteOldRow() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Submited List");
  var datarange = sheet.getDataRange();
  var lastrow = datarange.getLastRow();
  var values = datarange.getValues();
  var currentDate = new Date();//today
  var monthOld = Date.now() - 30*24*3600*1000; 
  for (i=lastrow;i>=2;i--) {
var tempDate = values[i-1][0];
values[0] 
if ((tempDate!=NaN) && (tempDate <= (monthOld)))
{
  Logger.log(i)
  sheet.deleteRow(i);
   }
  }

}

enter image description here

Upvotes: 1

Views: 1335

Answers (1)

Tanaike
Tanaike

Reputation: 201388

I believe your goal as follows.

  • You want to delete the sheet by retrieving the sheet name from the column "B" when the row is deleted.

If the value of column "B" is the sheet name, how about adding the following script to your script?

Modified script:

if ((tempDate!=NaN) && (tempDate <= (monthOld))) {
  sheet.deleteRow(i);
  var deleteSheet = ss.getSheetByName(values[i-1][1]); // Added. Or ss.getSheetByName(values[i-1][1].trim());
  if (deleteSheet) ss.deleteSheet(deleteSheet); // Added
}
  • In this modification, when the sheet of sheet name values[i-1][1] is existing, the sheet is deleted.

Reference:

Upvotes: 1

Related Questions