Google script UI - reading to and posting from a spreadsheet, link to populated UI

Question

I have created a UI that I would like to use to read from and post to a spreadsheet. 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.

Here is the current UI.

I would like the end result to be: 1. When a new entry is submitted, managers get emailed with a link.

That link will open up the UI.
The top (gray) area will be populated with the pertinent values from the spreadsheet, based on the ID number (column A).
The bottom area will be blank, or populated with the pertinent values from the spreadsheet if they exist.
The user will fill out the bottom area as necessary, and submit.
Data that was entered into the UI will populate the spreadsheet in the correct columns.
A user looking at the spreadsheet will be able to click on the ID number, and it will pull up the populated UI.

This is quite simple just using Forms, but I'm looking for something more flexible, so I'm using UI.

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.

I understand how to pass values from the UI to the spreadsheet, but not vice-versa.
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?

The code for the UI is below. Yes, I know the UI is ugly right now.

Any and all help is much appreciated! I'm still pretty darn new at this but learning new things daily.

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("<br/>"));
  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<24;++h){
  if(h<10){var hourstr='0'+h}else{var hourstr=h.toString()}
  fuHour.addItem(hourstr)
  }
  for (m=0;m<60;++m){
  if(m<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;
}

score 0 · Accepted Answer · edited May 23 '17 at 11:50

0

To send an email, you need to use the MailApp Class:

MailApp Class - Google Documentation

To get data out of the spreadsheet, there is a SpreadsheetApp Class:

SpreadsheetApp Class - Google Documentation

You can get a reference to the currently active spreadsheet with the getActive() method:

getActive() Spreadsheet Method

That returns the spreadsheet object. Once you have a reference to the current spreadsheet, you can get the active sheet, and from the active sheet get a two dimensional array of values:

getSheetValues Method

Here is a StackOverflow question that might help you:

Search a Spreadsheet by column

edited May 23 '17 at 11:50

Community

1
1

answered Jul 15 '14 at 01:53

Alan Wells

30,746
15
104
152

1

Thank you! I've gotten everything pretty much nailed down, except - how do I create a URL that will bring the user to the pre-populated UI... Any ideas? – user3795414 Jul 15 '14 at 04:05
Apps Script can read string values from the URL if that helps. `doGet(someVar)` will take the search string from the end of a url, and put the search string into the variable `someVar`. Typically, that variable name is shown as `e` `doGet(e)`, but there is nothing special about the letter `e`. After the letter `e`, you must use `parameter`, then the name of the key for the URL search string: `varName.parameter.nameOfSearchStringKey`. So if you had a URL: `www.mywebsite.com?customer=JohnDay`. To get the value of the customer, you would use: `var getCustmr = e.parameter.customer`. – Alan Wells Jul 15 '14 at 04:27
So, you can pass custom information to Apps Script with search string values at the end of a URL. From the info passed in the URL search string, Apps Script would look up particular information from the worksheet, then display it in the form. For example, you pass the customer ID in the search string, and when Apps Script runs, it takes the customer ID, looks up their info, and injects values into the UI form. You have links in the first column of the spreadsheet. Just use the Apps Script URL that ends with `exec`, then concatenate a unique search string to each URL with a string formula. – Alan Wells Jul 15 '14 at 04:36

Google script UI - reading to and posting from a spreadsheet, link to populated UI

1 Answers1