Google Sheets Script. getDisplayValues adds Leading Space

Question

Sample File

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.

I cannot delete this format from my original sheet/range. Why not? — TheMaster
– TheMaster, Commented Dec 28, 2021 at 12:08

TheMaster · Accepted Answer · 2021-12-28 13:48:29Z

2

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)

edited Dec 28, 2021 at 13:48

answered Dec 28, 2021 at 12:15

TheMaster

51.6k7 gold badges76 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user555121 Over a year ago

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

TheMaster Over a year ago

@Kos What makes you think it is displayed without spaces?Or Do you mean it is displayed without spaces in the documentation?

user555121 Over a year ago

latter, so documentation itself is not useful without trying and see that it's actually spaced

TheMaster Over a year ago

@Kos You're right. I guess you can send documentation feedback on the top of that page.

user555121 Over a year ago

just sent the feedback!

|

Collectives™ on Stack Overflow

Google Sheets Script. getDisplayValues adds Leading Space

Sample File

Problem

1 Answer 1

Note:

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Problem

1 Answer 1

Note:

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related