You Might Want
To Start Here
AI Generation of Named Ranges & a Nested IF Statement

To use AI to create or refine Google scripts there are a set of specific steps to follow. This page contains a an illustrated example of such as routine which involves: 

• Writing the instruction to the AI

• Generating from the AI the needed Google Script

• Testing. (Possibly "rinse and repeat".)

• Integrating the generated code into a cell or the Script Editor

• Using the script / code in your spreadsheet

Instructions: the Design Phase

The hardest part: You have to think through what you want in the way of an answer (in this case a script) from the AI. This includes, for example, identifying the sheet, column, line or cell where you want the action to occur. Essentially, you need to design either in your mind or on paper and then feed the instruction to your AI assistant.

The instructions to the AI for the following simple example have two parts:
1. Defining the names for the columns involved (Google's Named Ranges) so that the instruction
    themselves will be more readable.

2. The IF and IF ELSE statements the truth or falsity of which will determine if a particular cell will have
    content and, if so, how that content will be formatted.

(This particular example evaluates data in a particular row related to buying or selling or holding a particular stock and at what price. The resulting formatted string will then be copied into a separate application.)

After a bit of trial and error (i.e., "rinse and repeat"), it was found that the instruction shown below successfully generated a conditional statement that worked.

The Instruction
(to be copied into the AI's 'Ask Me Anything' Textbox)

/**
A few things to note about the instructions below:

* These words within a comment block and everything below can be copied into the AI's "Ask Me Anything" box. Comments such as this will be incorporated in the answer, which for sure is a convenience.
* Instruction #1 sets up eight named ranges. These are sections of eight columns. It so happens that these cover rows 3-22 but they could be anything.
* Named ranges are available to any sheet in your spreadsheet. The name is universally available for your use in scripts or functions. They can be created individually or, in this case, in the Google Script Editor (more on that below).
* The specified sheet name here is 'Setups' … if you only have one sheet in your spreadsheet, the default name is 'Sheet1'
* Capitalization doesn't matter and it typically doesn't matter how you word things as long as your wording is precise, makes common sense (to the AI) and is pretty much consistent. For example, this could as easily be written as a series of "IFs" instead of "ELSE IFs."

* Instruction #2 creates a nested IF statement designed to be copied into a sheet cell. Since no example of this working is provided, here is a written explanation of the Open SHORT position instruction: If the Direction column of the particular row contains the word 'Short' and that rows Instruction cell is empty combine the following elements into a single line:
* Sell
* [Quantity] (whatever the quantity for that row is)
* a space
* Stock Symbol
* the text " @" without the quotation marks
* [ActionPrice] (whatever the number in the ActionPrice column for that row is)
* the text " LMT"
If this test is true the following line would be inserted in the cell (specific values added):
SELL 100 IBM @$200.01 LMT
*/

// Here are the actual instructions to the AI:

For a Google spreadsheet do two things:



1. Set up the following named ranges:

'Symbol' for Setups!C3:C22

'Direction' for Setups!D3:D22

'ActionPrice' for Setups!E3:E22

'Instruction' for Setups!F3:F22

'Quantity' for Setups!G3:G22

'Orders' for Setups!H3:H22

'ActualShares' for Setups!L3:L22

'ExtractOrder' for Setups!T3:T22

2. Create an instruction for use in a Google Spreadsheet cell which follows these rules:



// First day hold
IF Instruction contains "Hold" do nothing.

// Open SHORT position

ELSE IF Direction contains "Short" AND Instruction is empty COMBINE “SELL " & Quantity & " " & Symbol & " @" & ActionPrice &" LMT"


// Close SHORT position

ELSE IF Direction contains "Short" AND Instruction contains "Cover on market open" COMBINE “BUY +” & ActualShares & " " & Symbol & " MKT"

// Adjust SHORT position

ELSE IF Direction contains "Short" AND Instruction contains a value COMBINE "BUY +" & ActualShares & " " & Symbol & " " & "@" & ExtractOrder & " LMT"


Open LONG position
* ELSE IF Direction contains "Long" and Instruction is empty COMBINE “BUY +" & Quantity & " " & Symbol & " @" & ActionPrice &" LMT"


CLOSE LONG position
ELSE IF Direction contains "Long" AND Instruction contains "Exit on market open" COMBINE “SELL -” & ActualShares & " " & Symbol & " MKT"

// Adjust LONG position

ELSE IF Direction contains "Long" and Instruction contains a value COMBINE "SELL -" & ActualShares & " " & Symbol & " " & "@" & ExtractOrder & " LMT"

AI Output #1

As there were two parts to the instructions the AI responded with two sections of code, both below.
The first sets up the specified named ranges which make it easier to write the somewhat lengthy IF statement.  Helpfully, the AI also provides instructions on how to automatically create the named ranges:

1. In your Google spreadsheet go to the Extensions menu and select Apps Script
2. Paste the script into the Apps Script editor
3. Save the project  (using available option at top of editor)
4. Run the setupNamedRanges( ) function

See also Google Apps Script Quickstart

AI Output #2

The second output is the nested IF statement. While there's nothing particularly complicated about the logic (with or without named ranges), creating nested IF statements is often frustrating and time-consuming because it's so easy to leave out OR add an extra semi-colon &/or parenthesis, which can be difficult to find and correct. With AI, the logic of your instructions may not be correct but it will never generate an IF statement containing typos or other dumb errors.

The following IF statement is designed to be pasted into cells in a column which will contain instructions to a 3rd party app, as mentioned above. For an example of editing a text string programmatically using AI please see Editing a Text String.

Conclusion

The example above describes 1) the process of using AI to generate both App Script and function code, 2) shows how to create named ranges through the App Script editor and 3) shows how AI can successfully output nested IF statements for use in your spreadsheets.