How to fix Google Spreadsheets removing leading spaces

There is a problem with pasting text with leading spaces in Google Spreadsheets, leading to spaces being removed.

SQL statements that require indentation to read are often pasted into Google Spreadsheets, but all indented leading spaces are removed, which worsens readability.

1. The problem of removing leading spaces in Google Sheets

Copy the following SQL,

SELECT x, y
  FROM (SELECT 1 AS x, true AS y UNION ALL
        SELECT 9, true UNION ALL
        SELECT NULL, false)
 ORDER BY x NULLS LAST;

+------+-------+
| x    | y     |
+------+-------+
| 1    | true  |
| 9    | true  |
| NULL | false |
+------+-------+

(source: https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax?hl=ko#order_by_clause)

When pasting into a Google Spreadsheet, all leading spaces are removed as follows:

2. How to Preserve Leading Spaces in Google Spreadsheets

If you add ' (single quotation mark (=single quotation), single quotation) as the first character of each line in the editor and paste it into Google Spreadsheet, the leading space is not removed.

For example, in Notepad++, when you have the following text:

Place the cursor on the first row, first column, Alt + C Press the key or “Edit > Column Mode… Run the ” menu and enter the character '(single quotation mark (=single quotation mark), single quotation) Enter When the key is pressed, it is added as the first character of each line as follows.

Now, if you copy the text above and paste it into a Google Spreadsheet, leading spaces are preserved as shown below.

Single quotation marks are invisible at a glance, but appear in the upper input bar when you click a cell.

However, in this state, even if you copy the text of Google Spreadsheet and paste it elsewhere, single quotation marks are excluded and the original text is pasted including leading spaces. There seems to be no problem with single quotes other than to preserve leading whitespace.

I was concerned about this problem for a while, so I googled it, but couldn't find it. I suddenly thought about adding a single quotation mark to format text in Excel, so I tried it, and the problem was solved cleanly.

For reference, in DBeaver, execute the following process.

1. Alt + Shift + A Switch to column edit mode with key
2. Shift + arrow Select the first column using the key and enter single quotation marks
3. again Alt + Shift + APress to switch to normal editing mode
4. Copy the text and paste it into a Google Spreadsheet

This method can be applied to any text editor that supports column mode editing. Depending on the editor, shortcut keys or menu functions may be slightly different, but it is enough to add a single quotation mark to the first column.

We looked at how to maintain indentation when inputting source code such as SQL or Python into Google Spreadsheet. If there is another way, please let me know in the comments.

* Summary in English: How to keep leading space in Google Spreadsheets.