13 June 2014

Add indent capability to your Google Drive spreadsheets

This addition to your Google Drive script repository allows you to indent and outdent text within a cell or multiples cells of a Drive spreadsheet.

Detailed Discussion

 https://productforums.google.com/forum/#!topic/docs/lUI-yFixCjw

Procedure

Open up the spreadsheet and select menu item "Tools" --> "Script Editor"
Once the script editor opens copy and paste the following code into the "Code.gs" window:


/**
 * Adds indent capability to Drive spreadsheets. 
 * More at https://productforums.google.com/forum/#!topic/docs/lUI-yFixCjw

 */

var ss = SpreadsheetApp.getActiveSpreadsheet();


function moveText(direction) {
  var values = ss.getActiveRange().getValues();
  var cols = ss.getActiveRange().getNumColumns();
  var rows = ss.getActiveRange().getNumRows();

  
  var newValues = new Array();

    
  for (x = 1; x <= rows; x++) {
    for (y = 1; y <= cols; y++) {
      var cell = ss.getActiveRange().getCell(x, y);
      var value = cell.getValue();
      var formula = (direction == ">>>") ? '=CONCAT(REPT( CHAR( 160 ), 5),"' + value + '")'
      : '=IF(TRIM(LEFT("' + value + '", 5))=CONCAT(REPT( CHAR( 160 ), 5),""), MID("' + value + '", 6, LEN("' + value + '")), TRIM("' + value + '"))';
      
      if (value != '') {
        cell.setFormula([formula]);
        cell.setValue(cell.getValue());
      } else {
        cell.setValue(['']);
      }
    }
  }
};


function indentText() {
  moveText(">>>");
};


function flushLeft() {
  moveText("<<<");

};


function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();

  var entries = [{
    name : ">>>",
    functionName : "indentText"
  },{
    name : "<<<",
    functionName : "flushLeft"

  }];
  sheet.addMenu("Indent Text", entries);
};


Go to menu "File" --> "Save"
Return to your spreadsheet and reload the browser.  You should now see a new menu item called "Indent Text" to the right of the "Help" menu.

What this code does is add a menu to the spreadsheet document called "Indent Text".
Within this menu it will create a menu item called "Indent Text".
When the "indent Text" menu item is selected any cells within a single column selection will be indented by 5 spaces.

You can increase or decrease the number of spaces the text is indented by changing the line below so that the "5" is changed to a larger or smaller number (depending on your preference)

      newValues.push(['=CONCAT(REPT( CHAR( 160 ), 5),"' + values[i][0] + '")']);

This code will work on multiple cells so you can select an entire column and indent in bulk.

Note that I have included the modified version of code (by andyrau) that allows you to "unindent," rather than the original code by leighelliott78. Thanks to both leighelliott78 and andyrau for this helpful script!


2 comments:

Project Manager said...

This appears to just indent the first line, vs indenting the whole cell (the way the Excell indent function works)?

Curtis said...

Yes, Project Manager, you are correct. Unfortunately. :-(