Guide on Creating Input Box for Data Entry in Google Sheets

The InputBox is primarily used to gather input from users in the Google Sheets application. In this article, let’s explore how to create an Input Box in Google Sheets by writing some code in Apps Script. The result will be an input box that looks like the following:

Create an Apps Script Project in Google Sheets

This is a separate working interface outside the usual Google Sheets interface. Through this interface, you can customize Google Sheets with additional features. Here’s how to do it:

  1. Open a new or existing spreadsheet. You can enter the shortcut “spreadsheet.new” in your browser to open a spreadsheet more quickly.
  2. In the Google Sheets toolbar, go to Tools > Script editor. This action will open a new project in the Apps Script editor.
  3. Name your project by clicking on “Untitled project.”

There are Four Main Sections in the User Interface of the Apps Script Project.

  • Files in your project are displayed in a side panel on the left.
  • If you select a file, its content will be shown in the code editor, occupying most of the screen.
  • There is a toolbar above the code editor.
  • There is a menu bar at the top. This menu bar includes different menu items like File, Edit, View, Run,…

You will find the following code snippet in the editing area:

javascriptCopy codefunction myFunction () {

}

This function is a part of the named code. You can run (or execute) this code elsewhere in your program by using its name. You can also run a function by selecting its name from the drop-down menu on the toolbar and clicking the play icon.

Currently, myFunction is empty, so when you click the run button, you won’t see anything happen. The next step is to add your custom code between the two curly braces {} to complete your project.

For specific instructions and other interesting tricks on Google Sheets, please refer to the online Google Sheets office automation course, from basic to advanced. It’s an excellent Excel replacement tool for effective collaboration in the era of cloud computing.

Creating an Input Box with Code in Google Sheets

Prompt dialogs can make collaboration easier. After running the code, every time users open Sheets, they will see a prompt before performing any actions. Additionally, you can use prompts for data entry.

The code to create a prompt in Google Sheets is just a few lines:

javascriptCopy codefunction displayPrompt() {
  var ui = SpreadsheetApp.getUi();
  var result = ui.prompt("Please enter your name");
}

When users click [OK], you can retrieve the text they entered from your script using the getResponseText() method:

javascriptCopy codefunction displayPrompt() {
  var ui = SpreadsheetApp.getUi();
  var result = ui.prompt("Please enter your name");
  Logger.log(result.getResponseText());
}

You can also check if the user closed the prompt dialog (i.e., clicked [X]) using the getSelectedButton() method:

javascriptCopy codefunction displayPrompt() {
  var ui = SpreadsheetApp.getUi();
  var result = ui.prompt("Please enter your name");
  // Get the button that the user pressed.
  var button = result.getSelectedButton();
  if (button === ui.Button.OK) {
    Logger.log("The user clicked the [OK] button.");
    Logger.log(result.getResponseText());
  } else if (button === ui.Button.CLOSE) {
    Logger.log("The user clicked the [X] button and closed the prompt dialog."); 
  }
}

To Display a Prompt, Click the Play Button in the Script Editor.

Title: When you click the play button, the prompt will appear on the Google Sheets containing that script. You will see the name you entered in the script’s log. To view these logs, click View → Logs (). You can also use the shortcut CTRL + ENTER to view logs instead of using the View menu.

Here are Various Methods for Creating InputBox in Google Apps Script

Code:

javascriptCopy codefunction myFunction() {
  var name = Browser.inputBox('Enter your name');
} 

Methods:

  • inputBox(prompt)
  • inputBox(prompt, buttons)
  • inputBox(title, prompt, buttons)

Buttons:

  • OK: Displays only the “OK” button.
  • OK_CANCEL: Displays “OK” and “Cancel” buttons.
  • YES_NO: Displays “Yes” and “No” buttons.
  • YES_NO_CANCEL: Displays “Yes,” “No,” and “Cancel” buttons.

For example, if you need to create a prompt, use the following code.

Corresponding Code for Creating an InputBox in Google Apps Script

Code:

javascriptCopy codefunction myFunction() {
  var name = Browser.inputBox('First Input Box', 'Enter your name', Browser.Buttons.YES_NO);
}

Hope this guide helps you create prompts and input for entering data in Google Sheets. To not miss out on other useful office productivity tips and tricks, join Officehabit today.

Leave a Comment

Your email address will not be published. Required fields are marked *

Shopping Basket
Scroll to Top