Skip to content

README.md

Latest commit

 

History

History
348 lines (328 loc) · 9.86 KB

README.md

File metadata and controls

348 lines (328 loc) · 9.86 KB
Raw

Google Apps Script SQL Abstract

Library helps working on Google Spreadsheet as on database. Inspiration was Perl module SQL::Abstract.

  • First row in sheet is reserved for columns headers.
  • The query is not text but Java Script structure.
  • Loading data from sheet by method getValues() (returns objects ex. if value in cell is date, returns Date object, etc.).
  • Serializing and deserializing data.

SqlAbstract object

Pure object.

var sql = new SqlAbstract();

All sheets from selected spreadsheets will be available as tables (table name is sheet name).

var sql = new SqlAbstract({
  spreadsheets: [SpreadsheetUrl]
});

Only selected tables from given spreadsheet will be tables.
Options:

  • as - given table name for selected sheet
  • serializer - data in each column could be write and read in different way, ex. as JSON
  • headerRowNr - the number of the row containing column headers
var sql = new SqlAbstract({
  spreadsheets: [
    {
      url: SpreadsheetUrl,
      tables: {
        'Sheet Name': {
          as: 'Table name',
          serializer: {
            'Column 1': { // for JSON
              get: JSON.parse,
              set: JSON.stringify
            },
            'Column 2': { // for date
              get: function (x) {
                return Utilities.formatDate(x, Session.getScriptTimeZone(), "yyyy-MM-dd HH:mm:ss")
              }
            }
          }
        }
      }
    }
  ]
});

Methods

  • createDB - creates sheets with given columns and assign them to sql object as table with given name.

    sql.createDB({
  spreadsheet: SpreadsheetUrl,
  tables: [
    {
      name: 'Sheet name',
      as: 'Table name',
      columns: ['C1', 'C2', 'C3']
    }
  ]
})

  • createTable - create table in given sheet and assign it to sql object as table with given name.

    sql.createTable({
  spreadsheet: SpreadsheetUrl,
  table: {
    name: 'Sheet name',
    as: 'Table name',
    columns: ['C1', 'C2', 'C3']
  }
});

  • dropTable - delete sheet from spreadsheet.

    sql.dropTable({table: 'Table1'});

  • initTable - insert initial data to selected table.

    sql.initTable({table: 'Table1', values: [[1, 'init', 1],[1, 'init', 1]]});

  • select - select data from table.

    let out = sql.select({table: 'Table1', where:{'C2': 'init'}, orderBy: ['C3','C4'], groupBy: ['C1']})

    Parameters:

    • sheet - sheet handler (required if "table" is not given)
    • as - declared table name
    • headerRowNr - the number of the row containing column headers
    • table - name of table, sheet name or declared using 'as' table name (required if "sheet" is not given)
    • where - where clause, Java Script structure (describled in next section) or function ex.: 
      function(row){return row['K1'] == 'd15'}
    • groupBy - list of columns for grouping ex. ['K1','K2']. First groups by column 'K1' next 'K2' returns structure: 
      {'K1_values': {'K2_values': [...rows...]}}
    • orderBy - sorts output by given list of columns ex. {'K1': 'asc', 'K2': 'desc'}
    • columns - limits colums, if not given returns all columns

    Return
    Array of row objects.

    Row object methods:

    • get([columnName]) - when column name is given, returns data from given column in row. Otherwise returns all row as hash, where keys are columns headers.
    • set({columnName: value}) - set value for given columns. You can set more then one value.
    • values() - returns row as hash, where keys are columns headers (the same as get())

    Row object properties:

    • data - row as array
    • row - row as array (the same as data)
    • rowNr - number of current row in sheet (started from 1)
    • id - number of current row in output array (started from 0)
    • columnIdByName - sheet headers mapping
    • headers: sheet headers as array
    • sheet: sheet handler

  • insert

    sql.insert({table: 'Table1', values: {'C1': 1, 'C2': 'insert', 'C3': 2}})

sql.insert({table: 'Update', values:[{'K1': 'a', 'K2': 'Null'},{'K1': 'b', 'K2': 'Null'}]});

sql.insert({table: 'Table1', values: [[1, 'insert multiple', 3],[1, 'insert multiple', 3]]});

  • update

        sql.update({table: 'Update', where:{'K1': 'a'}, set:{'K2': 'Test1'}});

  • updateRow

        sql.updateRow({table: 'Update', rowNr: 4, values: {'K2': 'updateRow'}})

  • getTable

      var table = sql.getTable({table: 'Update'});

  table.sheet.getName();

Where clause

  1. Simple equate.
    Query: K2 = 15 AND K3 = 'c'
    Where: {'K2': 15, 'K3': 'c'}
  2. Operators: '==', '!=', '>’, ‘<’, ‘>=’, ‘<=’, ‘~’.
    • '>='
      Query: K2 >= 12
      Where: {'K2': {'>=': 12}}
    • ‘!=’
      Query: K2 <> 12
      Where: {'K2': {'!=': 12}}
    • ‘~’
      Query: K2 LIKE '%Test%'
      Where: {'K5': {'~': 'Test'}}
  3. Value in range.
    Query: K3 IN ('a','c')
    Where: {'K3': ['a','c']}
  4. Value not in range.
    Query: K3 NOT IN ('a','c')
    Where: {'K3': {'!=': ['a','c']}}
  5. Logical operators for single column.
    • AND
      Query: K2 > 11 AND K2 <= 14
      Where: {'K2': {'>': 11, '<=': 14}}
    • OR
      Query: K2 = 12 OR K2 > 14
      Where: {'K2': [{'==': 12},{'>': 14}]}
  6. Logical operators in where clause.
    • AND
      Query: K2 >= 12 AND K3 IN ('b','c')
      Where: {'K2': {'>=': 12}, 'K3': ['b', 'c']}
      or with '-and' operator
      Where: {'-and': {'K2': {'>=': 12}, 'K3': ['b', 'c']}}
    • OR
      Query: K2 <= 11 OR K3 IN ('b','c')
      Where: [{'K2': {'<=': 11}}, {'K3': ['b', 'c']}]
      or with '-or' operator
      Where: {'-or': {'K2': {'<=': 11}, 'K3': ['b', 'c']}}
    • Mixed
      Query: K2 < 13 OR (K3 IN ('b','c') AND K4 > 7)
      Where: [{'K2': {'<': 13}}, {'K3': ['b', 'c'], 'K4': {'>': 7}}}]
      or
      Where: {'-or': {'K2': {'<': 13}, '-and': {'K3': ['b', 'c'], 'K4': {'>': 7}}}}
    • Mixed AND with OR inside
      Query: K1 = 1 AND (K2 = 1 OR K3 = 1)
      Where: {'K1': 1, '-or': {'K2': 1, 'K3': 1}}
  7. Regular expressions (mysql LIKE statement).
    Query: K2 LIKE '%Test%'
    Where (given regexp): {'K5': /Test/}
    Where (text is converted to regexp): {'K5': ‘Test’}
  8. Function as condition.
    Query: K2 > 11 AND K2 <= 14
    Where: {'K2': function(x){return x > 11 && x <= 14}}

Cache

Second and next select from the same table gets data from cache, so it's faster.

Single functions

  1. selectData
    Returns selected rows as array of objects.
    selectData(sheet, where, [groupBy], [orderBy], [columns])
  2. selectDataRows
    Returns selected rows as two dimensional array.
    selectDataRows(sheet, where, [groupBy], [orderBy], [columns])
  3. updateRow
    Updates values for selected columns in given row number (started from 1).
    updateRow(sheet, 2, {'K2': 'x', 'K6': 'y'})
  4. appendRow
    Adds given data for selected columns as a new row.
    appendRow(sheet, {'K1': 9, 'K2': 'x', 'K6': 'a'})

Duplicated header name handling

When in columns headers there is duplicated column name, it's name should be called with adding it's occurrence number after the name ex.:
Headers: 'K1', 'K2', 'K3', 'K2', 'K2'.
Second 'K2' is called 'K2_2', third 'K2_3' and so on.
Ex. the where clause, searching on second column 'K2': {'K2_2': 10}

Performance

Depends on Google Script environment performance. They vary depending on the time of run. Ex. measured times (table with 10000 rows):

  • getValues: 3549ms 
    var testSheet = ss.getSheetByName('Big Table');
testSheet.getDataRange().getValues();
  • selectData without condition: 3211ms 
    var out = selectData(testSheet);
  • selectData with condition: 4528ms 
    var out = selectData(testSheet, {'K1': 'Banana'});

Example

More examples there are in file Tests.gs.

Example sheet

K1 K2 K3 K4 K5 K6 K3
1 10 a 1 a 0 1
2 11 b 5 aTest 0 1
3 12 b 3 a 0 1
4 13 a 7 abaTestasg 2 10
5 14 a 7 a 1 1
6 15 c 9 a 0 1
7 16 c 2 a 0 1 
var spreadsheetUrl = 'https://docs.google.com/spreadsheets/d/1234';
var sql = new SqlAbstract({spreadsheets: [spreadsheetUrl]});
// table is sheet name
var out = sql.select({table: 'Sheet1', where: [{'K1': '2'}, {'K2': {'>': 14}, 'K3': 'c'}]});

for (var m in out){
  Logger.log(m);
  // Get value for column name ‘K1’
  Logger.log(out[m].get(‘K1’));
  // Set value in column name ‘K3’
  out[m].set({‘K3’: 5});
}

or

var spreadsheetUrl = 'https://docs.google.com/spreadsheets/d/1234';
var ss = SpreadsheetApp.openByUrl(spreadsheetUrl);
var sheet = ss.getSheetByName('Data');
var out = selectData(sheet, [{'K1': '2'}, {'K2': {'>': 14}, 'K3': 'c'}]);

for (var m in out){
  Logger.log(m);
  // Get value for column name ‘K1’
  Logger.log(out[m].get(‘K1’));
  // Set value in column name ‘K3’
  out[m].set({‘K3’: 5});
}