Google Sheets + ChatGPT

[ScottC]

ChatGPT helped me identify an almost invisible bug in a query that I would have gone crazy trying to find otherwise.

A couple of weeks ago, I posted an Apps Script that creates a custom function that displays a Table of Contents with hyperlinks to all tabs in a workbook.

Add Table of Contents with hyperlinks to all sheets in workbook. Option to exclude Hidden sheets

I was going to write a script to create a Table of Contents tab in my Tiller workbook because I’m getting a lot of tabs – some hidden and some visible. I was surprised to find a script named “CreateListOfSheets” already in the Apps Script Editor. I assume it comes automatically with Tiller Money because I don’t remember adding it. The script creates a custom function ‘Sheetnames’ that creates a list of sheet names in the workbook. To try this, create a new tab and enter this formula in any cell…

Even though I copied the code directly from the Script Editor in Google Sheets, people reported that it wasn’t working. They had to replace double quotes with single quotes in two lines of the code I posted. I checked and double-checked to make sure I was copying the App Script correctly. I couldn’t figure out what was wrong.

In a totally unrelated matter, I wrote a query to find data between two dates. Here’s the query:

=QUERY($A:$C, “Select * Where C>= date ’” &Text(H3,’”YYYY-MM-DD”)&”’ and C <= date '"&Text (I3, "YYYY-MM-DD”)&”’”,1)

I asked ChatGPT if the syntax was correct. It said that it wasn’t and it showed me the correct syntax which looked exactly like mine. I asked it to explain. Here’s what it said:

• The quotes around the SELECT and WHERE keywords are double quotes (") instead of curved quotes (“ and ”).
• There is a missing space between the date keyword and the single quotes (').
• The format of the TEXT function is corrected to use double quotes (") instead of curved quotes (“ and ”)

Looking at the code on the screen, it was impossible to see the differences between the double quotes. I still can’t but apparently, they’re different. Somehow, the double quotes in the script that worked in Google Sheets were converted to curly quotes when I copied and pasted into my post about creating a Table of Contents with hyperlinks.

Perhaps the quotes wouldn’t have been converted if I pasted into a Blockquote. ?? If anyone knows, I’d like to know how to paste code copied from Apps Script without any conversions.

In the meantime, I think I’ll check scripts using ChatGPT to identify syntax conversions and correct them manually when I post.