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.

CodePudding user response：

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)