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.

14 Likes

Nice!

Bravo bravissimo @GreenFlux

3 Likes

Updated to alert when SQL reserved words are used as column names:

Screen Shot 2021-01-07 at 8.52.01 PM Screen Shot 2021-01-07 at 8.52.09 PM

Secret Sauce
=query({IMPORTHTML("https://en.wikipedia.org/wiki/SQL_reserved_words","table")},"select Col1",1)'
/*******************************************************************
FUNCTION        | PURPOSE
================|===================================================
addTables       | Adds "Tables" sheet with list of all table headers

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

*/

function addTables() {
  var ui = SpreadsheetApp.getUi();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var tables = ss.insertSheet('Tables');
  
  // build array for new table
  var allHeaders = [];
  allHeaders.push(['Table','Column']);
  
  for (var i=0 ; i<sheets.length ; i++){
    var sh_name = sheets[i].getName();
    var sheet = ss.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);
      
    }
  }  
  
  // Format sheet
  tables.getRange(1,1,allHeaders.length,2).setValues(allHeaders);
  tables.getRange(1,1,1,2).setFontWeight('bold');
  tables.setFrozenRows(1);
  
  // Get list of reserved words. Leave no evidence! 
  tables.getRange(2,3).setFormula('=query({IMPORTHTML("https://en.wikipedia.org/wiki/SQL_reserved_words","table")},"select Col1",1)');
  var reservedWords = tables.getRange(2,3,tables.getLastRow(),1).getValues();
  tables.getRange(2,3).clear();
  
  // Copy the text from the multi-dimensional array to an array of strings
  var headStrArray = [];
  var headers = tables.getRange(2,2,tables.getLastRow(),1).getValues();
  
  for (var m=0; m<headers.length; m++){
    headStrArray.push(headers[m][0].toUpperCase());
  }
  
  // Check for reserved words
  var rw_len = reservedWords.length;
  for (var k=0; k<rw_len; k++){
    if (headStrArray.indexOf(reservedWords[k][0]) != -1) {
      if (reservedWords[k][0] != "") {
        tables.getRange(headStrArray.indexOf(reservedWords[k][0])+2,2).setFontWeight('bold');
        tables.getRange(headStrArray.indexOf(reservedWords[k][0])+2,2).setFontColor('red');
        ui.alert('RESERVED WORD DETECTED: ' + reservedWords[k][0]);
      }
    }
  }
  
}

5 Likes

Great work but. This is not working anymore!

To avoid the need to cut/paste, you could consider using .copyTo to a particular sheet/range and run hourly with a trigger (instead of cell formula calling function). Might be to often but makes the latest header list “static”. Depends how often you update and how “live” you need this data to be.