LinkedIn
Twitter
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.
Just open the script editor, and paste in the following code:
/******************************************************************
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.
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.
Updated to alert when SQL reserved words are used as column names:
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]);
}
}
}
}
