VLOOKUP style app script using UiApp and textBox form to reference a spreadsheet

Question

I'm trying to create an app script to work inside a Google Site that has a search text box which is supposed to reference a Spreadsheet and spit out results from that search. The problem, I believe, exists in the second half of the code where I'm trying to reference the spreadsheet based on the text entered into the box provide by the code below.

function doGet(e) {
var doc = SpreadsheetApp.openById(SPREADSHEET_ID_GOES_HERE);
var app = UiApp.createApplication().setTitle('New app');

 // Create the entry form, a 1 x 2 grid with text boxes for name, age, and city that is then added to a vertical panel
var grid = app.createGrid(1, 2);
grid.setWidget(0, 0, app.createLabel('Name:'));
grid.setWidget(0, 1, app.createTextBox().setName('userName').setId('userName'));


 // Create a vertical panel and add the grid to the panel
 var panel = app.createVerticalPanel();

panel.add(grid);

var buttonPanel = app.createHorizontalPanel();

var button = app.createButton('submit');
var submitHandler = app.createServerClickHandler('submit');
submitHandler.addCallbackElement(grid);
button.addClickHandler(submitHandler);
buttonPanel.add(button);

  var closeButton = app.createButton('close');
  var closeHandler = app.createServerClickHandler('close');
  closeButton.addClickHandler(closeHandler);
  buttonPanel.add(closeButton);

// Create label called statusLabel and make it invisible; add buttonPanel and          statusLabel to the main display panel.
var statusLabel = app.createLabel().setId('status').setVisible(false);
panel.add(statusLabel);

panel.add(buttonPanel);

app.add(panel);
return app;
}


function close() {
var app = UiApp.getActiveApplication();
app.close();

return app;
}

The Problem exists in the code below. I'm trying to search the Spreadsheet using the text entered into the textBox. I'm not sure how to achieve this.

// function called when submit button is clicked
function submit(e) {

// Write the data in the text boxes back to the Spreadsheet
var cell = getValue(e.parameter.submit);

var doc = SpreadsheetApp.openById('SPREADSHEET_ID_GOES_HERE');
var ss = doc.getSheets()[0];
var lastRow = doc.getLastRow();
var data = ss.getRange(2, 1, 2, 4).getValues();

for(nn=0;nn<data.length;++nn){
 if (data[nn][1]==cell){break} ;// if a match in column B is found, break the loop
  }

 // Make the status line visible and tell the user the possible actions
app.getElementById('status').setVisible(true).setText(data[nn][1]);
return app;
}​

Gregorio

What's the actual problem? Which line, and what doesn't happen? If an error is thrown, please include details of the error. – Brosy

Answer 1

When the form is submitted, your handler function submit(e) receives an event, e. If we log the value of it with Logger.log(Utilities.jsonStringify(e)); we'll see that looks like this:

{"parameter":
  {
    "clientY":"52",
    "clientX":"16",
    "eventType":"click",
    "ctrl":"false",
    "meta":"false",
    "source":"u146139935837",
    "button":"1",
    "alt":"false",
    "userName":"Lucy",
    "screenY":"137",
    "screenX":"16",
    "shift":"false",
    "y":"14",
    "x":"12"
  }
}

We can access the value of our input boxes by name, for example e.parameter.userName.

One other tweak, we need to get a value for app. Here's what your working handler looks like:

// function called when submit button is clicked
function submit(e) {
  var app = UiApp.getActiveApplication();

  Logger.log(Utilities.jsonStringify(e));  // Log the input parameter (temporary)

  // Write the data in the text boxes back to the Spreadsheet
  var cell = e.parameter.userName;

  var doc = SpreadsheetApp.openById('SPREADSHEET-ID');
  var ss = doc.getSheets()[0];
  var lastRow = doc.getLastRow();
  var data = ss.getRange(2, 1, 2, 4).getValues();

  var result = "User not found";
  for (nn = 0; nn < data.length; ++nn) {
    if (data[nn][1] == cell) {
      result = data[nn][1];
      break
    }; // if a match in column B is found, break the loop
  }

  // Make the status line visible and tell the user the possible actions
  app.getElementById('status').setVisible(true).setText(result);
  return app;
}

Pick

I thought it had to do with the log I just wasn't sure how to call it back. This code worked beautifully. Thank you! – Gregorio

Perhaps you can help me with one other question. With the current for/if logic test, if it were to fail to find a matching userName in the spreadsheet it just spits out an error once the code is stopped. How would I add an element so that instead it spit out something along the lines of "User not found" in place of the setText(data[nn][1]) code? Thanks again – Gregorio

Edited, with an example of one way to accomplish that, which can be expanded to additional fields easily. – Pick

Wow, that was incredibly easy. Thanks I have it all working now. – Gregorio