Home

Welcome to the gsSQL wiki!

What is gsSQL - SUMMARY

• It is javascript that is intended to be used within the Google Apps Script environment.
• It can be used from your existing scripts to perform SQL SELECT queries in a format which is familiar to many people.
• The existing google sheet function QUERY, is only available as a sheet function. It cannot be used from the apps script.
• It can also be used as a custom google sheets function, just like the QUERY, but has more flexibility.
• The QUERY command references columns by their column letter. Not only is this hard to understand (I currently have this in my sheet, but without doing a deep dive, I really am not sure what is going on). i.e.

SELECT B, sum(H), sum(E) where F matches " & ArrayFormula("'"&textjoin(".*|",1,".*"& query(budgetIncomeCategories, "select K where K <> '' label K ''", -1) )&".*'") & " and B >= date '" & TEXT(DATEVALUE(startIncomeDate),"yyyy-mm-dd") & "' and B <= date '" & TEXT(DATEVALUE(endIncomeDate),"yyyy-mm-dd") & "' group by B pivot G label sum(E) 'Net', sum(H) 'Gross'

• Rewritten, looks like this:

gsSQL( "select transaction_date as 'Transaction Date', sum(gross) as Gross, sum(amount) as Net from mastertransactions where transaction_date >= ?1 and transaction_date <= ?2 and expense_category in (select income from budgetcategories where income <> '') group by transaction_date pivot account", {{"mastertransactions", "Master Transactions!$A$1:$I", 60}; {"budgetCategories", "budgetIncomeCategoriesTable", 3600}}, true, startIncomeDate, endIncomeDate)

• startIncomeDate, endIncomeDate - are named ranges that reference a date on the sheet.