Google Apps Scripts: Deleting and Inserting Rows in Sheets

Walden Systems Geeks Corner News Google Apps Scripts: Deleting and Inserting Rows in Sheets Rutherford NJ New Jersey NYC New York City North Bergen County

Working with google sheets one quickly realizes there are many good reasons to automate many tasks. Even simple tasks like deleting a row(s) or inserting a row(s) get tedious, annoying and time consuming. In this blog we will go over a couple simple yet very useful functions to automate the task of deleting and inserting rows.

Let's start with deleting a range of rows we no longer need. We will pass a starting row position to delete, followed by a number of rows to remove. The function signature will accept three parameters: strRow, numOfRows, working-sheet.


function delRowRange(strRow, numOfRows, sheet)


To insert rows one first has to decide whether we want to insert a row before or after the starting position. Therefore we are going to create two separate functions, one to insert after a row and one to insert before the selected row. The function signatures are going to be the same: strRow, numOfRows, working-sheet.


function insRowRangeAfter(strRow, numOfRows, sheet)
function insRowRangeBefore(strRow, numOfRows, sheet)

Putting it all together, the full code:


function delRowRange(strRow, numOfRows, sheet) {
  sheet.deleteRows(strRow, numOfRows);
};

function insRowRangeAfter(strRow, numOfRows, sheet) {
  sheet.insertRowsAfter(strRow, numOfRows);
};

function insRowRangeBefore(strRow, numOfRows, sheet) {
  sheet.insertRowsBefore(strRow, numOfRows);
};

function myMainDelRow() {
  const sSheet = SpreadsheetApp.getActiveSpreadsheet();
  var firstSheet = sSheet.getSheets()[0];

  delRowRange(3, 1, firstSheet);
  insRowRangeAfter(2, 3, firstSheet);
  insRowRangeBefore(12, 2, firstSheet);
};