How To Add Multiple Columns to Google Spreadsheets

I recently ran into this problem when I was looking at someones Adwords account and wanted to get an idea about all the unique keywords that they were using.  I used the following formula to get an array of all unique keywords:

=UNIQUE(SPLIT(CONCATENATE('Keyword Report'!B2:B)," "))

But then I encountered a problem:

“Error Result was not automatically expanded, please insert more columns ([number-of-columns]).”

Soon, I found that the current best option is to highlight and copy a number of columns and insert the copied columns over and over until there are enough columns. It seemed there should be a better way. After all, you can add an arbitrary number of rows to a spreadsheet. So just like the script to delete multiple sheets, I made the simple functionality.

The script looks like this:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var lastCol = sheet.getLastColumn();
var maxCols = sheet.getMaxColumns();

function onOpen() { 
 // Try New Google Sheets method
 try{
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Sheet Expander')
  .addItem('Add Columns', 'Add Columns')
  .addToUi(); 
 } 
 
// Log the error
 catch (e){Logger.log(e)}
 
// Use old Google Spreadsheet method
 finally{
  var items = [
  {name: 'Add Columns', functionName: 'addColumns'}
 ];
 ss.addMenu('Sheet Expander', items);
 }
}

function addColumns(){
  // Input box asks how many columns
  var numCols = Browser.inputBox("How Many Columns Would You Like to Add? (Max: " + (maxCols - lastCol) + ")");
  // That number of columns are appended to the right side of the sheet
  sheet.insertColumns(lastCol +1 , numCols);
}

Using the Script

1. Open the Google Spreadsheet Script Editor from the menu: Tools > Script Editor…
2. Copy and paste the script into a new script file and save: File > Save
3. Refresh the Google Spreadsheet window.
4. Once the pages has refreshed, there should be a new menu called “Sheet Expander”
5. Go to: Sheet Expander > Add Columns, and enter the number of columns you would like to add.

Note: Google Spreadsheets claims to impose a maximum limit on columns within a spreadsheet. They say it is 256 but it seems like it is possible to add many more. Perhaps this depends on number of rows in the spreadsheet.

Hope this saves you some time.