COLUMN_VALUES Function for Google Sheets
The custom function COLUMN_VALUES for Google Sheets returns the non-empty values of a range of cells as an array, in a column format.
Simply copy and paste the code of the COLUMN_VALUES function into the script editor to be able to use it later (for more details, check out the Add a Custom Function to Google Sheets page).
Usage Example
The COLUMN_VALUES function here lists the data from the non-empty cells of the range A2:B7:
=COLUMN_VALUES(A2:B7)
Preview:
Google Apps Script Code of the Function
The code of the function to copy-paste into the script editor:
function COLUMN_VALUES(values) {
// Source: https://www.sheets-pratique.com/en/codes/column-values-function
// Range on one column
let array = values.flat();
// Removal of empty values
array = array.filter(x => x != '');
// Returns the array
return array.length ? array : '';
}