Google-sheets – Speed up Google Sheets scripts

google sheetsgoogle-apps-script

I use following scripts to do the following:

  1. Insert newline
  2. Copy info from line below to newline

script takes extremely long, how do I 

function rijToevoegen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(spreadsheet.getCurrentCell().getRow(), 1, 1, sheet.getMaxColumns()).activate();
  spreadsheet.getActiveSheet().insertRowsBefore(spreadsheet.getActiveRange().getRow(), 1);
  spreadsheet.getActiveRange().offset(0, 0, 1, spreadsheet.getActiveRange().getNumColumns()).activate();
  sheet = spreadsheet.getActiveSheet();
  sheet.getRange(spreadsheet.getCurrentCell().getRow() + 1, 1, 1, sheet.getMaxColumns()).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  var spreadsheet = SpreadsheetApp.getActive();
  var currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getActiveRange().getDataRegion(SpreadsheetApp.Dimension.COLUMNS).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(spreadsheet.getCurrentCell().getRow(), 1, 1, sheet.getMaxColumns()).activate();
  currentCell.activateAsCurrentCell();
  spreadsheet.getActiveRange().copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};

Best Answer

The problem with performance (speed) is in that you are calling Google API too often and unnecessary. Each call is time very consuming. API call is for e.g. .getDataRange(), .getRow(), getActiveSpreadsheet() and so on. Each of them can take up to a second - it depends a lot on size of sheet and other factors, but 200ms is pretty often.

You can find long execution times when you run your function in GAS editor and go in menu View - > Execution transcript - here you will learn times needed for different tasks.

So what you can do (at least) to speed up your function is to avoid repetitive calls of API, instead save result into variable and use the variable next time. As its shown in this code - I have edited your code to show the idea.. It should be much faster now.

function rijToevoegen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  var currentCell = spreadsheet.getCurrentCell();
  var activeRow = currentCell.getRow();
  var activeRange = spreadsheet.getActiveRange();
  sheet.getRange(activeRow, 1, 1, sheet.getMaxColumns()).activate();
  sheet.insertRowsBefore(activeRow, 1);
  activeRange.offset(0, 0, 1, activeRange.getNumColumns()).activate();
  //sheet = spreadsheet.getActiveSheet(); //not needed, its already defined up there
  sheet.getRange(currentCell.getRow() + 1, 1, 1, sheet.getMaxColumns()).copyTo(activeRange, SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  // var spreadsheet = SpreadsheetApp.getActive();//not needed, its already defined up there

  //rest of code im not changing - idea of what needs to be done is cler i think
  activeRange.getDataRegion(SpreadsheetApp.Dimension.COLUMNS).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(spreadsheet.getCurrentCell().getRow(), 1, 1, sheet.getMaxColumns()).activate();
  currentCell.activateAsCurrentCell();
  spreadsheet.getActiveRange().copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};

I’m not sure what you are trying to do with this code, but definitively it can be done in much more effective way - but I guess you are beginning with GAS, so you will learn through time and debugging.

Related Topic