Skip to content

Instantly share code, notes, and snippets.

@lstude

lstude/0-README.md

Last active Nov 17, 2020
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

@gtcoderme gtcoderme commented Dec 29, 2015

Great add-on. Worked as expected. Thanks!

@rydalch

This comment has been minimized.

Copy link

@rydalch rydalch commented Feb 19, 2016

Works great! Thank you!

@sachinaghor

This comment has been minimized.

Copy link

@sachinaghor sachinaghor commented Apr 15, 2016

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

@BLucky20

This comment has been minimized.

Copy link

@BLucky20 BLucky20 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

@Onore Onore 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

@dylantlorimer dylantlorimer 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

@rsdigital2013 rsdigital2013 commented Aug 18, 2016

Useful! Thanks...!

@62mkv

This comment has been minimized.

Copy link

@62mkv 62mkv 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

@wmupchurch wmupchurch 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

@MaTaLo81 MaTaLo81 commented Oct 5, 2017

thx!

@mathetes87

This comment has been minimized.

Copy link

@mathetes87 mathetes87 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

@austinj austinj commented Jan 30, 2018

Great! Thanks so much for this.

@turtleinaturtleneck

This comment has been minimized.

Copy link

@turtleinaturtleneck turtleinaturtleneck 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

@mhandiekar mhandiekar commented Feb 10, 2018

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

@barkin17

This comment has been minimized.

Copy link

@barkin17 barkin17 commented Mar 16, 2018

it was so necessary thnx

@cyndiloowho

This comment has been minimized.

Copy link

@cyndiloowho cyndiloowho 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

@pnatraj pnatraj 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

@jaymon0703 jaymon0703 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

@apurbapradhan apurbapradhan 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

@CShahed CShahed 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

@wallacejme wallacejme 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

@rojascesar rojascesar commented Feb 17, 2019

works great! thanks!

@ebell451

This comment has been minimized.

Copy link

@ebell451 ebell451 commented Feb 24, 2019

Thank you for sharing.

@stmaccharoli

This comment has been minimized.

Copy link

@stmaccharoli stmaccharoli 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

@rafaelbenavent rafaelbenavent 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

@milkburp milkburp 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

@torenunez torenunez commented May 14, 2019

Works for me. Thanks!

@RobertLewis60

This comment has been minimized.

Copy link

@RobertLewis60 RobertLewis60 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

@uturnr uturnr 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

@gitrormeister gitrormeister 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.

@quesyrahsarah

This comment has been minimized.

Copy link

@quesyrahsarah quesyrahsarah commented Jan 23, 2020

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!

@uturnr

This comment has been minimized.

Copy link

@uturnr uturnr commented Jan 23, 2020

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

😊

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.