Auto-Fill a Sheet with All Table/Column Names from Your App

Have you ever wanted a list of all Tables and Column Names in your app, displayed IN your app (not just in the editor)?

This can be useful for feeding dropdowns in other tables, especially when using another table to control security, user-permissions, etc.


This method uses Google Apps Script to write a custom function that can be used as a sheet formula.

2020-08-11 07.31.28

Just open the script editor, and paste in the following code:

Screen Shot 2020-08-11 at 7.52.41 AM

/******************************************************************
FUNCTION:     'allHeaders()' 
USAGE:        Cell Formula '=allHeaders()'
RETURN VALUE: 2D array of all Table/Column Header Names (minus the current sheet)

TABLE  | COLUMN
================
Table1 | Column1
Table1 | Column2
Table1 | Column3
Table2 | Column1
Table2 | Column2
Table2 | Column3

APPS SCRIPT BY GREENFLUX, LLC  |  www.greenflux.us
*****************************************************************/

function allHeaders() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = spreadsheet.getSheets();
  var cur_sh_name = SpreadsheetApp.getActiveSheet().getName();
  var allHeaders = [];
  
  for (var i=0 ; i<sheets.length ; i++){
    var sh_name = sheets[i].getName();
    
    if (sh_name != cur_sh_name){
      var sheet = spreadsheet.getSheetByName(sh_name);
      var lastCol = sheet.getLastColumn();
      var headerNames = sheet.getRange(1, 1, 1, lastCol).getValues();
      
      for (var j=0; j<lastCol ; j++){
        var row = [];
        
        row.push(sh_name);
        row.push(headerNames[0][j]);
        allHeaders.push(row);
      }
    }
  }
  
  return allHeaders;
}

Name, and save the script.

Then use the new '=allHeaders()' function in a new sheet to store all your table/column names.

Screen Shot 2020-08-11 at 7.58.45 AM


NOTE: For best performance, copy the resulting Table/Column values, and PASTE VALUES ONLY back over the top of the same range.

This will remove the formula and store a ‘snapshot’ of the table/column names instead of a formula that has to recalculate every sync. Just be sure to re-use the formula to update the sheet after any table/column edits.

9 Likes

Nice!

Bravo bravissimo @GreenFlux

3 Likes