/******************************************************************
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;
}
'=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.
Nice!
Bravo bravissimo @GreenFlux
=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]);
}
}
}
}
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.