Fixing and Understanding Google Apps Script Code: A Detailed Breakdown

by

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! 🚀