Skip to content

Instantly share code, notes, and snippets.

@SeptiyanAndika
Forked from lstude/0-README.md
Created December 27, 2017 13:21
Show Gist options
  • Save SeptiyanAndika/5789e9ab4785bb06fbfdebbc0492eabe to your computer and use it in GitHub Desktop.
Save SeptiyanAndika/5789e9ab4785bb06fbfdebbc0492eabe 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);
};
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment