2

Sample File

enter image description here

Users add new values by copying them from an external source. It creates a visual effect of a small leading space. Usual formulas can delete this space:

=""&A1"

↑ will produce the correct result.

Problem

when I use:

function display(rA1) {
  var r = SpreadsheetApp.getActiveSheet().getRange(rA1);
  return r.getDisplayValues();
}

The result will include leading spaces

Test1 → Test1

I cannot delete this format from my original sheet/range: if I click [Ctrl]+[\].

UPD1. Possible to copy and paste Format only.

Max Makhrov
  • 17,309
  • 5
  • 55
  • 81

1 Answers1

3

This is because the range has a number format:

_-* #,##0.00\ _₽_-;\-* #,##0.00\ _₽_-;_-* "-"??\ _₽_-;_-@

Ignoring numbers formats, the text format is

_-@

Where, _ according to documentation means

_:Skips the next character and renders a space. This is used to line up number formats where the negative value is surrounded by parenthesis.

Formulas like TEXT and scripts using range.getDisplayValues() can directly retrieve the formatted value:

=LEN(TEXT("Text1", "_-* #,##0.00\ _₽_-;\-* #,##0.00\ _₽_-;_-* ""-""??\ _₽_-;_-@"))//6

Note:

Formats, even if you did not apply it directly, the html data from the clipboard maybe directly converted to number formats by sheets application(Sheets api can also do the same)

TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • Wow, that's brilliant. But I don't get documentation example: `[Blue]#,##0;[Red]#,##0;[Green]0.0;[Magenta]_(@_)` shouldn't Magenta be rendered with spaces in this case? `MyText` is displayed without any spaces it's a bit misleading – Kos Dec 28 '21 at 13:48
  • @Kos What makes you think it is displayed without spaces?Or Do you mean it is displayed without spaces in the documentation? – TheMaster Dec 28 '21 at 13:54
  • 1
    latter, so documentation itself is not useful without trying and see that it's actually spaced – Kos Dec 28 '21 at 13:57
  • 1
    @Kos You're right. I guess you can send documentation feedback on the top of that page. – TheMaster Dec 28 '21 at 14:03
  • just sent the feedback! – Kos Dec 28 '21 at 14:23
  • Thanks for this. The reason did not come to my mind as I did not often use number formats for text. Changed my sample number format to `0;0;0;_-@` in order to simplify the case. – Max Makhrov Dec 28 '21 at 14:47