1

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++;
    }
  }
}

2 Answers 2

3

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--) 
0
0

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++;
    }
  } 
}
3
  • The problem I have with deleting a number of columns is that the number that needs to be deleted is variable. The spreadsheet is laid out for an entire year, so for week one it is deleting 51 weeks, then 50 weeks, then 49 weeks and so on. So I need it to always preserve the first 14 columns of data, then find the last column and delete everything between column 14 and the second to last column. Thanks for the help thus far.
    – jnohl
    Oct 6, 2016 at 17:47
  • Even after switching to hideRow as you indicated, it still seems to be deleting the rows. Is there anything else that would need to be modified?
    – jnohl
    Oct 6, 2016 at 17:54
  • Edited response. Oct 6, 2016 at 18:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.