title: Google script UI - reading to and posting from a spreadsheet, link to populated UI
body: <p>I have created a UI that I would like to use to read from and post to a <a href="https://docs.google.com/spreadsheets/d/11Tf5vD_b_s__F6jPkggE7LUhGH7pQ5Rbuaf_zk2qiWI/edit?usp=sharing" rel="nofollow">spreadsheet</a>.  Please ignore, for now, the link formatting in col A.  Right now, that's linking to a form.  I want to use the UI instead, and will remove references to the linked form as soon as I have this set up.</p>
  
  <p><a href="https://script.google.com/macros/s/AKfycbzsTkgD0KVwIUNhyhqwxeezj5azvTsJSa8ZSmRf9VeMu7vkdvw/exec" rel="nofollow">Here is the current UI</a>.</p>
  
  <p>I would like the end result to be:
  1. When a new entry is submitted, managers get emailed with a link.</p>
  
  <ol>
  <li><p>That link will open up the UI.</p></li>
  <li><p>The top (gray) area will be populated with the pertinent values from the spreadsheet, based on the ID number (column A).</p></li>
  <li><p>The bottom area will be blank, or populated with the pertinent values from the spreadsheet if they exist.</p></li>
  <li><p>The user will fill out the bottom area as necessary, and submit.</p></li>
  <li><p>Data that was entered into the UI will populate the spreadsheet in the correct columns.</p></li>
  <li><p>A user looking at the spreadsheet will be able to click on the ID number, and it will pull up the populated UI.</p></li>
  </ol>
  
  <p>This is quite simple just using Forms, but I'm looking for something more flexible, so I'm using UI.</p>
  
  <p>Here are my problems:
  1. I cannot find a simple, straightforward script that can search for a value, and, once that value is found, get the rest of the data in the row that contains that value.
       ◦ I've searched like crazy for this, and everyone seems to have a different idea, but none of them fit what I am looking for. </p>
  
  <ol>
  <li><p>I understand how to pass values from the UI to the spreadsheet, but not vice-versa.</p></li>
  <li><p>Does var formUrl = formResponse.toPrefilledUrl(); work the same way with UI as it does with forms?  If not, how do I create a link to the pre-populated UI?</p></li>
  </ol>
  
  <p>The code for the UI is below.  Yes, I know the UI is ugly right now.</p>
  
  <p>Any and all help is much appreciated!  I'm still pretty darn new at this but learning new things daily.</p>
  
  <pre><code>function doGet(e) {
    var complaintApp = UiApp.createApplication().setTitle("Complaint Follow-Up").setWidth(1100).setHeight(1000);
    //For pre-populating  
    var prePanel = complaintApp.createAbsolutePanel().setWidth('100%').setStyleAttributes({background: 'D8D8D8'})
  
    var infoGrid = complaintApp.createGrid(2, 6).setStyleAttributes({fontWeight: "bold", }).setWidth('100%');
    var idNum = complaintApp.createLabel('Complaint ID #:').setStyleAttributes({textAlign: 'right', float: 'left'});
    infoGrid.setWidget(0, 0, idNum);
    infoGrid.setWidget(0, 1, complaintApp.createTextBox().setName('ID').setId('ID').setStyleAttributes({float: 'left'}));
  
    infoGrid.setWidget(0, 2, complaintApp.createLabel('Submitted by:').setStyleAttributes({textAlign: 'right', margin: "0 auto"}));
    infoGrid.setWidget(0, 3, complaintApp.createTextBox().setName('subBy').setId('subBy').setStyleAttributes({margin: "0 auto"}));
  
    infoGrid.setWidget(0, 4, complaintApp.createLabel('Submitted at:').setStyleAttributes({textAlign: 'right'}));
    infoGrid.setWidget(0, 5, complaintApp.createTextBox().setName('subAt').setId('subAt').setStyleAttributes({float: 'left'}));
  
    infoGrid.setWidget(1, 2, complaintApp.createLabel('Time since complaint \(in hours\):').setStyleAttributes({textAlign: 'right'}));
    infoGrid.setWidget(1, 3, complaintApp.createTextBox().setName('timeSince').setId('timeSince').setWidth(50));
  
    var guestGrid = complaintApp.createGrid(2, 8).setStyleAttributes({fontWeight: "bold"}).setWidth('100%');
    guestGrid.setWidget(0, 0, complaintApp.createLabel('Guest name:').setStyleAttributes({textAlign: 'right'}));
    guestGrid.setWidget(0, 1, complaintApp.createTextBox().setName('gName').setId('gName'));
  
    guestGrid.setWidget(0, 2, complaintApp.createLabel('Room #:').setStyleAttributes({textAlign: 'right'}));
    guestGrid.setWidget(0, 3, complaintApp.createTextBox().setName('roomNum').setId('roomNum'));
  
    guestGrid.setWidget(0, 4, complaintApp.createLabel('Dates of Stay:').setStyleAttributes({textAlign: 'right', float: 'right'}));
    guestGrid.setWidget(0, 5, complaintApp.createTextBox().setName('arrDate').setId('arrDate').setStyleAttributes({float: 'right'}));
    guestGrid.setWidget(0, 6, complaintApp.createLabel(' - ').setStyleAttributes({margin: '0 auto'}));
    guestGrid.setWidget(0, 7, complaintApp.createTextBox().setName('depDate').setId('depDate').setStyleAttributes({float: 'right'}));
    guestGrid.setWidget(1, 0, complaintApp.createLabel('Email Address:').setStyleAttributes({textAlign: 'right'}));
    guestGrid.setWidget(1, 1, complaintApp.createTextBox().setName('email').setId('email'));
    guestGrid.setWidget(1, 2, complaintApp.createLabel('Phone Number:').setStyleAttributes({textAlign: 'right'}));
    guestGrid.setWidget(1, 3, complaintApp.createTextBox().setName('phone').setId('phone'));
  
    var complaintGrid = complaintApp.createGrid(2, 2).setStyleAttributes({fontWeight: "bold", margin: "0 auto"}).setWidth('100%');
    complaintGrid.setWidget(0, 0, complaintApp.createLabel('Complaint concerns:'));
    complaintGrid.setWidget(1, 0, complaintApp.createTextArea().setName('department').setId('department').setWidth(200));
    complaintGrid.setWidget(0, 1, complaintApp.createLabel('Complaint:'));
    complaintGrid.setWidget(1, 1, complaintApp.createTextArea().setName('complaint').setId('complaint').setWidth(800).setHeight(100).setStyleAttributes({overflow: "auto"}));
  
    var detailsGrid = complaintApp.createGrid(2, 6).setStyleAttributes({fontWeight: 'bold'}).setWidth('100%');
    detailsGrid.setWidget(0, 0, complaintApp.createLabel('First attempt at resolution by:').setStyleAttributes({textAlign: 'right'}));
    detailsGrid.setWidget(0, 1, complaintApp.createTextBox().setName('firstRes').setId('firstRes'));
    detailsGrid.setWidget(0, 2, complaintApp.createLabel('First attempt at resolution at:').setStyleAttributes({textAlign: 'right'}));
    detailsGrid.setWidget(0, 3, complaintApp.createTextBox().setName('firstTime').setId('firstTime'));
    detailsGrid.setWidget(0, 4, complaintApp.createLabel('Resolution provided:').setStyleAttributes({textAlign: 'right'}));
    detailsGrid.setWidget(0, 5, complaintApp.createTextArea().setName('resList').setId('resList'));
    detailsGrid.setWidget(1, 0, complaintApp.createLabel('Appeasement amount:').setStyleAttributes({textAlign: 'right'}));
    detailsGrid.setWidget(1, 1, complaintApp.createTextBox().setName('appeasement').setId('appeasement'));
    detailsGrid.setWidget(1, 2, complaintApp.createLabel('Guest mindset after initial resolution:').setStyleAttributes({textAlign: 'right'}));
    detailsGrid.setWidget(1, 3, complaintApp.createTextBox().setName('mindset').setId('mindset'));
    detailsGrid.setWidget(1, 4, complaintApp.createLabel('Is follow-up required?').setStyleAttributes({textAlign: 'right'}));
    detailsGrid.setWidget(1, 5, complaintApp.createTextBox().setName('followup').setId('followup'));
  
    var topPanel = complaintApp.createHorizontalPanel().setStyleAttributes({borderStyle: "groove", borderWidth: "2", borderColor: "threedface"}).setWidth('100%');
    var guestPanel = complaintApp.createCaptionPanel('Guest Information').setStyleAttributes({background: "#D8D8D8", fontWeight: 'bold'});
    var complaintPanel = complaintApp.createCaptionPanel('Complaint Information').setStyleAttributes({background: "#D8D8D8", fontWeight: 'bold', overflow: 'auto'});
    var detailsPanel = complaintApp.createCaptionPanel('Initial Resolution Attempt').setStyleAttributes({fontWeight: 'bold'});
  
  
    topPanel.add(infoGrid);
    guestPanel.add(guestGrid);
    complaintPanel.add(complaintGrid);
    detailsPanel.add(detailsGrid);
  
    prePanel.add(topPanel);
    prePanel.add(guestPanel);
    prePanel.add(complaintPanel);
    prePanel.add(detailsPanel);
  
    complaintApp.add(prePanel);
  
    //Take info
    var subPanel = complaintApp.createAbsolutePanel().setWidth('100%').setStyleAttributes({background: '#FBFBEF'})
  
    var form = complaintApp.createFormPanel();  
    var flow = complaintApp.createFlowPanel();
    var grid1 = complaintApp.createGrid(2, 1).setStyleAttributes({margin: "0 auto"});
    grid1.setWidget(0, 0, complaintApp.createHTML("&lt;br/&gt;"));
    grid1.setWidget(1, 0, complaintApp.createLabel("Please fill out the following, where applicable.")
                     .setStyleAttributes({textDecoration: "underline", fontSize: "16", fontWeight: "bold"}));
  
    var fuGrid = complaintApp.createGrid(1, 7).setStyleAttributes({margin: "0 auto"})//.setWidth('100%');
    var userName = complaintApp.createTextBox().setName('userName').setId('userName').setStyleAttributes({float: 'left'})
    fuGrid.setWidget(0, 0, complaintApp.createLabel("Your name:").setStyleAttributes({textAlign: 'right', fontWeight: "bold"}));
    fuGrid.setWidget(0, 1, userName);
    fuGrid.setWidget(0, 2, complaintApp.createLabel("Date/time of follow-up:").setStyleAttributes({textAlign: 'right', fontWeight: "bold"}));
    var fuDate = complaintApp.createDateBox().setFormat(UiApp.DateTimeFormat.DATE_SHORT).setName('fuDate').setId('fuDate').setStyleAttributes({float: 'left'})
    fuGrid.setWidget(0, 3, fuDate);
    var fuHour = complaintApp.createListBox().setName('fuHour').setId('fuHour').setWidth(60).setStyleAttributes({float: 'left'}).addItem("Hr");
    var fuMin = complaintApp.createListBox().setName('fuMin').setId('fuMin').setWidth(60).setStyleAttributes({float: 'left'}).addItem("Min");
  for (h=0;h&lt;24;++h){
    if(h&lt;10){var hourstr='0'+h}else{var hourstr=h.toString()}
    fuHour.addItem(hourstr)
    }
    for (m=0;m&lt;60;++m){
    if(m&lt;10){var minstr='0'+m}else{var minstr=m.toString()}
    fuMin.addItem(minstr)
    }
    fuGrid.setWidget(0, 4, fuHour.setStyleAttributes({float: 'left'}));
    fuGrid.setWidget(0, 5, fuMin.setStyleAttributes({float: 'left'}));
  
    var fuDescGrid = complaintApp.createGrid(2, 4).setStyleAttributes({fontWeight: "bold", margin: "0 auto"}).setWidth('100%')
    fuDescGrid.setWidget(0, 1, complaintApp.createLabel('Description of Follow-up:'));
    var fuDesc = complaintApp.createTextArea().setName('fuDesc').setId('fuDesc').setWidth(500).setHeight(70).setStyleAttributes({overflow: "auto"})
    fuDescGrid.setWidget(1, 1, fuDesc);
    fuDescGrid.setWidget(1, 2, complaintApp.createLabel('Amount of appeasement:'));
    var fuApp = complaintApp.createTextArea().setName('fuApp').setId('fuApp');
    fuDescGrid.setWidget(1, 3, fuApp);
  
    var resSubClose = complaintApp.createGrid(1, 3).setStyleAttributes({margin: "0 auto"})
    var resHandler = complaintApp.createServerHandler("resolved");
    var resolved = complaintApp.createCheckBox("Issue is fully resolved.").setName("resCB").addValueChangeHandler(resHandler).setStyleAttributes({margin: "0 auto"});
    resSubClose.setWidget(0, 0, resolved)
    resSubClose.setWidget(0, 1, complaintApp.createSubmitButton("Submit"));
    var closeHandler = complaintApp.createServerHandler("close");
    var close = complaintApp.createButton("Close").addClickHandler(closeHandler);
    resSubClose.setWidget(0, 2, close)
  
    var resTf = complaintApp.createLabel("test").setId("resTf").setVisible(false)
  
    subPanel.add(grid1);
    flow.add(fuGrid);
    flow.add(fuDescGrid);
    flow.add(resSubClose);  
    form.add(flow);
  
    subPanel.add(form);
  
    complaintApp.add(subPanel);
    complaintApp.add(resTf);
    //var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
    //spreadSheet.show(complaintApp);
  
    return complaintApp;
  }
  function resolved(e){
    var app = UiApp.getActiveApplication();
    if (e.parameter.resCB == true){
      app.getElementById('resTf').setText("Yes")
      return app;
    }
  
  }
  
  function close(e) {
    var app = UiApp.getActiveApplication();
    app.close();
    return app;
  }
  </code></pre>