Creating a pop-up form on google sheets using custom forms

Question

I am very new to creating functions on google sheets and so am struggling in creating code that returns a value from a pop up form. I have been playing around with this code from Google Apps Script:

var ui = SpreadsheetApp.getUi();
var response = ui.prompt('Getting to know you', 'May I know your name?', ui.ButtonSet.YES_NO);

if (response.getSelectedButton() == ui.Button.YES) {
  Logger.log('The user\'s name is %s.', response.getResponseText());
} else if (response.getSelectedButton() == ui.Button.NO) {
  Logger.log('The user didn\'t want to provide a name.');
} else {
  Logger.log('The user clicked the close button in the dialog\'s title bar.');
}

and was wondering if there was a way to return the response.getResponseText to a cell. When I use the "return" function where the "logger.log" section is I keep getting the error - 'Cannot call SpreadsheetApp.getUi() from this context'

is there another method, in editing this script or should I interpret getting a pop-up form for user interaction differently.

Thanks

Answer 1

Custom functions have limitations, they cannot call SpreadsheetApp.getUi()

As an alternative you can draw a custom button to which you can assign a script.

You can dessign the script in such a way that it sets a value into the cell that was active at the moment the button was clicked.

Sample

function myFunction() {
  var cell = SpreadsheetApp.getCurrentCell();
  var ui = SpreadsheetApp.getUi();
  var response = ui.prompt('Getting to know you', 'May I know your name?', ui.ButtonSet.YES_NO);
  var output;
  if (response.getSelectedButton() == ui.Button.YES) {
    output = 'The user\'s name is '+ response.getResponseText();
  } else if (response.getSelectedButton() == ui.Button.NO) {
    output = 'The user didn\'t want to provide a name.';
  } else {
    output = 'The user clicked the close button in the dialog\'s title bar.';
  }
  cell.setValue(output);
}