Formatting, custom menus and dialogs

Concept

Formatting a range

function formatReport() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sales');
  const lastRow = sheet.getLastRow();

  // Header row
  sheet.getRange('A1:E1')
    .setFontWeight('bold')
    .setBackground('#1f4e78')
    .setFontColor('#ffffff')
    .setHorizontalAlignment('center');

  // Number and date formats
  sheet.getRange(2, 1, lastRow - 1, 1).setNumberFormat('dd-mmm-yyyy');
  sheet.getRange(2, 4, lastRow - 1, 2).setNumberFormat('₹#,##0.00');

  // Borders, freeze, auto-size
  sheet.getRange(1, 1, lastRow, 5).setBorder(true, true, true, true, true, true);
  sheet.setFrozenRows(1);
  sheet.autoResizeColumns(1, 5);
}

Most setters return the range, so you can chain them as shown. For lakh-style grouping (1,23,456.00), set the spreadsheet locale to India under File → Settings — Sheets groups digits by locale.

A custom menu

onOpen runs automatically every time someone opens the spreadsheet:

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('ExcelWalaa Tools')
    .addItem('Format report', 'formatReport')
    .addItem('Add GST column', 'addGstColumn')
    .addSeparator()
    .addSubMenu(
      SpreadsheetApp.getUi().createMenu('Help')
        .addItem('About', 'showAbout')
    )
    .addToUi();
}

Reload the sheet — a new ExcelWalaa Tools menu appears next to Help. Each item runs the function whose name you give as a string.

Alerts and prompts

function addGstColumn() {
  const ui = SpreadsheetApp.getUi();

  const response = ui.prompt('GST rate', 'Enter GST % (e.g. 3):', ui.ButtonSet.OK_CANCEL);
  if (response.getSelectedButton() !== ui.Button.OK) return;

  const rate = Number(response.getResponseText()) / 100;
  if (isNaN(rate)) {
    ui.alert('Please enter a number.');
    return;
  }

  const confirm = ui.alert(`Add GST at ${rate * 100}%?`, ui.ButtonSet.YES_NO);
  if (confirm !== ui.Button.YES) return;

  // ... do the work ...
  SpreadsheetApp.getActiveSpreadsheet().toast('GST column added', 'Done', 3);
}

function showAbout() {
  SpreadsheetApp.getUi().alert('ExcelWalaa Tools v1.0');
}

ui.alert — message box (optionally Yes/No).
ui.prompt — asks the user to type something.
toast — small, non-blocking notification in the corner.

A sidebar with HTML

Add an HTML file in the editor (+ → HTML) named Sidebar:

<!DOCTYPE html>
<html>
  <body style="font-family: Arial; padding: 12px;">
    <h3>Quick entry</h3>
    <input id="item" placeholder="Item name"><br><br>
    <input id="amount" type="number" placeholder="Amount"><br><br>
    <button onclick="save()">Add</button>
    <p id="msg"></p>
    <script>
      function save() {
        const item = document.getElementById('item').value;
        const amount = Number(document.getElementById('amount').value);
        google.script.run
          .withSuccessHandler(() => { document.getElementById('msg').textContent = 'Saved!'; })
          .addEntry(item, amount);
      }
    </script>
  </body>
</html>

And in Code.gs:

function showSidebar() {
  const html = HtmlService.createHtmlOutputFromFile('Sidebar').setTitle('Quick entry');
  SpreadsheetApp.getUi().showSidebar(html);
}

function addEntry(item, amount) {
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sales')
    .appendRow([new Date(), item, 1, amount]);
}

google.script.run.functionName(...) lets the HTML page call your server-side functions. Add showSidebar to your menu to open it.

Key points

Chain setters to format ranges; use number formats for ₹ and dates.
onOpen + createMenu gives non-technical users one-click buttons.
HtmlService + google.script.run builds simple forms inside Sheets.

What you'll learn