Add Indent / Outdent Functionality to Google Spreadsheets

0-README.md

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-config.md

1. Open your spreadsheet and select menu item "Tools" --> "Script Editor"

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

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

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!

2-indent-outdent-functions.js

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

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!

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!

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

Useful! Thanks...!

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

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?

thx!

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

Great! Thanks so much for this.

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

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

it was so necessary thnx

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?

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

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

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?

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.

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).

works great! thanks!

Thank you for sharing.

@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

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

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!!!

Works for me. Thanks!

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.

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

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

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

@uturnr, i completely replaced the previous code w/ yours. but when i tried running it, i got an error. Error message below:

The number of columns in the data does not match the number of columns in the range. The data has 0 but the range has 1. (line 23, file "indentALL")

will your code indent multiple lines within a cell?
Example:
A1 (original)
Plants
Trees
Flowers
Herbs
Grass

A1 (after script)
Plants
-Trees
-Flowers
-Herbs
-Grass

Appreciate your help!

Hi @quesyrahsarah,

A few notes:

• The previous code that I provided was just one function that needed to be replaced among the other functions. This enables the "Indent Text" menu in the spreadsheet editor. (Select cells, then use that menu to indent)
• The previous code was not designed to be run directly from the code editor. However I did notice that error you noticed would also come up when removing indents if any of the selected cells didn't have indents, so I have fixed that in the example below.
• The previous code would only add or remove the first indent in the cell, not considering newlines. The new code below will indent multiple lines.
• This time I included the entire code so you should be able to replace what you have entirely. After saving, you may need to reload your spreadsheet or manually run the onOpen function from the code editor. I put this together quickly, so it might be buggy - please let me know if there are issues.

var ss = SpreadsheetApp.getActiveSpreadsheet();

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 == '>>>') {
        // Add indent(s)
        var strings = value.split("\n");
        var newStrings = [];
        for (stringI = 0; stringI < strings.length; stringI++) {
          var prefix = stringI === 0 ? "" : "\n";
          newStrings.push(prefix + indent + strings[stringI]);
        }
        newValues[rowI].push(newStrings.join(""));
      } else {
        // Remove indent(s)
        var strings = value.split("\n");
        var newStrings = [];
        for (stringI = 0; stringI < strings.length; stringI++) {
          var prefix = stringI === 0 ? "" : "\n";
          if (strings[stringI].indexOf(indent) == 0) {
            newStrings.push(prefix + strings[stringI].substr(5));
          } else {
            newStrings.push(prefix + strings[stringI]);
          }
        }
        newValues[rowI].push(newStrings.join(""));
      }
    }
  }
  
  range.setValues(newValues);
};

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

😊

This works really nice. Thanks to all.
Is there a way to get it to modify the format but not the content of the cell? (similar to the " @" custom formating)