HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Looping through selected cells to indent text

Submitted by: @import:stackexchange-codereview··
0
Viewed 0 times
loopingtextcellsselectedthroughindent

Problem

I have written the following Google Spreadsheet script that attempts to loop through all selected cells and then update the value so that it adds an indent, by adding

=CONCAT(REPT( CHAR( 160 ), 5),"' + value[row] + '")


around the value.

Is there a better way to achieve this?

var ss = SpreadsheetApp.getActiveSpreadsheet();

function indentText() {
  var values = ss.getActiveRange().getValues();
  var newValues = new Array();

  for (i = 0; i < values.length; i++) {
    if (values[i][0] != '') {
      newValues.push(['=CONCAT(REPT( CHAR( 160 ), 5),"' + values[i][0] + '")']);
    } else {
      newValues.push(['']);  
    }
  }

  ss.getActiveRange().setValues(newValues);

};

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Indent Text",
    functionName : "indentText"
  }];
  sheet.addMenu("Indent Text", entries);
};

Solution

Interesting question,

there is clearly a lack in the Google Script API to indent cells..

Still, I find the CONCAT approach ugly, if one wants to indent 2 times, the formula will be:

=CONCAT(REPT( CHAR( 160 ), 5),"=CONCAT(REPT( CHAR( 160 ), 5),"'Hello World'")")


Which is ugly, I would simply pre-calculate the prefix and concatenate in the script and not let the formula do the work. This will also make un-indenting much easier.

Code Snippets

=CONCAT(REPT( CHAR( 160 ), 5),"=CONCAT(REPT( CHAR( 160 ), 5),"'Hello World'")")

Context

StackExchange Code Review Q#42922, answer score: 3

Revisions (0)

No revisions yet.