Week in Review - issue 1

Two thoughts this week - perseverance and focus - which are nicely encapsulated by these two quotes:

"I don't think there is any quality so essential to success as the quality of perseverance. It overcomes almost everything" – JD Rockefeller

“Focus is saying no to 1,000 good ideas.” — Steve Jobs

Interesting article regarding pricing inefficiencies on San Fransisco's Bay Area Rapid Transit, from a Twitter Data Scientist - read it here.

An in-depth article on getting into data science from Zipfian Academy, a San Francisco based data science education outfit - read it here.

Javascript coding articles for the weekend work - I want to add interactive % labels to a bar chart I'm building for a client project:
Adding labels to charts with script in Google Spreadsheets
JS Fiddle - a really smart looking browser based coding environment

Always be Coding - a great article on being a good developer from Medium (this is way ahead of where I am but is still very inspiring):
Always be coding

Several interesting posts on data driven projects, again all from Medium (which I'm really getting a lot of value from at the moment - it's a far superior reading experience that many other tech sites out there):
The Optimal post is 7 minutes from the Data Science team at Medium
Defining your target market and finding your niche
Interesting look at thought process behind building a SaaS business

Adding a time and date stamp to the contacts database project

Just a small update to my contacts database app - I want to add a time and date stamp to each new entry that I add to the spreadsheet. This is achieved easily using the Javascript "new Date()" object, formatting it to look the way I want using "Utilities.formatDate" and then adding into my new row in the "apendRow" method.

The code to achieve this is as follows:

var d = Utilities.formatDate(new Date(),"EST","EEE', 'MMM dd', 'yyyy' at 'HH:mm:ss");

EST stands for Eastern Standard Time (I'm based on E coast of the US)
EEE gives the abbreviated 3 letter day of the week (e.g. Mon)
MMM gives the abbreviated 3 letter month of the year (e.g. Mar)
Then I modify my appendRow method to include the date variable "d" in the output. The code is as follows:

//apend row
sheet.appendRow([lastCell+1,d,textBoxValue1,textBoxValue2,textBoxValue3]);

So my contacts database now automatically adds a date/time stamp to each new entry I add, and looks like this:

References for this entry:
Stack Overflow on Javascript Date object
Stack Overflow on formatting Date object
Official Google documentation on formatDate

Adding rows of data through a GUI using Google Scripts

I want to create a simple form so that the user can enter some basic details into the spreadsheet. Of course, the easy way to do this would be to have the user just type them direct into the spreadsheet but where's the fun in that! Whether this app ever ends up being useful or not (or even ends up looking anything like the vision I have for it in my head) is another matter but I'm learning as I try to build it out.

The Google tutorial that I used for this post is found here.

So, in this post I'm going to setup a GUI interface that will prompt the user for inputs and then transfer these to the spreadsheet on the next blank line. The steps are as follows:

1. Declare the GUI application
2. Create the labels (e.g. "Enter Name") using the .createLabel() method
3. Create the empty text boxes for the user inputs, using the .createTextBox() method
4. Create a layout grid for these labels and boxes, using the .createGrid() method
5. Assign the items to the grid using the .setWidget() method
6. Create a "Submit" button that will do something once the user has entered data
7. Create the panel component that will display the labels, text boxes and button
8. Add the grid to the panel (which by definition adds the items as we've already added these to the grid in step 5)
9. Add the button to the panel
10. Add the panel to the GUI application
11. Show the application

The code to do this is as follows, with the steps added as comments:

function gridUI() {
  // Step 1 - create the GUI application
  var testapp = UiApp.createApplication().setTitle('New Contact');
  
  //Step 2 - setup the three labels
  var label1 = testapp.createLabel('Enter name: ');
  var label2 = testapp.createLabel('Enter company: ');
  var label3 = testapp.createLabel('Enter web url: ');
  
  //Step 3 - setup the three text input boxes
  var textBox1 = testapp.createTextBox().setName('TextBoxName').setId('TextBoxName');
  var textBox2 = testapp.createTextBox().setName('TextBoxCo').setId('TextBoxCo');
  var textBox3 = testapp.createTextBox().setName('TextBoxURL').setId('TextBoxURL');
  
  //Step 4 - create and lay out the form grid
  var mygrid = testapp.createGrid(3,2);

  //Step 5 - add labels and text boxes to grid
  mygrid.setWidget(0,0,label1)
    .setWidget(0,1,textBox1)
    .setWidget(1,0,label2)
    .setWidget(1,1,textBox2)
    .setWidget(2,0,label3)
    .setWidget(2,1,textBox3);
  
  //Step 6 - create the form buttons
  var submitButton = testapp.createButton('Submit');
  
  //Step 7 - create the panel
  var panel = testapp.createVerticalPanel();
  
  //Step 8 - add the grid widget to the panel
  panel.add(mygrid);

  //Step 9 - add the button to the panel
  panel.add(submitButton);
  
  //Step 10 - add the panel to the GUI application
  testapp.add(panel);
  
  //Step 11 - display the panel
  var doc = SpreadsheetApp.getActive();
  doc.show(testapp);
}

Next step is to add an event handler to take the user entered data and drop this into the next blank row of the spreadsheet. There is a convenient method called "appendRow()" that I can use to achieve this relatively easily. Before getting to that however, I need to add an event handler to the submit button in the data entry form created above, then create a function that this event handler will call when the button is pressed, the ultimate outcome of which is to append the data.

The code to add the event handler is as follows - it is added between steps 10 and 11 above:

  //create the event handler for when the submit button is pressed
  var clickHandler = testapp.createServerHandler("respondToSubmit");
  submitButton.addClickHandler(clickHandler);
  clickHandler.addCallbackElement(panel);

The function code is then, placed as a new function under the closing "}" above:

//function to respond to the submit button
function respondToSubmit(e) {
  var testapp = UiApp.getActiveApplication();
  
  //get values of the three text boxes
  var textBoxValue1 = e.parameter.TextBoxName;
  var textBoxValue2 = e.parameter.TextBoxCo;
  var textBoxValue3 = e.parameter.TextBoxURL;
  
  //determine the next available row to input the new line of data
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow() + 1;
  
  //apend row
  sheet.appendRow([lastRow,textBoxValue1,textBoxValue2,textBoxValue3]);
  
  //close the dialog box
  return testapp.close();
}

When we press run, the GUI should look something like the image below. Entering data and then pressing submit should add that data in the last line of the spreadsheet, magic!

Creating drop down lists with javascript in a Google Spreadsheet

This function builds on my previous post about passing a range of data from a google spreadsheet into an array. Taking it one step further, I now want to display (some of) that data in a drop down list box that allows the user select a record so we can then perform actions on it e.g. displaying the data.

The starting point for this exercise is the Google Developer site, specifically this list box widget code.

The code creates a "panel" that sits above the spreadsheet - this is a graphical interface that will display text, buttons to etc. (a message box or input box is a simple example of a panel). Then I create a list box, to which I add the list items (these will come from the array). I add that list box to the panel, then add a button to the panel as well. The button has a click handler associated with it - this tells it what to do on the event of a click. In this case it'll call on another function that I'll define which simply displays the entry that the user has selected in the list box.

The data for this example is simple enough:

The script from my previous blog post adds this to an array so here I want to add code to the end of that script - the snippets below are not standalone, they will only work when added to the code from my previous example. Before the final closing "}", I add this code to declare the variables needed for the graphical panel and the list box:

  var app = UiApp.createApplication().setTitle('Search');
  var panel = app.createVerticalPanel();
  var lb = app.createListBox(true).setId('myId').setName('myLb');

Now I need to add some items to the list box. I want to do this using the variables I have stored in my array. Recall, the array is sized to hold all of the line items from my data range, which contains "N" rows in this case (where N is defined to equal the last row count of the data range). So I use a for loop to cycle through the values in the array and add each one to the list box (the array holds N rows, which are each in turn an array holding values "index", "name1", "name2" and "web" (see header row of image above)). In this case I've chosen to add name1 and name2 to my list box. The code is as follows:

  //create the list box items
  lb.setVisibleItemCount(4); // how many items show at once
  for (var k = 1; k < N; k++) {
    // add items to ListBox
    var listBoxString = mainObject[k].name1 + " " + mainObject[k].name2;
    lb.addItem(listBoxString);
  }

Finally, I want to add a button and event handler so that I can do something once the user has selected an entry in the list box. Several things happen in this snippet of code: I declare a button labeled "Select"; I declare a variable called "handler" that calls on the function called "select" and passes the data from the panel (i.e. the list box); and finally, I associate this handler to the button. I don't pretend to fully understand this yet but there are more details here on the Google Developer website.

  //add button and click handler
  var button = app.createButton('Select');
  var handler = app.createServerClickHandler('select').addCallbackElement(panel);
  button.addClickHandler(handler);

The final piece of the code is required to add the list box and the button to the panel, then add the panel and then display the panel.

  panel.add(lb);  // add list box to panel
  panel.add(button); // add button to panel
  app.add(panel);  // add panel to the app
  spreadsheet.show(app); // display the app i.e. the panel with list box

Adding this code to the array code from my earlier blog post should give the following output:

Cool huh! This took me quite a while to work out, progressing by adding static items to the list box, then data in static cells in my worksheet, then data from a static array I created, and finally the data from my dynamic array. I was stoked to get it working. The final piece of code is the "select" function that the button handler calls on - it's a simple display box to show the entry the user has selected, as follows:

function select(eventInfo) {
  var value = eventInfo.parameter.myLbName;
  Browser.msgBox(value);
  return app; 
}

The output should show:

Voila! Next up, doing something more useful than just displaying the entry from the list box.

Deleting rows of data using Google Script

I'm building a basic contacts database inside of a Google Spreadsheet, primarily as a way to learn Javascript. So far I've looked at passing the data in my spreadsheet into an array for manipulation and then how to do this with a dynamically changing data range in my spreadsheet. This post explores the code needed to delete rows of data in the spreadsheet. After this we'll look at adding rows of data using a GUI, then adding a way to search for a contact and display the data for that contact. That'll form the basis of my small program, and I can build out from there.

Deleting a row of data with Google script is pretty easy - there's a "deleteRow()" method that we call on to achieve this. I want to give the user a choice of which row to delete (using an input box) and then check that the user enters a valid row number before executing the delete row method.

• So, step 1 is to determine how many rows are in the spreadsheet, let's call it N.
• Step 2 is to ask the user to choose a row to delete between 1 and N, using an input box.
• Step 3 is to check the user has entered a valid number between 1 and N before executing the deleteRow command. This is achieved with a do-while loop. The loop keeps looping until the entry in the user box is a number between 1 and N.
• Step 4 is to delete the row chosen by the user in Step 3, using the deleteRow() method.
• That's it!

The code to achieve is as follows:

function deleteRow() {
  // select spreadsheet and sheet
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheets()[1];
  
  // get the size of the data range
  var N = sheet.getLastRow();
  var numString = "Choose row to delete between 1 and " + N;
  
  var isNum = false;
  
  // here is the awesome do-while loop!
  // it runs through the loop once, then checks status of isNum
  // while isNum is still false then it loops again
  // if user has entered a number between 1 and N then
  // set isNum to true
  // when isNum is true, we exit the loop. Magic!
  do {
    var delNum = Browser.inputBox(numString);
      if (delNum > 0 && delNum <= N) {
        isNum = true;
      }
  } while (isNum == false);
  
  sheet.deleteRow(delNum);  
}

Scripting for Dynamic Ranges in Google Spreadsheets

In my previous post, I took the data from my google spreadsheet and passed into an array, so that I could then perform an operation (print out the third row of data). However, I was selecting a static range from my spreadsheet - fine for that example, but not suitable for any application in real life, where your database will change in size. This example below uses the same spreadsheet as yesterday's example.

So what I want to do is change the code to work whether my spreadsheet has 10 rows, 100 rows or 10,000 rows. I also want to simplify yesterday's code, which was largely based on this excellent google tutorial.
This is the code snippet that I want to change:

//select the data range - static for now
var dataRange = sheet.getRange("A1:D8");

Instead of referencing cells, I use the "getLastRow()" and "getLastColumn()" methods to find out the size of my data range and use that in my "getRange" method. The code is as follows, telling the computer to grab everything from cell (1,1) to (last row, last column):

// grab the range from cell (1,1) to last row and column
// and put into a variable called "dataRange"
var dataRange = sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn());

We can make the code cleaner by defining two variables, N and P, that hold the values of the last row and last column - we'll find these are useful elsewhere. The code to do this is as follows:

// get the size of the data range
var N = sheet.getLastRow();
var P = sheet.getLastColumn();

And then the code to grab the range becomes:

var dataRange = sheet.getRange(1,1,N,P);

So, this is good, I now have the data range selected. Time to pass into an array. For my own learning, I'm going to do this step by step, as follows: i) create a variable that holds the header labels (these are used as labels in the row arrays); ii) create a row array for one example row in my data range (I chose row 2); and finally iii) create a new array that holds each row array.

Step 1: create a variable of header labels by using the "getValues()" method on the data range. Notice the "[0]" which ensures I'm selecting the first row i.e. the header row. The code is:

// define headers variable as first row of data range 
// use as labels in my array
var headers = dataRange.getValues()[0];

Step 2: create an object array called "object" and pass in the data from a single row. The idea is to use a for loop that starts with the data in the first column, puts into the array with the label "header[0]", then repeat for the second column, which has label "header[1]" through to the last column. The code is as follows:

//get values from data range
var dataRangeValues = dataRange.getValues();

// create the empty array object
var object = {};

for (var j = 0; j < P; j++) {
   var cellData = dataRangeValues[4][j]; // select data from row 4
   object[headers[j]] = cellData; // pass into object array
}

To test, let's try printing out a value stored in the array. I chose something from the 4th column with the following snippet of code:

var stringToDisplay = object["web"];
Browser.msgBox(stringToDisplay);

The output should return a message box with "www.example4.com".

Step 3: finally, I want to grab every row array and pass these into a new array called "mainObjects". This is achieved by another for loop that cycles through from the first to the last row. The code is as follows:

// define empty array to hold all row arrays
var mainObject = []; 
  
for (var i = 0; i < N; i++) {
    
    // define empty array for each row
    var object = {}; 
    
    // for each row, loop to add data from each column
    for (j = 0; j < P; j++) {
      var cellData = dataRangeValues[i][j];
      object[headers[j]] = cellData;
    }
    
    // push each row array into the main array
    mainObject.push(object);
}

Lastly for tonight, let's test that the data is correctly placed into the "mainObjects" array by printing out an example value (the first name of the third row array), as follows:

var thirdEntry = mainObject[3];
Browser.msgBox(thirdEntry.name1);

The output should be a message box showing "Bob" as follows:

So, the full code for this example is:

function array2() {
  // select spreadsheet and sheet
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheets()[0];
  
  // get the size of the data range
  var N = sheet.getLastRow();
  var P = sheet.getLastColumn();
  
  // grab the range from cell (1,1) to the cell in the last row and last column
  // and put into a variable called "dataRange"
  var dataRange = sheet.getRange(1,1,N,P);
  
  // pass the header row into a headers variable for use as labels in my array
  var headers = dataRange.getValues()[0];
  
  var dataRangeValues = dataRange.getValues();
  
  // define empty array to hold all row arrays
  var mainObject = []; 
  
  for (var i = 0; i < N; i++) {
    
    // define empty array for each row
    var object = {}; 
    
    // for each row, loop to add data from each column
    for (j = 0; j < P; j++) {
      var cellData = dataRangeValues[i][j];
      object[headers[j]] = cellData;
    }
    
    // push each row array into the main array
    mainObject.push(object);
  }

  var thirdEntry = mainObject[3];
  Browser.msgBox(thirdEntry.name1);
  
}

Any thoughts or corrections welcome! Tomorrow I will show some of the work I've been doing to create a GUI to add rows of data to my spreadsheet. My goal is to build a contacts database in a google doc that I can add data to, edit existing data, delete data and display data. Each of these posts will be a building block for my final application.

Passing data from a Google spreadsheet into an array object

What's the deal here? I have 4 columns in my Google spreadsheet consisting of an index, a first name, a second name and a web address and I want to pass this data into an array so I can then perform functions, such as searching or displaying a specific entry. (I'm building a mini database app to manage contacts, so the next few blog posts will all be building blocks - small programs that can all be bolted together to create my full application.)

The following diagram shows my test data setup:

I'm working from the following tutorial and simplifying the code to fit my needs:

Extending Google Sheets

Before diving into the code, it's worth thinking about what we're trying to do here. I want to pass the data from each row into an array of elements e.g. [index, name1, name2, web] and then I want to hold all of these row arrays in a main array like this:

row 1:   [index[0], name1[0], name2[0], web[0]]

row 2:   [index[1], name1[1], name2[1], web[1]]

row 3:   [index[2], name1[2], name2[2], web[2]]

etc..

So in my case, with the above data, the array I want to have is:

[1, James, Smith, www.example1.com],

[2, Laura, Jones, www.example2.com],
[3, Bob, Philips, www.example3.com],

etc...

Moving now to the code, I need to start by making sure I'm in the correct spreadsheet and the correct sheet within that spreadsheet. Then I select the range of my data (I'm doing this with static references at the moment but will want to change to be dynamic further down the line). Finally, I call the function "getRowsData" (see below) with the arguments "sheet" (i.e. my current sheet) and "dataRange" (i.e. my range of data):

function arrayTest() {
  // select the current spreadsheet and first sheet within
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  
  //select the data range - static for now
  var dataRange = sheet.getRange("A1:D8");
  
    // For every row of employee data, generate an employee object.
  var employeeObjects = getRowsData(sheet, dataRange);
}

The function "getRowsData":
This function takes an input of two arguments: sheet and range. By passing the current sheet from the arrayTest function above I can reference it to identify the names in the header row, and thereby use these to name each item in my array e.g. for the column "name1" I want to identify each row entry as: name1[1] = "James"

function getRowsData(sheet,range) {
  var headersRange = sheet.getRange("A1:D1");
  var headers = headersRange.getValues()[0];
  return getObjects(range.getValues(), headers);
}

The function "getObjects":
You'll see that within "getRowsData" we also call on another function called "getObjects". We pass the data range and the header values (to use as names for each piece of data in the row arrays) to this function and it returns an array object consisting of each row of data.

// For every row of data in data, generates an object that contains the data.
function getObjects(data, keys) {
  var objects = [];
  for (var i = 0; i < data.length; i++) {
    var object = {};
    var hasData = false;
    for (var j = 0; j < data[i].length; j++) {
      var cellData = data[i][j];
      object[keys[j]] = cellData;
      hasData = true;
    }
    if (hasData) {
      objects.push(object);
    }
  }
  return objects;
}

In a nutshell what's happening here is that we declare an array object called "objects". Then for each row of data (the number of rows identified by the "data.length" syntax in the first for loop), each cell in turn in a given row is "pushed" into the array. The whole array, called "objects", is then returned as the output of this function.

So how do I know if it works?

Well, we can test it by writing a couple of lines of code to choose some information from the array. So in the first function, "arrayTest", drop in these two lines of code just above the final "}" (here, I've chosen the third entry as an example):

  //choose a test entry from the array
  var thirdEntry = employeeObjects[3];

and then finally, add these two lines to display the requested entry from the array (note, I'm only displaying name1 and name2 back but you could easily add in "index" and "web" as well):

  var stringToDisplay = "The third row is: " + thirdEntry.name1 + " " + thirdEntry.name2;
  Browser.msgBox(stringToDisplay);

And voila! You should get the following output:

Lastly, a caveat - I'm new to Javascript and I'm learning as I go so I don't promise that everything I write is perfect by any shot. However, by trying to explain what I'm doing, it's really helping me to understand the code and what each line does. Thank you for your patience. Any constructive feedback appreciated. Happy coding!

Hello world

Well, it has to start this way right? With the ubiquitous Hello World program, a program beloved of all beginner tutorials and computer books.

1. Create a new Google spreadsheet from the Drive home page and call it "Hello World".

2. On the menu bar, go to Tools > Script Editor…  which opens a new tab in your browser.

3. Delete the boilerplate code that is currently sitting in the main window and type in the following snippet of code:

function helloWorld () {
  Browser.msgBox("Hello World");
}

4. Save this script as "Hello World".

5. Hit run and switch back over to your Google spreadsheet.

6. Hey presto! There's a message box saying "Hello World"

Ok, what's this all about - I'll be posting my experiments and thoughts here as I learn to code. I've been working extensively doing data analysis in Excel for many years, MySQL on/off for the last year and have recently learnt VBA so that I can automate some of the manual tasks I do at work.

I recently completed the Javascript course on Codeacadamy (which I'd highly recommend) so I'm keen to put that into practise building small apps for Google spreadsheets.

Let's see where this journey takes me.

Ben, February 2014