Google Spreadsheet Script to Insert more than one number of rows or columns

Question:

( by Margaret Krohn )

Proposal: "Insert 1 above" or "Insert 1 below" option is nice, but sometimes, I want to insert multiple rows or columns. It would be nice if I could provide a number amount instead of doing them one at a time. 

Repro steps: 

1. Right click column or row

2. Notice "Insert 1 above" or "Insert 1 left" 

Expected Result: Allow user to choose amount of columns or rows the user would like to insert. "1" can be the default.

Solution:

Have a look at the following animated screenshot:

Have a look at the following code:

///////////////////////////////////////

// This script has been developed by Kishan
// For more visit: iGoogleDrive.blogspot.com

function onOpen(){
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [];
  menuEntries.push({name: "Insert Rows After", functionName: "insertRowsAfter"});
  menuEntries.push({name: "Insert Rows Before", functionName: "insertRowsBefore"});
  menuEntries.push({name: "Insert Columns After", functionName: "insertColumnsAfter"});
  menuEntries.push({name: "Insert Columns Before", functionName: "insertColumnsBefore"});
  spreadsheet.addMenu("Script", menuEntries);
};

function insertRowsAfter() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var cell = s.getActiveCell();
  var row = cell.getRow();
  
  var num = Browser.inputBox("Enter No. of rows:");  
  s.insertRowsAfter(row,num);
};

function insertRowsBefore() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var cell = s.getActiveCell();
  var row = cell.getRow();
  
  var num = Browser.inputBox("Enter No. of rows:");
  s.insertRowsBefore(row,num);
};

function insertColumnsAfter() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var cell = s.getActiveCell();
  var row = cell.getColumn();
  
  var num = Browser.inputBox("Enter No. of columns:");
  s.insertColumnsAfter(row,num);
};

function insertColumnsBefore() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var cell = s.getActiveCell();
  var row = cell.getColumn();
  
  var num = Browser.inputBox("Enter No. of columns:");
  s.insertColumnsBefore(row,num);
};

///////////////////////////////////////

put the above code in your script editor, and then you can use it directly in your Google Spreadsheet.

After inserting the above code in your spreadsheet, you can refresh your spreadsheet and then you would be able to see the custom menu "Script" on the menu bar. And then in menu "Script" click on the sub menus:

• "Insert Rows After"
• "Insert Rows Before"
• "Insert Columns After"
• "Insert Columns Before"

And If you are not much familiar with scripts then check out the following link:http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive