We are going to use our basic Spreadsheet Hello World script to demonstrate how variables can be used in a program.
Open your HelloWorld Sheet and then open the script editor from Tools -> Script editor
Replace the code with that below, the highlighted lines are new:
// Define our application
var myDoc = SpreadsheetApp.getActiveSpreadsheet();
var app = UiApp.createApplication().setTitle('Hello World');
// Create a set of variables
var string2 = 'Text 2';
var num1 = 10;
var num3 = 0;
// run the following loop
num1++;
}while (num1 < 5); // continue running the loop while num1 is less than 5
// while num2 is less than 10 then run the following loop
num2 = num2 * num2;
// run this look while the variable i is less than 5 and increment i by 1 each time the end of the loop is reached
num3++;
}
// create lables for our application
var helloWorldLabel = app.createLabel('My first Google Script UI');
var label3 = app.createLabel(string2);
var label5 = app.createLabel(num1);
var label7 = app.createLabel(num3);
app.add(helloWorldLabel);
app.add(label3);
app.add(label5);
app.add(label7);
myDoc.show(app);
do{ . } while(condition); With this loop all the commands contained within the curly brackets are run before the while test condition is evaluated. If the test condition evaluates to true then the loop is run again.
while(condition){.} With this loop the commands contained within the curly brackets are only run if the while condition evaluates to true.
With while and do while loops it is important to remember to influence the while condition in the loop, otherwise the loop will never exit.
for(var i=0; i<5; i++){.} runs a loop for a defined period. There are 3 items which must be set to create a for loop inside the round brackets as follows: test variable, test condition, increment. Typically a variable is defined at the start of the loop as done here, and incremented in steps of 1. To increment a variable by 1 rather than using i = i + 1; the shorthand of i++; can be used.
Test conditions can be:
<= less than or equal to
equal to (note the double sign, as = means assign to rather than compare)
> greater than
Dry running - when creating scripts with loops it is important to dry run each loop so that you know that the exit condition can be met. Dry running is the process of stepping through the script/loop using pencil and paper to keep track of what is happening to each variable and whether the test condition evaluates to true or false.
Comments - with this script we have introduced comments, this is the text prefixed by // and ignored when the script is run. Comments are important as they explain what the script is aiming to achieve and follows the coders train of thought. They are important not just so that others can follow your script, but also when dry running, debugging and returning after some time as they explain why the program was put together in a certain way. It should be possible for anyone to read just the comments and understand fully what the script is aiming to achieve at each stage.
