gsSQL
Use real SQL SELECT syntax to filter your Google Sheets data using a simple custom function.
AI Summary
gsSQL
Provides SQL SELECT syntax to filter/summarize Google Sheets data as tables.
Target Users
Beginner-intermediate Sheets users wanting SQL for JOINs/aggregations instead of cryptic QUERY.
Problems Solved
Sheets QUERY lacks easy JOINs, subqueries; needs standard SQL for complex filtering/summaries.
Script ID
- In GAS Editor: Click "Libraries +" → Paste into "Script ID" field → Click "Look up"
- "gsSQL" will appear in the search results
- Select the latest version (highest number) from "Version" dropdown
- Click "Add"
Tags
Main Features
1
Easy SQL
Intuitive English SQL syntax for SELECT, JOINs, GROUP BY vs. QUERY's cryptic formulas.
2
Full JOINs
Supports INNER/LEFT/RIGHT/FULL JOIN, UNION for multi-sheet combinations.
3
Bind Vars
Uses ?1 placeholders for dynamic params like dates, simplifying queries.
Examples
Main Functions
| Function | Description |
|---|---|
| gsSQL | Execute SQL SELECT on sheets |
| Sql.addTableData | Add table data to Sql |
| Sql.execute | Execute SQL statement |
Examples
Basic Sql Class Usage
/**
* Sqlクラス基本使用
* ・テーブルデータ追加: .addTableData("bookReturns", this.bookReturnsTable())
* ・タイトル出力有効: .enableColumnTitle(true)
* ・バインドパラメータ: .addBindParameter("05/01/2022")
*/
function exampleSqlBasic() {
let stmt = "select date, sum(quantity) from bookReturns where date >= ?1 and date <= ?2 group by date pivot customer_id";
let data = new Sql()
.addTableData("bookReturns", this.bookReturnsTable())
.enableColumnTitle(true)
.addBindParameter("05/01/2022")
.addBindParameter("05/04/2022")
.execute(stmt);
return data;
}Select2Object Usage
/**
* Select2Objectでオブジェクト取得
* ・テーブル定義: .addTableData("authors", this.authorsTable())
* ・SQL実行: .execute(stmt)
* ・結果形式: オブジェクト配列（table.columnで列参照）
*/
function exampleSelect2Object() {
let stmt = "select * from authors";
let data = new Select2Object()
.addTableData("authors", this.authorsTable())
.execute(stmt);
return data;
}Library gsSQL Usage
/**
* ライブラリ経由gsSQL使用
* ・ライブラリ呼び出し: gsSqlLibrary.gsSQL(sqlStatement, ...parms)
* ・テーブル指定: "authors", authors!A1:C
* ・タイトル出力: true
*/
function exampleLibraryGsSQL() {
return gsSqlLibrary.gsSQL("select * from authors", "authors", authors!A1:C, true);
}