I don't look for =ImportRange because I want to stay within the same Google Sheets spreadsheet. Instead, I want to import a column from one sheet into a column of another sheet within the same spreadsheet.
One way to do this is with =ArrayFormula. If the sheet you want to import from is named "Sheet1" and you want to select the entirety of column A, you can do the following:
You can see an example of this in action here: https://docs.google.com/spreadsheets/d/1kJraNDzrLF8-W_BxbL8pU6e_QuoMqFcl_sgyzT14L2Y/edit?usp=sharing
In general you can use the form
[SheetName]![Range] to pull in data from another sheet from within the same spreadsheet. In this case, ArrayFormula is needed to expand the entire range instead of just importing the first cell in the range.
I don't look for =ImportRange because I want to stay within the same Google Sheets spreadsheet.
seems to me to be repetition of a misconception I think I have seen elsewhere. IMPORTRANGE works to fetch data from the same document or even the same sheet.
It may not be the best solution is all cases, but it is functional. For example:
=importrange(" k e y ","Sheet1!A:A")
will copy ColumnA to any other chosen (available) column even when
Sheet1 is the only sheet in the document. However authorisation is still required.
A possible use case is where reference to the column on the extreme left is to be maintained even after insertion of a new
ColumnA. (In the formula
Sheet1!A:A does not auto adjust when a column is inserted on the extreme left, it remains