Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Add Indent / Outdent Functionality to Google Spreadsheets

This tutorial demonstrates how to add an "Indent Text" menu item to a Google spreadsheet so you can indent/outdent 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 works on multiple cells so you can select an entire column and indent in bulk.
  1. Open your spreadsheet and select menu item "Tools" --> "Script Editor" step 1

  2. Once the script editor opens, copy and paste the code into the "Code.gs" file, beneath the code that's already included. step 2

  3. Go to menu "File" --> "Save" step 3

  4. Return to your spreadsheet and reload the browser. You may get a pop-up asking you to grant access to your new script - approve it. Once granted access, you should now see a new menu item called "Indent Text" to the right of the "Help" menu. Yay! indenting

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);
};
@gtcoderme

This comment has been minimized.

Copy link

commented Dec 29, 2015

Great add-on. Worked as expected. Thanks!

@rydalch

This comment has been minimized.

Copy link

commented Feb 19, 2016

Works great! Thank you!

@sachinaghor

This comment has been minimized.

Copy link

commented Apr 15, 2016

This is awsome!!! works like charm. Thanks for sharing

@BLucky20

This comment has been minimized.

Copy link

commented Apr 27, 2016

I followed these instructions exactly, but nothing changed in Google Sheets. The "Indent Text" item was never added to the menu. I tried in three different browsers. Could you please help me? Do you have any idea what the problem could be? Is this available as an add-on? I really want to add indentation functionality to Sheets. The columns are too crowded for me to read comfortably. Your help would be greatly appreciated!

@Onore

This comment has been minimized.

Copy link

commented May 19, 2016

Hello
I have got #ERROR! in cell after click on "Indent text"

For example when I past formula =CONCAT(REPT( CHAR( 160 ), 5),"12") I also have got the #ERROR!

@dylantlorimer

This comment has been minimized.

Copy link

commented Aug 4, 2016

Is there a way to grant access to ANYONE who uses the sheet?

@rsdigital2013

This comment has been minimized.

Copy link

commented Aug 18, 2016

Useful! Thanks...!

@62mkv

This comment has been minimized.

Copy link

commented Sep 16, 2016

just for the history:

Кнопка "Купить" >>>>> ERROR
Кнопка ""Купить"" >>>>> Кнопка "Купить"

Embedded quotation marks are stripped as a result of the script run. They have to be escaped somehow, I believe

@wmupchurch

This comment has been minimized.

Copy link

commented Jul 29, 2017

Fantastic! Thank you! One request. This moves the text, but when you have word wrap on, it only moves the first line. I think it's because it isn't a 'true' indent, like a bullet point or paragraph indent would do. Is it possible to indent the second, third, etc. lines?

@MaTaLo81

This comment has been minimized.

Copy link

commented Oct 5, 2017

thx!

@mathetes87

This comment has been minimized.

Copy link

commented Dec 1, 2017

@Onore I had the same problem. It was caused by my location settings. If you use semicolon instead of comma in your formulas, try with this code instead:

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);
};
@austinj

This comment has been minimized.

Copy link

commented Jan 30, 2018

Great! Thanks so much for this.

@turtleinaturtleneck

This comment has been minimized.

Copy link

commented Feb 6, 2018

First time doing something like this with Google Sheets. Works perfect - thank you!

@mhandiekar

This comment has been minimized.

Copy link

commented Feb 10, 2018

Great add-on. Works perfectly. Thanks for sharing.

@barkin17

This comment has been minimized.

Copy link

commented Mar 16, 2018

it was so necessary thnx

@cyndiloowho

This comment has been minimized.

Copy link

commented May 18, 2018

I'm not a computer person...but created an account and followed these instructions. It worked great!!

The only thing I also want to know is how can I auto indent text in a column based on a value in another cell (same row)...anyone?

@pnatraj

This comment has been minimized.

Copy link

commented May 28, 2018

Thank you very much, when i applied the same with a single cell with multiple line items it applied the indent to only the first value
for eg
Cell A:
Text1
Text2
Text3

when i applied indent >>> it just applied the indent to Text1

@jaymon0703

This comment has been minimized.

Copy link

commented Jul 4, 2018

Thanks...anyone know how to move the underline as well as a text string or value?

@apurbapradhan

This comment has been minimized.

Copy link

commented Jul 19, 2018

sweet. works the first time but when i close the tab and re-open, the indent text tab is no longer available and have to do the whole process again. is that normal?

@CShahed

This comment has been minimized.

Copy link

commented Sep 26, 2018

Great, the only issue is if I use a quotation mark (") then it shows error. @apurbapradhan, no it's not normal to redo the same thing everytime you close and re-open a tab.

@wallacejme

This comment has been minimized.

Copy link

commented Dec 7, 2018

Great job! Thank you so much for sharing.

@CShaded, I also had this problem with quotation marks. It happens because they conflict with the ones used as string delimiters in the formula. I managed to solve this issue by replacing any occurrence of " with "". It tells Google Sheets to consider it part of the value, instead of a string delimiter.

You can do this by changing the line

var value = cell.getValue();

to:

var value = cell.getValue().toString().replace(/"/gm, '""');

It worked in my case (I didn't test it against other situations, though).

@rojascesar

This comment has been minimized.

Copy link

commented Feb 17, 2019

works great! thanks!

@ebell451

This comment has been minimized.

Copy link

commented Feb 24, 2019

Thank you for sharing.

@stmaccharoli

This comment has been minimized.

Copy link

commented Apr 3, 2019

@pnatraj I am seeing the same behaviour. Were you able to figure out how to apply it to a single cell containing multiple values?

Thank you very much, when i applied the same with a single cell with multiple line items it applied the indent to only the first value
for eg
Cell A:
Text1
Text2
Text3

when i applied indent >>> it just applied the indent to Text1

@rafaelbenavent

This comment has been minimized.

Copy link

commented Apr 17, 2019

Thanks for the script! It was very useful. I just made a slight refactoring to your moveNext function. It now automatically indents the cell instead of setting its value to a formula only to remove it and replace it with its new value. This helps make it a bit faster.

function moveText(direction) {
  
  var indent = '     ';
  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();

      if (direction == '>>>')
      {
        cell.setValue(indent + value);
      }
      else if (value.indexOf(indent) == 0)
      {
        cell.setValue(value.substr(5));
      }
    }
  }
};
@milkburp

This comment has been minimized.

Copy link

commented Apr 25, 2019

Great Add-In! As noted by a few times in this thread, Double Quotations produce an error. wallacejme nails it with a quick fix in the script. Awesome stuff!!!

@torenunez

This comment has been minimized.

Copy link

commented May 14, 2019

Works for me. Thanks!

@RobertLewis60

This comment has been minimized.

Copy link

commented May 23, 2019

Is there a way to have it indent multiple lines when using the "wrap text" feature? The script works great, but it only indents the first line. Since I don't write code, I have no way to know how to make adjustments to have it indent multiple lines in the cell.

@uturnr

This comment has been minimized.

Copy link

commented Jun 13, 2019

I took @rafaelbenavent's moveText function and made it faster when indenting multiple cells at once, by updating the spreadsheet one time, rather than cell by cell.

function moveText(direction) {
  var indent = '     ';
  var range = ss.getActiveRange()
  var values = range.getValues();
  var newValues = [];

  for (rowI = 0; rowI < values.length; rowI++) {
    var row = values[rowI];
    newValues.push([]);
    for (colI = 0; colI < row.length; colI++) {
      var value = row[colI];

      if (direction == '>>>') {
        newValues[rowI].push(indent + value);
      } else if (value.indexOf(indent) == 0) {
        newValues[rowI].push(value.substr(5));
      }
    }
  }
  
  range.setValues(newValues);
};
@gitrormeister

This comment has been minimized.

Copy link

commented Sep 9, 2019

This is very slick. It would be the bomb if it could handle Text with links. Presently returns ERROR. Otherwise, very handy.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.