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!