Creating a dialogue box to capture booking details in Google sheets App Script

0
votes

I am using Google sheets with app script to build a reservations chart for a hotel

Can someone please tell me if there is a way to add a Dialogue box to a google sheet that can ask multiple questions? I have found the Prompt Dialogue box but that seems to allow only one text box for data entry. I have something like this

var result = ui.prompt(
  "Let's get to know each other!",
  "Please enter your name:",
  ui.ButtonSet.OK_CANCEL
);

// Process the user's response.
var button = result.getSelectedButton();
var text = result.getResponseText();
if (button == ui.Button.OK) {
  // User clicked "OK".
  ui.alert("Your name is " + text + ".");
} else if (button == ui.Button.CANCEL) {
  // User clicked "Cancel".
  ui.alert("I didn't get your name.");
} else if (button == ui.Button.CLOSE) {
  // User clicked X in the title bar.
  ui.alert("You closed the dialog.");
}

If there isnt something pre-built, can you please recommend how else I can capture data which would then feed a second sheet within the same spreadsheet .

many thanks

1
google-apps-scriptgoogle-sheetsgoogle-workspace
1. Please add in any code you have already done as text into the question using code blocks 2. Only ask one thing per question, please make your question focused on one problem, but feel free to ask more than one question! See How To Ask for more info 3. Your sheet is protected and cannot be accessed. – iansedano
What should I do when someone answers my question? – iansedano

1 Answers

1
votes

You need to use the HTML service

The method you are using is quite limited. To go further than that you would need to create your own HTML file and serve it from Apps Script. The flow of that is:

  • Create an HTML file in the script editor
  • Create your HTML form
  • Write a script on the HTML that calls a function on your gs script.

Sample code

Code.gs

// Creates form on UI
function form() {
  var htmlOutput = HtmlService
  .createHtmlOutputFromFile('form')
  .setWidth(250)
  .setHeight(300);
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Add your info');
}

// Uses info passed from rendered HTML to add data to sheet.
function addForm(data){
  console.log(data)
  SpreadsheetApp.getActiveSpreadsheet().getRange("A1:C1").setValues([data])
}

form.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script>
      // function to run when server-side script is successful
      function onSuccess(){
          google.script.host.close()
      }
    
      // function to run when form is submitted
      function sendForm(){ 
          console.log("RUNNING")
          let name = document.getElementById("name").value
          let country = document.getElementById("country").value
          let DOB = document.getElementById("DOB").value
          let data = [name, country, DOB]
          
           // call server side function
          google.script.run.withSuccessHandler(onSuccess).addForm(data)
      }
  </script>
  </head>
  <body>
    <form id="form" onsubmit="sendForm()">
        <label for="name">First name:</label><br>
        <input type="text" name="name" id="name">
        <label for="country">Country:</label><br>
        <input type="text" name="country" id="country">
        <label for="DOB">DOB:</label><br>
        <input type="text" name="DOB" id="DOB">
        <input type="submit">
    </form>
  </body>
  
</html>

Explanation

  • When the function form() is run from the script editor, it displays your HTML in the Spreadsheet UI.
  • This shows a form with three text inputs and a submit button.
  • The submit button has a onsubmit="sendForm()" which is a function defined within the HTML.
  • It gets all the info from the form, and then calls google.script.run.withSuccessHandler(onSuccess).addForm(data). This is an asynchronous function that sends a request to the gs file to run the addForm function and then when successful, to run the onSuccess function in the HTML.
  • The onSuccess simply closes the form.
  • addForm adds the info to a range in the spreadsheet.

Reference