Sql

Sql

Perform SQL SELECT using this class.

Constructor

new Sql()

Source:

Members

bindData

Source:
Properties:
Type Description
BindData

List of BIND data linked to '?' in statement.

columnTableNameReplacement

Source:
Properties:
Type Description
String

derived table name to output in column title replacing source table name.

columnTitle

Source:
Properties:
Type Description
Boolean

Are column tables to be ouptout?

tables

Source:
Properties:
Type Description
Map.<String, Table>

Map of referenced tables.

Methods

addBindNamedRangeParameter(value) → {Sql}

Description:

  • The BIND data is a sheet named range that will be read and used for bind data.

Source:
Parameters:
Name Type Description
value String

Sheets Named Range for SINGLE CELL only.
Returns:
Type
Sql

addBindParameter(value) → {Sql}

Description:

  • Add a bind data value. Must be added in order. If bind data is a named range, use addBindNamedRangeParameter().

Source:
Parameters:
Name Type Description
value any

literal data.
Returns:
Type
Sql

addColumnTitles(viewTableData, view) → {Array.<Array.<any>>}

Description:

  • Add column titles to data if needed.

Source:
Parameters:
Name Type Description
viewTableData Array.<Array.<any>>
view SelectTables
Returns:
Type
Array.<Array.<any>>

addTableData(tableName, tableData, cacheSeconds, hasColumnTitle) → {Sql}

Description:

  • Add data for each referenced table in SELECT, before EXECUTE().

Source:
Parameters:
Name Type Default Description
tableName String

Name of table referenced in SELECT.
tableData any

Either double array or a named range.
cacheSeconds Number 0

How long should loaded data be cached (default=0)
hasColumnTitle Boolean true

Is first data row the column title?
Returns:
Type
Sql

areColumnTitlesOutput() → {Boolean}

Description:

  • Query if this instance of Sql() will generate column titles.

Source:
Returns:
Type
Boolean

clearBindParameters() → {Sql}

Description:

  • Clears existing BIND data so Sql() instance can be used again with new bind parameters.

Source:
Returns:
Type
Sql

copyTableData(tableMap)

Description:

  • Copies the data from an external tableMap to this instance.
    It copies a reference to outside array data only.
    The schema would need to be re-loaded.

Source:
Parameters:
Name Type Description
tableMap Map.<String, Table>

enableColumnTitle(value) → {Sql}

Description:

  • Include column headers in return data.

Source:
Parameters:
Name Type Description
value Boolean

true will return column names in first row of return data.
Returns:
Type
Sql

execute(statement) → {Array.<Array.<any>>}

Description:

  • Parse SQL SELECT statement, performs SQL query and returns data ready for custom function return.
    Execute() can be called multiple times for different SELECT statements, provided that all required table data was loaded in the constructor.
    Methods that would be used PRIOR to execute are:
    enableColumnTitle() - turn on/off column title in output
    addBindParameter() - If bind data is needed in select. e.g. "select * from table where id = ?"
    addTableData() - At least ONE table needs to be added prior to execute. This tells execute where to find the data.
    Example SELECT and RETURN Data

      let stmt = "SELECT books.id, books.title, books.author_id " +
       "FROM books " +
       "WHERE books.author_id IN ('11','12') " +
       "ORDER BY books.title";

   let data = new Sql()
       .addTableData("books", this.bookTable())
       .enableColumnTitle(true)
       .execute(stmt);

   Logger.log(data);

[["books.id", "books.title", "books.author_id"],
   ["4", "Dream Your Life", "11"],
   ["8", "My Last Book", "11"],
   ["5", "Oranges", "12"],
   ["1", "Time to Grow Up!", "11"]]
Source:
Parameters:
Name Type Description
statement any

SELECT statement as STRING or AST of SELECT statement.
Returns:
  • Double array where first index is ROW and second index is COLUMN.
Type
Array.<Array.<any>>

getBindData() → {Array.<any>}

Description:

  • List of bind data added so far.

Source:
Returns:
Type
Array.<any>

getTables() → {Map.<String, Table>}

Description:

  • Returns a map of all tables configured for this SELECT.

Source:
Returns:
  • Map of referenced tables indexed by TABLE name.
Type
Map.<String, Table>

replaceColumnTableNameWith(replacementTableName) → {Sql}

Description:

  • Derived table data that requires the ALIAS table name in column title.

Source:
Parameters:
Name Type Description
replacementTableName String

derived table name to replace original table name. To disable, set to null.
Returns:
Type
Sql

select(selectAst) → {Array.<Array.<any>>}

Description:

  • Load SELECT data and return in double array.

Source:
Parameters:
Name Type Description
selectAst Object

Abstract Syntax Tree of SELECT
Returns:
  • double array useable by Google Sheet in custom function return value.
  • First row of data will be column name if column title output was requested.
  • First Array Index - ROW
  • Second Array Index - COLUMN
Type
Array.<Array.<any>>

selectFromSubQuery()

Description:

  • Modifies AST when FROM is a sub-query rather than a table name.

Source:

selectJoinSubQuery() → {void}

Description:

  • Checks if the JOINed table is a sub-query.
    The sub-query is evaluated and assigned the alias name. The AST is adjusted to use the new JOIN TABLE.

Source:
Returns:
Type
void

selectSet(leftTableData, unionAst) → {Array.<Array.<any>>}

Description:

  • Apply set rules to each select result.

Source:
Parameters:
Name Type Description
leftTableData Array.<Array.<any>>
unionAst Object
Returns:
Type
Array.<Array.<any>>

setBindValues(value) → {Sql}

Description:

  • Set all bind data at once using array.

Source:
Parameters:
Name Type Description
value BindData

Bind data.
Returns:
Type
Sql

setTables(mapOfTables)

Description:

  • Sets all tables referenced SELECT.

Source:
Parameters:
Name Type Description
mapOfTables Map.<String, Table>

Map of referenced tables indexed by TABLE name.

(static) cleanUp(viewTableData) → {Array.<Array.<any>>}

Description:

  • If no data and no titles, create empty double array so sheets function does not have an error.

Source:
Parameters:
Name Type Description
viewTableData Array.<Array.<any>>
Returns:
Type
Array.<Array.<any>>

(static) distinctField(ast) → {Object}

Description:

  • If 'GROUP BY' is not set and 'DISTINCT' column is specified, update AST to add 'GROUP BY'.

Source:
Parameters:
Name Type Description
ast Object

Abstract Syntax Tree for SELECT.
Returns:
  • Updated AST to include GROUP BY when DISTINCT field used.
Type
Object

(static) errorCheckSelectAST(ast)

Description:

  • Basic sanity check of AST for a SELECT statement.

Source:
Parameters:
Name Type Description
ast object

(static) loadSchema(tables)

Description:

  • Updates 'tables' with table column information.

Source:
Parameters:
Name Type Description
tables Map.<String, Table>