Tag Archives: Spreadsheet

Google Spreadsheet – How to put indent to a spreadsheet cell

Today I was working in Google spreadsheet (Google Sheet) for preparing effort estimation for one of the project. Then I realized the need of indentation to move the text of one of the cell to right (to visually show grouping), however I struggle with this for so long but didn’t found any option. After more of exploration on Internet, found a very interesting solution and thought of sharing the same in this blog.

The best solution is to write a script.

To do this 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:

var ss = SpreadsheetApp.getActiveSpreadsheet();

function indentText() {
  var values = ss.getActiveRange().getValues();
  var newValues = new Array();
 
  for (i = 0; i < values.length; i++) {
    if (values[i][0] != ”) {
      newValues.push([‘=CONCAT(REPT( CHAR( 160 ), 5),”‘ + values[i][0] + ‘”)’]);
    } else {
      newValues.push([”]);  
    }
  }  
  ss.getActiveRange().setValues(newValues);  
};function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : “Indent Text”,
    functionName : “indentText”
  }];
  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] + ‘”)’]);

Hope people find this useful. 🙂
Advertisements