GasSql

GasSql

Top level class used by Google Sheets custom function to process SELECT and return table data.

Constructor

new GasSql()

Source:

Methods

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

Description:

  • Run SELECT command statement and returns data in a table format (double array). The appropriate functions are selected to be run based on the format of the command line parameters.

Source:
Parameters:
Name Type Description
statement String
parms Array.<any>
Returns:
Type
Array.<Array.<any>>

(static) executeSqlv1(statement, parms) → {Array.<Array.<any>>}

Description:

  • Processes SQL SELECT using original command line syntax. This syntax does not update automatically if the data changes, so is not recommended anymore.

Source:
Parameters:
Name Type Description
statement String
parms Array.<any>
Returns:
Type
Array.<Array.<any>>

(static) executeSqlv2(statement, parms) → {Array.<Array.<any>>}

Description:

  • Process SQL SELECT using new command line syntax. Using this syntax ensures that the select data is refreshed if any of the selected table data changes - and is therefore the recommended usage.

Source:
Parameters:
Name Type Description
statement String
parms Array.<any>
Returns:
Type
Array.<Array.<any>>

(static) parseTableSettings(tableArr, statement, randomOrder) → {Array.<Array.<any>>}

Source:
Parameters:
Name Type Default Description
tableArr Array.<Array.<any>>

Referenced Table list. This is normally the second parameter in gsSQL() custom function.
It is a double array with first index for TABLE, and the second index are settings in the table. The setting index for each table is as follows:

  • 0 - Table Name.
  • 1 - Sheet Range.
  • 2 - Cache seconds.
  • 3 - First row contains title (for field name)
statement String

SQL SELECT statement. If no data specified in 'tableArr', the SELECT is parsed and each referenced table is assumed to be a TAB name on the sheet.
randomOrder Boolean true

Returned table list is randomized.
Returns:
  • Data from 'tableArr' PLUS any extracted tables referenced from SELECT statement. It is a double array with first index for TABLE, and the second index are settings in the table. The setting index for each table is as follows:
  • 0 - Table Name.
  • 1 - Sheet Range.
  • 2 - Cache seconds.
  • 3 - First row contains title (for field name)
Type
Array.<Array.<any>>