Optimizing Delete Rows Script

Question

I am fairly new to script. I have the following script which duplicates the active spreadsheet to another tab and then deletes the columns out of a varying range. Essentially I want it to delete from column 12 up to the 3rd to the last column in the spreadsheet. This portion is working correctly, but it takes almost 20 seconds to run. Is there any way I could optimize this for faster processing speeds?

The second part of the script searches the rows of the spreadsheet, and if Column A is empty or has an "x" in it, deletes that row. This portion works correct but I would like it to hide the row rather than delete it and can't seem to figure it out.

function NewWeek() {

  //Duplicate the sheet

  SpreadsheetApp.getActiveSpreadsheet().duplicateActiveSheet();

  //Delete Future Weeks

  var sheet = SpreadsheetApp.getActiveSheet();
  var columns = sheet.getDataRange();
  var numColumns = columns.getNumColumns();
  var columnsDeleted = 0;
  for (var i = 14; i <= numColumns - 3; i++) {
    sheet.deleteColumn((parseInt(i) + 1) - columnsDeleted);
    columnsDeleted++;
  }

  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();
  var rowsDeleted = 0;
  for (var i = 4; i <= numRows - 1; i++) {
    var row = values[i];
    if (row[0] == 0 || row[0] == 'x') {
      sheet.deleteRow((parseInt(i) + 1) - rowsDeleted);
      rowsDeleted++;
    }
  }
}

Answer 1

whenever you have a loop doing a delete, the first place to optimize is to see if the loop can be done backwards.

In systems like spreadsheets removing an item at the top of the sheet or the left side of the sheet is done by shifting everything to the left or top. If you have a lot of data each delete is expensive. Doing a delete starting at the bottom or the right side may be faster.

Another thing to look at is doing it as a group, deleting many rows or columns at a time, thus cutting down on the number of shifts.

to delete from the right side first change this line:

for (var i = 14; i <= numColumns - 3; i++) 

to this line:

for (var i = numColumns - 3; i >= 14 ; i--) 

Answer 2

I believe the following does what you want. Example of results here.

In part one it preserves columns through N and the last column (whatever that is). The second portion hides rows rather than deletes them.

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var columns = sheet.getDataRange();
  var numColumns = columns.getNumColumns();
  Logger.log('total cols' + numColumns);
  var toDelete = numColumns-15;
  Logger.log('to del- '+toDelete)

  if (toDelete >0){
  sheet.deleteColumns(14, toDelete)
  }

  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();
  var rowsDeleted = 0;
  for (var i = 4; i <= numRows - 1; i++) {
    var row = values[i];
     if (row[0] == 0 || row[0] == 'x') {
      sheet.hideRows((parseInt(i)) - rowsDeleted);
     rowsDeleted++;
    }
  } 
}