The allHeaders function retrieves all table and column headers from all sheets within a Google Sheets document, except the currently active sheet. Each header is represented as a pair [table name, column name].
Orignal Blog Post: https://www.googlecloudcommunity.com/gc/Tips-Tricks/Auto-Fill-a-Sheet-with-All-Table-Column-Names-from-Your-App/m-p/262203
To use the function:
-
Copy the Script: Copy the
allHeadersfunction into the script editor of your Google Sheets document. -
Cell Formula: Enter the cell formula
=allHeaders()in any cell of your Google Sheets document. -
Result: The function will return a 2D array containing all table and column headers, excluding the headers from the currently active sheet.
The function returns a 2D array where each row represents a table and column header pair. The format of each row is [table name, column name].
| TABLE | COLUMN |
|---|---|
| Table1 | Column1 |
| Table1 | Column2 |
| Table1 | Column3 |
| Table2 | Column1 |
| Table2 | Column2 |
| Table2 | Column3 |