Fixing and Understanding Google Apps Script Code: A Detailed Breakdown

If you’re working with Google Apps Script to automate tasks in Google Sheets, you may encounter issues that require debugging and improvements. In this blog post, we’ll analyze a given script, fix potential issues, and improve the overall functionality.

---

Understanding the Code

The script creates a custom menu in Google Sheets and provides various functions:

1. onOpen(e) – Adds an “Advanced” menu with options to trigger other functions.
2. findSheetData(sheetNameSel) – Retrieves data from a specified sheet.
3. showDashboard() – Displays a modal dialog listing all sheet names.
4. showInfo() – Displays an informational modal dialog.
5. addFormula() – Inserts a formula into a specific sheet.
6. buildContent() – Creates new sheets, fills them with random data, and copies values from a “main” sheet.

---

Identified Issues and Fixes

Issue 1: onOpen(e) Missing Semicolon

The onOpen() function is missing a semicolon at the end of addToUi(). While Apps Script often tolerates missing semicolons, it’s best practice to include them for consistency.

Fix:

function onOpen(e) {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Advanced')
    .addItem('Content', 'buildContent')
    .addItem('Formula', 'addFormula')
    .addSeparator()
    .addItem('Info', 'showInfo')
    .addItem('Dashboard', 'showDashboard')
    .addToUi();
}

---

Issue 2: Hardcoded Spreadsheet ID

The script references a specific spreadsheet using SpreadsheetApp.openById('1T6tej_vhVdzlGtIEL2G7x8-Ri_m_lfZMIHLVYMMBWnQ'). If the script is meant to be used across multiple spreadsheets, this approach is limiting.

Fix: Use SpreadsheetApp.getActiveSpreadsheet()

var ss = SpreadsheetApp.getActiveSpreadsheet();

This ensures that the script works regardless of which spreadsheet it’s deployed in.

Example Update for findSheetData()

function findSheetData(sheetNameSel) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetNameSel);

  if (sheet) {
    var data = sheet.getDataRange().getValues();
    Logger.log(data);
    return { success: true, sheetNameSel: sheetNameSel, data: data };
  }

  return { success: false, sheetNameSel: sheetNameSel };
}

---

Issue 3: Missing Null Check for addFormula()

The function addFormula() assumes that “New Sheet 1” exists. If it doesn’t, the script will throw an error.

Fix: Check for Null Before Using the Sheet

function addFormula() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('New Sheet 1');

  if (!sheet) {
    Logger.log("Sheet 'New Sheet 1' not found.");
    return;
  }

  var range = sheet.getRange('E1:E10');
  range.setFormula('=B1+C1+D1');  // Corrected formula syntax
  range.setFontColor('red');
  range.setFontWeight('bold');
}

---

Issue 4: buildContent() Creates Unnamed Sheets

The script creates and deletes sheets but doesn’t explicitly define where new sheets should be created. If “main” doesn’t exist, an error occurs.

Fix: Check for Main Sheet Existence

function buildContent() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var mainSheet = ss.getSheetByName('main');

  if (!mainSheet) {
    Logger.log("Main sheet not found. Exiting.");
    return;
  }

  var valueToCopy = mainSheet.getRange(1, 1, 10, 1).getValues();

  for (var x = 1; x <= 4; x++) {
    var sheetName = 'New Sheet ' + x;
    var sheet = ss.getSheetByName(sheetName);

    if (sheet) {
      ss.deleteSheet(sheet);
    }

    sheet = ss.insertSheet(sheetName);

    for (var col = 1; col <= 3; col++) {
      for (var row = 1; row <= 10; row++) {
        sheet.getRange(row, col).setValue(Math.ceil(Math.random() * 1000));
      }
    }

    sheet.insertColumnBefore(1);
    sheet.getRange(1, 1, 10, 1).setValues(valueToCopy);
  }

  Logger.log('Content built successfully.');
}

---

Issue 5: showDashboard() and showInfo() Reuse Variables Incorrectly

Both functions reuse variables that might cause unexpected results.

Fix: Use Unique Variables and Add Error Handling

function showDashboard() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var sheetNames = sheets.map(sheet => sheet.getName());

  var t = HtmlService.createTemplateFromFile('dashboard');
  t.data = { sheets: sheetNames };

  var html = t.evaluate().setWidth(1200).setHeight(600);
  SpreadsheetApp.getUi().showModalDialog(html, 'Project Dashboard');
}

function showInfo() {
  Logger.log('Displaying info');
  var t = HtmlService.createTemplateFromFile('info');

  var html = t.evaluate().setWidth(1200).setHeight(600);
  SpreadsheetApp.getUi().showModalDialog(html, 'Project Info');
}

---

Final Thoughts

These fixes improve the script’s robustness, maintainability, and error handling. Here’s a summary of the key improvements:

✅ Added missing semicolons for consistency
✅ Replaced hardcoded spreadsheet ID with getActiveSpreadsheet()
✅ Added null checks before using sheets
✅ Fixed incorrect formula syntax in addFormula()
✅ Ensured sheets are properly created and deleted in buildContent()
✅ Refactored showDashboard() and showInfo() for better variable usage

These changes will make your script more reliable and adaptable for different spreadsheets. Happy coding! 🚀