Skip to content
Advertisement

How to make automatically delete rows with multiple sheet in google script

How to make automatically delete rows with multiple sheet in google script based on trigger, I’ve used this script and it worked, but I want this script to be able to use two or more tab sheets at once while the current script only uses one tab sheet. how to use more than one tab sheet in getSheetByName? Can anyone help me? because every time I ask here no one responds, this is my third question, that the previous two no one helped answer. is there something wrong i don’t know.

function acraCleanup() { 
  var SPREADSHEET_KEY = "AABVGGHJGFGDFG4GFD65GHDF56HGFHG53";

// Replace with your spreadsheet Key,

//you can find this by trying to share the Spread sheet

// and getting the key between the "=" and the "&" 

  var SHEET_NAME = "Sheet 1"; //Sheet 1 unless you changed the sheet name 
  var rowsToKeep = 1000; //Will keep bottom 1000 Rows 
  var sheet  = SpreadsheetApp.openById(SPREADSHEET_KEY).getSheetByName(SHEET_NAME); 
  var rows = sheet.getLastRow(); 
  var numToDelete = rows – rowsToKeep  -1; 
  if (numToDelete > 0) sheet.deleteRows(2, numToDelete); 
}

Answer

Try

function acraCleanup() { 
  var SPREADSHEET_KEY = "AABVGGHJGFGDFG4GFD65GHDF56HGFHG53";
  var rowsToKeep = 1000; //Will keep bottom 1000 Rows 
  const list = ['SheetX','SheetY'];
  var ss  = SpreadsheetApp.openById(SPREADSHEET_KEY)
  ss.getSheets().filter(sh => list.indexOf(sh.getName()) != -1).forEach(function (sheet){
    var rows = sheet.getLastRow(); 
    var numToDelete = rows - rowsToKeep  - 1; 
    if (numToDelete > 0) sheet.deleteRows(2, numToDelete);
  })
}
Advertisement