Apps Script Code Exercises

🌟 Elevate Your Google Sheets with Custom UIs! 🌟

Apps Script User Interface Coding Exercises

👩‍💻 10 hands-on coding exercises for mastering Google Apps Script’s User Interface capabilities! These exercises range from simple alerts to complex interactive UIs, empowering you to enhance your Google Sheets with custom dialogs, sidebars, and menus.

💼 Perfect for both beginners and advanced scripters, these exercises will elevate your Google Sheets projects, making them more interactive and user-friendly.

🚀 Discover the power of custom UIs and transform your Google Sheets experience!

Exercise 1: Create a Simple Alert Dialog

Objective: Learn to create a basic alert dialog box.

Explanation: This exercise introduces the simplest form of UI in Google Apps Script: an alert dialog box.

Code:

function showAlert() {

  var ui = SpreadsheetApp.getUi();

  ui.alert(‘This is an alert!’);

}

Exercise 2: Create a Custom Dialog Box

Objective: Learn to create a custom dialog box with HTML.

Explanation: This exercise focuses on creating a custom dialog box using HTML content.

Code:

function showCustomDialog() {

  var html = ‘<p>Hello, world!</p>’;

  var ui = SpreadsheetApp.getUi();

  ui.showModalDialog(HtmlService.createHtmlOutput(html), ‘My Custom Dialog’);

}

Exercise 3: Create a Sidebar

Objective: Understand how to create a sidebar in Google Sheets.

Explanation: This exercise teaches you how to add a custom sidebar to a Google Sheets UI.

Code:

function showSidebar() {

  var html = ‘<p>This is a sidebar!</p>’;

  var ui = SpreadsheetApp.getUi();

  ui.showSidebar(HtmlService.createHtmlOutput(html));

}

Exercise 4: Using HTML and CSS in UI

Objective: Learn to enhance UI with HTML and CSS.

Explanation: This exercise focuses on improving the look and feel of the UI using HTML and CSS.

Code:

function showStyledDialog() {

  var html = ‘<div style=”color: red; font-size: 16px;”>Styled Text</div>’;

  var ui = SpreadsheetApp.getUi();

  ui.showModalDialog(HtmlService.createHtmlOutput(html), ‘Styled Dialog’);

}

Exercise 5: Adding Buttons to Dialog

Objective: Understand how to add interactive buttons to a dialog.

Explanation: This exercise teaches how to make UIs interactive with buttons.

Code:

function showDialogWithButtons() {

  var html = ‘<p>Do you want to proceed?</p><button onclick=”google.script.host.close()”>Close</button>’;

  var ui = SpreadsheetApp.getUi();

  ui.showModalDialog(HtmlService.createHtmlOutput(html), ‘Dialog with Buttons’);

}

Exercise 6: Processing Form Input

Objective: Learn to handle form input in a custom dialog.

Explanation: This exercise demonstrates how to capture and process user input from a form in a dialog.

Code:

function showFormDialog() {

  var html = ‘<form id=”myForm”><input type=”text” name=”input”><input type=”button” value=”Submit” onclick=”submitForm()”></form><script>function submitForm() {google.script.run.processForm(document.getElementById(“myForm”));google.script.host.close();}</script>’;

  var ui = SpreadsheetApp.getUi();

  ui.showModalDialog(HtmlService.createHtmlOutput(html), ‘Form Dialog’);

}

function processForm(formObject) {

  Logger.log(‘Input: ‘ + formObject.input);

}

Exercise 7: Creating a Menu in Google Sheets

Objective: Understand how to add custom menus in Google Sheets.

Explanation: This exercise focuses on enhancing the Google Sheets UI by adding a custom menu.

Code:

function onOpen() {

  var ui = SpreadsheetApp.getUi();

  ui.createMenu(‘Custom Menu’)

      .addItem(‘Show Alert’, ‘showAlert’)

      .addToUi();

}

Exercise 8: Displaying a Prompt Dialog

Objective: Learn to display a prompt dialog and capture user response.

Explanation: This exercise shows how to create a prompt dialog and handle user input.

Code:

function showPrompt() {

  var ui = SpreadsheetApp.getUi();

  var response = ui.prompt(‘Enter your name:’);

  if (response.getSelectedButton() == ui.Button.OK) {

    Logger.log(‘User name: ‘ + response.getResponseText());

  }

}

Exercise 9: Interactive Sidebars with JavaScript

Objective: Understand how to make sidebars more interactive using JavaScript.

Explanation: This exercise teaches how to use JavaScript to add interactivity to a sidebar.

Code:

function showInteractiveSidebar() {

  var html = ‘<div id=”content”><button onclick=”updateContent()”>Click Me</button></div><script>function updateContent() {document.getElementById(“content”).innerHTML = “Updated Content”;}</script>’;

  var ui = SpreadsheetApp.getUi();

  ui.showSidebar(HtmlService.createHtmlOutput(html));

}

Exercise 10: Building a Complex UI with Multiple Elements

Objective: Learn to create a complex UI with various HTML elements.

Explanation: This exercise demonstrates building a more complex UI, including text, buttons, and form elements.

Code:

function showComplexUI() {

  var html = ‘<div><h1>Title</h1><p>Some text…</p><form><input type=”text”><button type=”submit”>Submit</button></form></div>’;

  var ui = SpreadsheetApp.getUi();

  ui.showModalDialog(HtmlService.createHtmlOutput(html), ‘Complex UI’);

}