Skip to content

Instantly share code, notes, and snippets.

@lstude
Last active December 2, 2023 15:41
  • Star 62 You must be signed in to star a gist
  • Fork 10 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save lstude/48b77d77a8a49c142e94 to your computer and use it in GitHub Desktop.
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);
};
@mhandiekar
Copy link

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

@barkin17
Copy link

it was so necessary thnx

@cyndiloowho
Copy link

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
Copy link

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
Copy link

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

@apurbapradhan
Copy link

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
Copy link

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
Copy link

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
Copy link

works great! thanks!

@ebell451
Copy link

Thank you for sharing.

@stmaccharoli
Copy link

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
Copy link

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
Copy link

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
Copy link

Works for me. Thanks!

@RobertLewis60
Copy link

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
Copy link

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
Copy link

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

@quesyrahsarah
Copy link

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
Copy link

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

😊

@FelipeAcha
Copy link

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)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment