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:



google sheets function column values

Google Apps Script Code of the Function

The code of the function to copy-paste into the script editor:

function COLUMN_VALUES(values) {
  // Source:
  // Range on one column
  let array = values.flat();
  // Removal of empty values
  array = array.filter(x => x != '');
  // Returns the array
  return array.length ? array : '';