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

Google Apps Scripts: Deleting and Inserting Columns in Sheets

Working with google sheets one quickly realises there are many good reasons to automate many tasks. Even simple tasks like deleting a column(s) or inserting a column(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 columns.

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


function delColumnRange(strCol, numOfCols, sheet)

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


function insColumnRangeAfter(strCol, numOfCols, sheet)
function insColumnRangeBefore(strCol, numOfCols, sheet)

Putting it all together, the full code:


function delColumnRange(strCol, numOfCols, sheet) {
  sheet.deleteColumns(strCol, numOfCols);
};

function insColumnRangeAfter(strCol, numOfCols, sheet) {
  sheet.insertColumnsAfter(strCol, numOfCols);
};

function insColumnRangeBefore(strCol, numOfCols, sheet) {
  sheet.insertColumnsBefore(strCol, numOfCols);
};

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

  delColumnRange(3, 1, firstSheet);
  insColumnRangeAfter(2, 3, firstSheet);
  insColumnRangeBefore(1, 2, firstSheet);
};