Not working when using optional parameters

[mdininno]

Hi everyone. I've been working with Google Sheets since a few months, building a database for a volunteer organization, and I came across the Chris's "gsSQL" project, which I find very interesting and useful. So I installed it by copying "gssql.js" into an AppsScript and tried using your custom function. Everything works fine if I only use the required "SelectSqlStatement" parameter on tables that have their own dedicated sheet, but if I use the "TableDefinitions" parameter, I get "#ERROR!". I tried installing it as a library, but I get the same behavior. I then tried to replicate the exact same situation in the Chris's test file, which I asked to be shared. It works even though, when editing the formula, the part defining the cell range in the "TableDefinitions" is underlined (as if there were a hyperlink) with a tooltip showing "gsSQL only accepts 2 arguments, but this is argument number 3!" Despite having scrupulously followed the instructions in the readme, I suspect I'm missing something. Can anyone help me? Thanks.

[demmings]

Hi mdininno,

Could you paste in the exact statement you are using so I can have a look at it?

For table definitions I have two distinct syntaxes. My original way that supports a very large table size but does NOT re-rerun the SQL if any table data changes and my newer syntax that will cause the sql to re-run when table data changes BUT only supports a limited table size (a google sheets limitation)

The syntax for the tables is that you need to define every table you will reference in your SELECT statement. So if you have two tables using original syntax it is (and you need quotes around the ranges). The 60 and 3600 are now obsolete but still needed. They define how long the data is to be cached.

{{"mastertransactions", "Master Transactions!$A$1:$I", 60};{"budgetCategories","budgetIncomeCategories", 3600}}

The newer syntax for the table definitions would be:
"mastertransactions", 'Master Transactions'!$A$1:$I, "budgetCategories", budgetIncomeCategories

where 'budgetIncomeCategories' is a named range.

The table definitions are terminated with the true/false parameter to indicate if column titles are included.

After that is all of the bind variables used.. They are replaced in the SELECT statement with ?1, ?2 for as many as needed.

The onscreen help indicating it only takes two parameter is misleading. My javascript syntax needs to hande a variable number of command line parameters like this:

function gsSQL(statement, ...parms) {}

So google sheets thinks it is only two parameters where parms actually expands to as many parameters that were used.

Chris.

[mdininno]

Thank you so much Chris for your timely interest in my issue. For the test I used your 'Books' and 'Authors' test tables: in an additional sheet I inserted the following statement:

=gsSQL("SELECT books.id, books.title, authors.first_name, authors.last_name FROM books INNER JOIN authors ON books.author_id = authors.id ORDER BY books.id", "books", Books!A1:F10, "authors", Authors!A1:C6, true)

I confirm that it works within your gsSqlTest file; the exact same statement in a test file of mine that contains the same tables, gives "ERROR!" as a result, with a comment "ERROR!" and a tooltip "Formula analysis error".

Below is the sharing link of my test file:
https://docs.google.com/spreadsheets/d/1HtS4P_8d3k6sl8w0w8n1_3TN3WnriFX3bfd2hiurBdw/edit?usp=sharing

Best regards. Mino.

[demmings]

The 'Formula Parse Error' is being thrown by Google Sheets and not by the gsSQL() function. There must be some character I cannot see that is causing the issue.

[demmings]

I can cause the error by using single quotes around the strings. Try inserting double quotes around the SELECT and the table names.

The gsSQL() statement in your comment above has double quotes, but the sheet that you shared is using single quotes.

[demmings]

I tried the statement in your test sheet and it appears that the parameter separator in Italy is the semi-colon, where it is the comma in Canada/North America. After changing to that separator, the function works as expected.

[mdininno]

I got it while you where working on it! Thank you very much Chris. One more question. I suppose that I can't use the names automatically generated by Google Sheets when converting a range in a table, like "Table1[[#HEADERS],[#DATA]]", cant' I?

[demmings]

Hi, To be honest, I have not used tables - so I am not up to date if that can be made to work. You can of course use name ranges or open ended ranges like =gssql("select * from master_transactions limit 100", "master_transactions", 'Master Transactions'!A1:F) so you can add records and not have to adjust your select. You can also use importrange to specify the table data.

…

On Sun, Mar 1, 2026 at 12:20 PM mdininno ***@***.***> wrote: I got it while you where working on it! Thank you very much Chris. One more question. I suppose that I can't use the names automatically generated by Google Sheets when converting a range in a table, like "Table1[[#HEADERS],[#DATA]]", cant' I? — Reply to this email directly, view it on GitHub <#70 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AU5JPSHOM5P34WYATUJ566L4ORWOHAVCNFSM6AAAAACWCXGQEOVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTKOJWGM2DANY> . You are receiving this because you modified the open/close state.Message ID: ***@***.***>

[mdininno]

Thanks a lot Chris.

[demmings]

No problem. We are coming to Sicily and Calabria in September, so its the least I can do.

[mdininno]

Enjoy your journey then. If you can, have a look to the "Salento" in Apulia: the sea is wonderful (I live in Taranto).

[demmings]

Good advice but ....we just got back at the end of September from visiting Matera, Lecce, Monopoli, Otranto, Brindisi, Ostuni, Loco Rotondo, Polagno a Mare, and old Bari. By far the best trip to Italy ever.

…

On Sun, Mar 1, 2026 at 12:38 PM mdininno ***@***.***> wrote: Enjoy your journey then. If you can, have a look to the "Salento" in Apulia: the sea is wonderful (I live in Taranto). — Reply to this email directly, view it on GitHub <#70 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AU5JPSBKK6EPQOZ5IEQGGDL4ORYRXAVCNFSM6AAAAACWCXGQEOVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTKOJWGM2DOMA> . You are receiving this because you modified the open/close state.Message ID: ***@***.***>

[mdininno]

Very good choises. Send me an email if you would need some help.