Home - Documentation for the library Google GSQL related to Google Apps Script

Getting started

There is multiple ways to set up the library.

1 - Include gSQL in your project

You can include directly the source code of Google Script SQL into your project as a new script file.

2 - Using it as an external library

If you want to use it as an external library, you need to create a new project and do exactly the same than the first way.

More info about external library

Source code there

3 - Minify the code and put it directly in your project

You can take the source code and minify it. Once minified, you just need to put it over your Google Script project file as shown in the picture.

Source code there

Creating a new instance of the object "gSQL"

Once you’ve set up the library, you need to create a new instance of the object. There is two way to do it.

Using minified code or gSQL in your project

var SQL = new gSQL();

Using external library

var SQL = new indentifier.gSQL;
//Where 'identifier' is the name of the identifier choosed in your external library

Example of usage

//Outside any function, as a global scope
var SQL = new gSQL();

//Or inside a function
function myfunction(){
var SQL = new gSQL();

Example of usage

//Outside any function, as a global scope
var SQL = new indentifier.gSQL;

//Or inside a function
function myFucntion(){
var SQL = new indentifier.gSQL;
}

Create database, tables and headers rows

CREATEDB() - Create Database

When you use "CREATEDB", it will create a Google Spreadsheet which will be used as a database

var SQL = new gSQL(); 
SQL.CREATEDB('YOUR_DB_NAME');
//CREATEDB accept only one parameter which is a string (Name of your DB);

INFOLDER() - Choose the folder destination for you database

This is optional. You can choose where your Spreadsheet (DB) has to be stored in your Google Drive.

var SQL = new gSQL();
SQL.CREATEDB('YOUR_DB_NAME').INFOLDER('YOUR_FOLDER_ID');

INFOLDER() accept only one parameter: the ID of the folder (a string) where you want to store the DB in.

To find your folder ID, you can refer to this article. If you choose to create your DB without INFOLDER() you can find your DB into your Google Spreadsheet App.

SETTABLES() - Create table(s)

When you are creating your(s) table(s), it will create one or multiple sheet in your Spreadsheet (DB).

var SQL = new gSQL();

//Creating only one table 
SQL.CREATEDB('YOUR_DB_NAME').SETTABLES('YOUR_TABLE_NAME');

//Creating multiple tables
SQL.CREATEDB('YOUR_DB_NAME').SETTABLES(['YOUR_TABLE_NAME_1','YOUR_TABLE_NAME_2',...]);

If you’re creating only one table : SETTABLES() accept one parameter which as a string (Name of the table).

If you’re creating mutliple table : SETTABLES() accept one parameter which is an array on string (Array of your tables’s name).

SETCOLUMNS() - Set the columns of your table(s)

As any SQL database, each table has to have some headers. You need to know that the first column will be automatically "ID" which is an auto increment column.
For the well working of the library, please DO NOT DELETE IT AT ANY CASE.

var SQL = new gSQL();
//Columns for only one table 
SQL.CREATEDB('YOUR_DB_NAME').SETTABLES('YOUR_TABLE_NAME_1').SETCOLUMNS(['HEADER_1','HEADER_2',...]);

//Columns for multiple tables
SQL.CREATEDB('YOUR_DB_NAME').SETTABLES(['YOUR_TABLE_NAME_1','YOUR_TABLE_NAME_2',...]).SETCOLUMNS([['HEADER_1','HEADER_2',...],['HEADER_1','HEADER_2',...]);

If you’re setting columns for one table : SETCOLUMNS() accept one parameter which is an array of your columns header name.
If you’re setting columns for mutliple table : SETCOLUMNS() accept one parameter which is a two dimensional array.

Example of usage

We have seen so far how to create and set our new database. Let's use the following database structure to use our library.

Then our code would be

 function myFunction(){ 
var SQL = new gSQL(); 
SQL.CREATEDB('Customers').SETTABLES(['customers_infos','orders_infos'])
.SETCOLUMNS([['name','surname','adress','phone','mail'],['order_number','total_amount','customer_id']]); 
}

Using Database and Tables

How to use it?

In the following section, we'll see how to read, update and delete data stores in our database. To be able to do it, each request need to start like this.

function myFunction(){ 
var SQL = new gSQL(); 
SQL.DB('YOUR_DB_ID').TABLE('YOUR_TABLE_ID') 
}

Where « YOUR_DB_ID » has to be replaced by the spreadsheet ID.

Example of usage

We'll from now using the database that has been created before.

function myFunction(){ 
var SQL = new gSQL; 
SQL.DB('1VcdfCyvyy8_RD67ji_GjtRXVUqIuX9abpO_oIo').TABLE('customers_infos') 
}

Where « 1VcdfCyvyy8_RD67ji_GjtRXVUqIuX9abpO_oIo » is my spreadsheet ID.

Insert data

INSERT() - Inserting data in the database

As any database, we want to be able to insert data in. The length of the data's array has to be the same than the table's columns length excluding the fist column which is "ID".

Insert one line of data

function myFunction() {
    var SQL = new gSQL();
    SQL.DB('MY_DB_ID').TABLE('MY_TABLE_NAME').INSERT(['value1', 'value2', ....]);
}

Insert multiple line of data

function myFunction() {
    var SQL = new gSQL();
    SQL.DB('MY_DB_ID').TABLE('MY_TABLE_NAME').INSERT([['LINE_1_VALUE1', 'LINE_2_VALUE2', ....],['LINE_2_VALUE1', 'LINE_2_VALUE2', ...]]);
}

Example of use

function myFunction() {
    var SQL = new gSQL();
    SQL.DB('1VcdfCyvyy8_RD67ji_GjtRXVUqIuX9abpO_oIo').TABLE('customers_infos').INSERT(['Lenon', 'John', '8 Hollywood Bd', '0445879563', 'john.lenon@imastar.com']);
}

Example of use

function myFunction(){
var SQL = new gSQL();
SQL.DB(&quot;1VcdfCyvyy8_RD67ji_GjtRXVUqIuX9abpO_oIo&quot;).TABLE(&quot;customers_infos&quot;).INSERT([[&quot;Brown&quot;,&quot;James&quot;,&quot;6 famous place&quot;, &quot;987654321&quot;,&quot;brown.james@imastar.com&quot;], [&quot;West&quot;, &quot;Kanye&quot;, &quot;Somewhere in LA&quot;, &quot;123456789&quot;,&quot;Kanye.west@music.com&quot;]]);
}

After executing the previous functions, see below the result.

Read the data

SELECT() - Get the data from the database

Please, don't forget ".getVal()" at the end of the request. If not, this will not work.

Select one column

function myFunction() {
var SQL = new gSQL();
var data = SQL.DB('MY_DB_ID').TABLE('My_TABLE_NAME').SELECT('column_Name').getVal();
}

Example of use

function myFunction() {
 var SQL = new gSQL();
 var oneColumn = SQL.DB('1VcdfCyvyy8_RD67ji_GjtRXVUqIuX9abpO_oIo').TABLE('customers_infos').SELECT('name').getVal();
 Logger.log(oneColumn); 
//Expected output : [['Lenon','Brown','West']]
  }

Select multiple columns

 function myFunction() {
var SQL = new gSQL();
var data = SQL.DB('MY_DB_ID').TABLE('My_TABLE_NAME').SELECT(['column_Name1', 'column_Name2', ...]).getVal();
}

Example of use

function myFunction(){
var SQL = new gSQL();
var multipleColumns = SQL.DB('1VcdfCyvyy8_RD67ji_GjtRXVUqIuX9abpO_oIo').TABLE('customers_infos').SELECT(['name','surname']).getVal();
Logger.log(multipleColumns);
//Expected output : [['Lenon','John'],['Brown','James],[...]]
}

Select all columns

function myFunction(){
var SQL = new gSQL();
var data = SQL.DB('MY_DB_ID').TABLE('My_TABLE_NAME').SELECT('ALL').getVal();
}

Example of use

function myFunction(){
var SQL = new gSQL();
var allData = SQL.DB('1VcdfCyvyy8_RD67ji_GjtRXVUqIuX9abpO_oIo').TABLE('customers_infos').SELECT('ALL').getVal();
Logger.log(allData);
//Exepected output : [[0,'Lenon','John','8 Hollywood Bd','0445879563','john.lenon@imastar.com'],[1,'Brown','James',...]...]
}

SELECT().WHERE() - Get the data where.

As in SQL, "WHERE" help you to be more specific in your request.

Note : Comparaison sign accepted are only the following : "=" - "!=" - ">" - "<" - "<=" - ">=" and needs to be inside quote.
The first argument need to be a column header. Be careful because it’s case sensitive. If your column header is « name », « Name » will not work. Same for the second argument, if the name is « West », « west » will not work either.

Select one column + "WHERE"

function myFunction(){
var SQL = new gSQL();
var data = SQL.DB('MY_DB_ID').TABLE('MY_TABLE_NAME').SELECT('column_name').WHERE('argument1','comparaison','argument2').getVal();
}

Example of use

function myFunction(){
var SQL = new gSQL();
var oneColumn = SQL.DB('1VcdfCyvyy8_RD67ji_GjtRXVUqIuX9abpO_oIo').TABLE('customers_infos').SELECT('name').WHERE('name','!=','West').getVal();
Logger.log(oneColumn);
//Expected output : [['Lenon','Brown']]
}

Select multiple columns + "WHERE"

function myFunction(){
var SQL = new gSQL();
var data = SQL.DB('MY_DB_ID').TABLE('My_TABLE_NAME').SELECT(['column_Name1', 'column_Name2',...]).WHERE('argument1','comparaison','argument2').getVal();
}

Example of use

function myFunction(){
var SQL = new gSQL();
var multipleColumns = SQL.DB('1VcdfCyvyy8_RD67ji_GjtRXVUqIuX9abpO_oIo').TABLE('customers_infos').SELECT(['name','surname']).WHERE('ID','<','2').getVal();
Logger.log(multipleColumns);
//Expected output : [['Lenon','John'],['Brown','James]]
}

Select all columns + "WHERE"

function myFunction(){
var SQL = new gSQL();
var data = SQL.DB('MY_DB_ID').TABLE('My_TABLE_NAME').SELECT('ALL').WHERE('argument1','comparaison','argument2').getVal();
}

Example of use

function myFunction(){
var SQL = new gSQL();
var allData = SQL.DB('1VcdfCyvyy8_RD67ji_GjtRXVUqIuX9abpO_oIo').TABLE('customers_infos').SELECT('ALL').WHERE('ID','=','0').getVal();
Logger.log(allData);
//Exepected output : [[0,'Lenon','John','8 Hollywood Bd','0445879563','john.lenon@imastar.com']]
}

SELECT().WHERE().AND() - Get the data where and.

Be more specific than "WHERE"

Select one column + "WHERE" + "AND"

function myFunction(){
var SQL = new gSQL();
var data = SQL.DB('MY_DB_ID').TABLE('MY_TABLE_NAME').SELECT('column_name').WHERE('argument1','comparaison','argument2').AND('argument1','comparaison','argument2').getVal();
}

Example of use

function myFunction(){
var SQL = new gSQL();
var oneColumn = SQL.DB('1VcdfCyvyy8_RD67ji_GjtRXVUqIuX9abpO_oIo').TABLE('customers_infos').SELECT('name').WHERE('name','!=','West').AND('ID','<','1').getVal();
Logger.log(oneColumn);
//Expected output : [['Lenon']]
}

Select multiple columns + "WHERE" + "AND"

function myFunction(){
var SQL = new gSQL();
var data = SQL.DB('MY_DB_ID').TABLE('My_TABLE_NAME').SELECT(['column_Name1', 'column_Name2',...]).WHERE('argument1','comparaison','argument2').AND('argument1','comparaison','argument2').getVal();
}

Example of use

function myFunction(){
var SQL = new gSQL();
var multipleColumns = SQL.DB('1VcdfCyvyy8_RD67ji_GjtRXVUqIuX9abpO_oIo').TABLE('customers_infos').SELECT(['name','surname']).WHERE('ID','<=','2').AND('name','!=','West').getVal();
Logger.log(multipleColumns);
//Expected output : [['Lenon','John'],['Brown','James]]
}

Select all columns + "WHERE" + "AND"

function myFunction(){
var SQL = new gSQL();
var data = SQL.DB('MY_DB_ID').TABLE('My_TABLE_NAME').SELECT('ALL').WHERE('argument1','comparaison','argument2').AND('argument1','comparaison','argument2').getVal();
}

Example of use

function myFunction(){
var SQL = new gSQL();
var allData = SQL.DB('1VcdfCyvyy8_RD67ji_GjtRXVUqIuX9abpO_oIo').TABLE('customers_infos').SELECT('ALL').WHERE('name','!=','West').AND('ID','=','0').getVal();
Logger.log(allData);
//Exepected output : [[0,'Lenon','John','8 Hollywood Bd','0445879563','john.lenon@imastar.com']]
}

SELECT().WHERE().OR()- Get the data where or.

Select data who match arguments of "WHERE" or arguments who match arguments of "OR"

Select one column + "WHERE" + "OR"

function myFunction(){
var SQL = new gSQL();
var data = SQL.DB('MY_DB_ID').TABLE('MY_TABLE_NAME').SELECT('column_name').WHERE('argument1','comparaison','argument2').OR('argument1','comparaison','argument2').getVal();
}

Example of use

function myFunction(){
var SQL = new gSQL();
var oneColumn = SQL.DB('1VcdfCyvyy8_RD67ji_GjtRXVUqIuX9abpO_oIo').TABLE('customers_infos').SELECT('name').WHERE('name','!=','West').AND('ID','<','1').getVal();
Logger.log(oneColumn);
//Expected output : [['Lenon']]
}

Select multiple columns + "WHERE" + "OR"

function myFunction(){
var SQL = new gSQL();
var data = SQL.DB('MY_DB_ID').TABLE('My_TABLE_NAME').SELECT(['column_Name1', 'column_Name2',...]).WHERE('argument1','comparaison','argument2').OR('argument1','comparaison','argument2').getVal();
}

Example of use

function myFunction(){
var SQL = new gSQL();
var multipleColumns = SQL.DB('1VcdfCyvyy8_RD67ji_GjtRXVUqIuX9abpO_oIo').TABLE('customers_infos').SELECT(['name','surname']).WHERE('name','=','Lenon').OR('name','=','Brown').getVal();
Logger.log(multipleColumns);
//Expected output : [['Lenon','John'],['Brown','James]]
}

Select all columns + "WHERE" + "OR"

function myFunction(){
var SQL = new gSQL();
var data = SQL.DB('MY_DB_ID').TABLE('My_TABLE_NAME').SELECT('ALL').WHERE('argument1','comparaison','argument2').OR('argument1','comparaison','argument2').getVal();
}

Example of use

function myFunction(){
var SQL = new gSQL();
var allData = SQL.DB('1VcdfCyvyy8_RD67ji_GjtRXVUqIuX9abpO_oIo').TABLE('customers_infos').SELECT('ALL').WHERE('mail','=','brown.james@imastar.com').OR('ID','<','2').getVal();
Logger.log(allData);
//Exepected output : [[0,'Lenon','John','8 Hollywood Bd','0445879563','john.lenon@imastar.com'],[1,'Brown','James',....]]
}

Update the database

INSERTCOL() - Insert new(s) column(s)

Inserting one or multiple news columns into the table

Insert one column

var SQL = new gSQL();
SQL.DB('MY_DB_ID').TABLE('MY_TABLE_NAME').INSERTCOL('NEW_COLUMN_NAME');

Insert multiple news columns

var SQL = new gSQL();
SQL.DB('MY_DB_ID').TABLE('MY_TABLE_NAME').INSERTCOL(['NEW_COLUMN_NAME1','NEW_COLUMN_NAME2',...]);

Example of use

function myFunction(){
var SQL = new gSQL();
SQL.DB('1VcdfCyvyy8_RD67ji_GjtRXVUqIuX9abpO_oIo').TABLE('customers_infos').INSERTCOL(['age','right']);
}

Then result

UPDATE().VALUES() - Update the database's data

Change one or multiple values in our database. Use "setVal()" at the end of your request. If not, this will not work.

Update one whole column

var SQL = new gSQL();
SQL.DB('DB_ID').TABLE('TABLE_NAME').UPDATE('COLUMN_NAME').VALUES('VALUE').setVal();

Fully update multiple columns

var SQL = new gSQL();
SQL.DB('DB_ID').TABLE('TABLE_NAME').UPDATE(['COLUMN_NAME1', 'COLUMN_NAME2').VALUES(['VALUE_FOR_COLUMN1','VALUE_FOR_COLUMN2').setVal();

Example of use

function myFunction(){
var SQL = new gSQL();
SQL.DB('1VcdfCyvyy8_RD67ji_GjtRXVUqIuX9abpO_oIo').TABLE('customers_infos').UPDATE('right').VALUES('0').setVal();
}

Then result

UPDATE().WHERE().VALUES() - Update the database's data specifically

Change one particular value into the database

function myFunction(){
var SQL = new gSQL();
SQL.DB('DB_ID').TABLE('TABLE_NAME').UPDATE('COLUMN_NAME').WHERE('condition1', 'comparator', 'condition2').VALUES('VALUE').setVal();
}

Example of use

function myFunction(){
var SQL = new gSQL();
//Update the Lenon's age
SQL.DB('1VcdfCyvyy8_RD67ji_GjtRXVUqIuX9abpO_oIo').TABLE('customers_infos').UPDATE('age').WHERE('name','=','Lenon').VALUES('12').setVal();
//Update the Brown's age
SQL.DB('1VcdfCyvyy8_RD67ji_GjtRXVUqIuX9abpO_oIo').TABLE('customers_infos').UPDATE('age').WHERE('name','=','Brown').VALUES('46').setVal();
//Update the West's age
SQL.DB('1VcdfCyvyy8_RD67ji_GjtRXVUqIuX9abpO_oIo').TABLE('customers_infos').UPDATE('age').WHERE('name','=','West').VALUES('40').setVal();
}

Then result

UPDATE().WHERE().AND().VALUES() - Update the database's data more specifically

You can use "WHERE()" & "AND()"

Example of use

function myFunction(){
var SQL = new gSQL();
SQL.DB('1VcdfCyvyy8_RD67ji_GjtRXVUqIuX9abpO_oIo').TABLE('customers_infos').UPDATE('right').WHERE('age','>','18').AND('age','<','42').VALUES('1').setVal();
}

Then result

Delete data and more

DELETEWHERE()- Delete specific lines of data

This function will help you to delete specific line in your table

function myFunction(){
var SQL = new gSQL();
SQL.DB('DB_ID').TABLE('TABLE_NAME').DELETEWHERE('condition1', 'comparator', 'condition2');
}

Example of use

function myFunction(){
var SQL = new gSQL();
SQL.DB('1VcdfCyvyy8_RD67ji_GjtRXVUqIuX9abpO_oIo').TABLE('customers_infos').DELETEWHERE('age','=','40');
}

Then result

TRUNCATE() - How to empty a table

It will delete the whole data in your table

function myFunction(){
var SQL = new gSQL();
SQL.DB('DB_ID').TABLE('TABLE_NAME').TRUNCATE();
}

Example of use

function myFunction(){
var SQL = new gSQL();
SQL.DB('1VcdfCyvyy8_RD67ji_GjtRXVUqIuX9abpO_oIo').TABLE('customers_infos').TRUNCATE();
}

Then result

DROPTABLE() - How to remove a table

It will delete the table selected

function myFunction(){
var SQL = new gSQL();
SQL.DB('DB_ID').TABLE('TABLE_NAME').TRUNCATE();
}

Example of use

function myFunction(){
var SQL = new gSQL();
SQL.DB('DB_ID').TABLE('TABLE_NAME').DROPTABLE();
}

DROPDB() - How to remove your database

It will delete the database selected

function myFunction(){
var SQL = new gSQL();
SQL.DB('DB_ID').DROPDB();
}

Example of use

function myFunction(){
var SQL = new gSQL();
SQL.DB('1VcdfCyvyy8_RD67ji_GjtRXVUqIuX9abpO_oIo').DROPDB();
}