Google Apps Script is taking too much time, is there a way to reduce the runtime?

Question

function dataManp() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("My-Sheet-1");
  var pasteSheet = ss.getSheetByName("My-Sheet-2");
  var clearContentRange = pasteSheet.getRange("A1:Z100");
  clearContentRange.clearContent();

  var source = copySheet.getRange("a1:f100");

  var destination = pasteSheet.getRange("a1:f100");

  source.copyTo(destination, {formatOnly:true , contentsOnly:true});
  source.copyTo(destination,SpreadsheetApp.CopyPasteType.PASTE_COLUMN_WIDTHS,false);

  var rows = pasteSheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  var rowsDeleted = 0;
  for (var i = 0; i <= numRows - 1; i++) {
    var row = values[i];
    if (row[3] == '') {
      var deleteRowNum = (parseInt(i)+1) - rowsDeleted
      pasteSheet.deleteRow(deleteRowNum);
     // temp_array[i] = i
      rowsDeleted++;
    }
  }
  pasteSheet.deleteColumn(2)
}

Hi,

I have written the following script to perform the following operations,

1. Copy data from My-Sheet-1 and paste to My-Sheet-2
2. Deletion of rows that corresponds to empty cells in column 3.
3. After that deletion of column 2

Rigthnow, the My-Sheet-1 contains only 60 rows and 20, the script is taking approximately 7 secs to complete. However in future the number of rows may extend to some 1000. Is there a way to optimize the above code, so that it takes less time to complete.

My observation is that, copy and pasting the data takes just milli secs. The major time consuming part are the operations, I am performing in the pasteSheet after pasting it. It may be helpful, if we can copy My-Sheet-1 to a temporary variable (copy everything including the formulas, format specifications, values, text etc..) and perform all operations in the temporary variable and then paste everything in the temporary variable to the desired target sheet. But, I don't know, how to copy everything in a sheet to a temporary variable, also, I am not sure, whether this will reduce the time or not. I would be glad, if I can get some help on this as well (i.e. copying everything in a sheet to a temporary variable, perfrom operations on the variables and then paste data in the variable to a new sheet)

Thank you

Edit - 1

Would like to add that, My-Sheet-1 contains mixed data (i.e. numerics, color formatted text, formulas in some cells etc)

Answer 1

Explanation:

deleteRow() takes some time per execution, so it's not recommended to use on hundreds of rows in a loop.

Simple answer would be:

1. Make a 2D array for Sheet1 using getValues().
2. Delete / filter out array elements depending if row2 is blank.
3. Use setValues() to write the filtered array into Sheet2.

Sample Code:

function dataManp() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("My-Sheet-1");
  var pasteSheet = ss.getSheetByName("My-Sheet-2");
  var lr = copySheet.getLastRow();
  var clearContentRange = pasteSheet.getRange(1,1,lr,26);
  clearContentRange.clearContent();

  var source = copySheet.getRange(1,1,lr,6);
  var destination = pasteSheet.getRange(1,1,lr,6);

  source.copyTo(destination, {formatOnly:true , contentsOnly:true});
  source.copyTo(destination,SpreadsheetApp.CopyPasteType.PASTE_COLUMN_WIDTHS,false);
  destination.clearContent();

  var values = source.getValues();

  var temp_array = [];
  for (var i = 0; i < lr; i++) {
    var rowValue = values[i];
    if (rowValue[2] != '') {
      temp_array.push(rowValue);
    }
  }

  var newDest = pasteSheet.getRange(1,1,temp_array.length,6)
  newDest.setValues(temp_array);
  pasteSheet.deleteColumn(2);
}

One caveat is that you need to have the same format for all rows in a column.

Sample Input:

Sample Output: