← All posts · 2026-08-12 · Sophia Excel & Automation

Google Sheets settings in one place: manage with a SETTINGS sheet

Intro: when you want to change behavior without touching code

When you use a Google Sheets receiving appointment system, you’ll often get requests like “For this week only, start the schedule at 8 a.m.,” or “Turn yard slots off for a while.” If you open the Apps Script code and edit constants every time, you risk making mistakes, and the operator has to keep going back to the developer for help.

This post covers how to keep your Google Sheets settings in one place by creating a SETTINGS sheet and managing all settings from a single sheet. With an example SETTINGS sheet table, we’ll go through the full Apps Script code for loadSettings_() that reads these values, the _isSettingEnabled() helper that normalizes yes/no values, a numeric helper for settings like slot interval, how to cache settings with CacheService, and even how to add a menu item to force‑clear the cache (including the shared onOpen structure).

We’ll continue from the previous posts: Google Sheets receiving appointment system sheet structure — Basics Part 1, where we built the sheet structure, and How to auto-generate dock doors in Google Sheets: create 30 doors and 50 yard slots at once, where we filled in the door and yard master data. The goal of this Part 3 is to build a structure where “you leave the code as-is, and just changing values in the SETTINGS sheet changes how the system behaves.” You can copy over the tables and code below and start using them right away without any extra framework.

Designing the SETTINGS sheet: start with three columns — key/value/description

You don’t need a complex design to create a settings sheet in Google Sheets. The key is to build a minimal structure that’s easy for humans to understand and simple for code to work with. In practice, the most reliable pattern is to have three columns like this:

• Column A: KEY (setting key)
• Column B: VALUE (actual value)
• Column C: DESCRIPTION (description)

For example, enter the following in the SETTINGS sheet:

| KEY | VALUE | DESCRIPTION |

|--------------------|------------|----------------------------------------------------------|

| APPT_START_TIME | 09:00 | Appointment start time (24-hour format) |

| APPT_END_TIME | 18:00 | Appointment end time (24-hour format) |

| APPT_SLOT_MINUTES | 30 | Slot interval (minutes) |

| APPT_ENABLE_YARD | TRUE | Whether to use yard slots |

| APPT_TIMEZONE | America/New_York | Base system time zone (change to your local warehouse time) |

| APPT_MAX_APPTS_DAY | 80 | Max appointments per day (example value) |

For the key names we’re using the APPT_ prefix throughout this series. Having a consistent prefix makes it easier to visually pick out “appointment-related settings” as the project grows. The description column isn’t read by the code, but it helps operators quickly understand what each value represents when they open the sheet.

This structure lets you mix numbers, strings, and booleans in a single sheet. Later we’ll convert numeric values (intervals, counts, etc.) using Number() or parseInt(), and we’ll normalize yes/no values via _isSettingEnabled(). We’ll implement these functions step by step in actual code below.

Shared onOpen structure: how to combine code from multiple posts without conflicts

If you put all of the code from this series into one script project, you can easily run into duplicate top-level onOpen functions. In Google Apps Script, if the same top-level function name is declared twice, the last one silently overwrites the earlier ones, and menus from previous parts just disappear.

To avoid this, always define onOpen() only once, and in each post only add small helper functions like add○○Menu_(menu) that plug into the shared onOpen.

• If your project already has onOpen(), do not create a new onOpen(). Instead, just add a helper like below and call it from the existing onOpen().
• If you don’t have onOpen() yet, create the shared version from this post once, and in later posts attach menus with the same pattern (APPT_addSeedMenu_(menu), APPT_addSettingsMenu_(menu), etc.) so everything can coexist without collisions.

You’ll see the full shared onOpen() code in the “Force-clear cache” section below.

Full loadSettings_ implementation: read SETTINGS sheet + defaults + cache

Now to the core part: how to use Apps Script’s loadSettings pattern. The loadSettings_() function reads the SETTINGS sheet into a JavaScript object, stores it in CacheService for a short time, and reuses it on subsequent calls.

The code below does all of the following:

1. Read KEY/VALUE/DESCRIPTION from the SETTINGS sheet
2. Normalize keys to uppercase and build an object
3. Merge in predefined defaults
4. Cache the result with CacheService for a set duration
5. On errors, fall back safely with a clear exception message

Paste this code into Google Sheets → Extensions → Apps Script → Code.gs. It can sit anywhere alongside your other functions, but it’s usually easiest to keep it near your other settings-related constants at the top or bottom of the file. After pasting, just save (⌘S or Ctrl+S). We’ll test it with testLoadSettings_() later.

First, the “change these to your own values” constants and the main loadSettings_() body:

const APPT_SETTINGS_CONFIG = {                         // → Reservation settings constants
  SETTINGS_SHEET_NAME: 'SETTINGS',                    // → SETTINGS sheet name
  CACHE_KEY: 'APPT_SETTINGS_CACHE_V1',                // → Cache key (include version)
  CACHE_SECONDS: 300,                                 // → Cache duration in seconds (e.g., 5 minutes)
  DEFAULTS: {                                         // → Default values if missing
    APPT_START_TIME: '09:00',                         // → Default appointment start time
    APPT_END_TIME: '18:00',                           // → Default appointment end time
    APPT_SLOT_MINUTES: '30',                          // → Default slot interval (minutes, stored as string)
    APPT_ENABLE_YARD: 'TRUE',                         // → Default yard usage
    APPT_TIMEZONE: 'America/New_York',                // → Default time zone
                                                      //    ⚠ Be sure to change this to your warehouse time zone
    APPT_MAX_APPTS_DAY: '80',                         // → Default max appointments per day (example)
  },
};                                                    // 

function loadSettings_() {                            // → Read SETTINGS sheet and return an object
  const cache = CacheService.getScriptCache();        // → Get script cache
  const cached = cache.get(APPT_SETTINGS_CONFIG.CACHE_KEY);  // → Read cached value
  if (cached) {                                       // → If cache exists
    try {                                             // → Try parsing JSON
      return JSON.parse(cached);                      // → Return cached settings object
    } catch (e) {                                     // → If parsing fails
      Logger.log('Settings cache parse error: ' + e); // → Log error
      cache.remove(APPT_SETTINGS_CONFIG.CACHE_KEY);   // → Remove bad cache
    }                                                 // 
  }                                                   // 

  let settings = {};                                  // → Prepare settings object
  try {                                               // → Protect entire sheet read
    const ss = SpreadsheetApp.getActive();            // → Current spreadsheet
    const sheet = ss.getSheetByName(                 // → Find SETTINGS sheet
      APPT_SETTINGS_CONFIG.SETTINGS_SHEET_NAME
    );                                               // 
    if (!sheet) {                                    // → If sheet not found
      Logger.log('Could not find SETTINGS sheet. Using defaults.'); // → Log
      settings = Object.assign({}, APPT_SETTINGS_CONFIG.DEFAULTS); // → Copy defaults
    } else {                                         // → If sheet is found
      const lastRow = sheet.getLastRow();            // → Last row number
      if (lastRow >= 2) {                            // → Check for data rows beyond header
        const range = sheet.getRange(2, 1, lastRow - 1, 3); // → Range A2:C(last row)
        const values = range.getValues();            // → Read as 2D array
        values.forEach((row, idx) => {               // → Loop each row
          const rawKey = String(row[0] || '').trim();  // → Clean key from column A
          const rawValue = String(row[1] || '').trim(); // → Clean value from column B
          if (!rawKey) {                             // → If key is empty
            if (rawValue) {                          // → But value exists
              Logger.log('Row with value but no key: ' + (idx + 2)); // → Warning
            }                                        // 
            return;                                  // → Skip this row
          }                                          // 
          const key = rawKey.toUpperCase();          // → Normalize key to uppercase
          settings[key] = rawValue;                  // → Store setting on object
        });                                          // 
      } else {                                       // → No data rows
        Logger.log('No data in SETTINGS sheet. Using defaults.'); // → Log
      }                                              // 

      settings = Object.assign(                      // → Merge sheet values with defaults
        {},                                          // → Into a new object
        APPT_SETTINGS_CONFIG.DEFAULTS,               // → Copy defaults first
        settings                                     // → Override with sheet values
      );                                             // 
    }                                                // 
  } catch (e) {                                      // → If any exception during read
    // If we quietly fall back to defaults here, then even on permission errors or
    // broken sheet structure, operators won’t notice and appointments will still
    // be accepted with 'default times/default max count'. That is more dangerous.
    Logger.log('Failed to load settings: ' + e);     // → Log root cause
    throw new Error(                                 // → Stop execution
      'Could not read SETTINGS sheet. Check sheet name and permissions. Cause: ' + e.message
    );
  }                                                  // 

  try {                                              // → Try writing settings to cache
    cache.put(                                       // → Write to cache
      APPT_SETTINGS_CONFIG.CACHE_KEY,                // → Cache key
      JSON.stringify(settings),                      // → Store as JSON string
      APPT_SETTINGS_CONFIG.CACHE_SECONDS             // → Cache TTL in seconds
    );                                               // 
  } catch (e) {                                      // → If cache write fails
    Logger.log('Settings cache write error: ' + e);  // → Log but continue
  }                                                  // 

  return settings;                                   // → Return final settings object
}                                                    // 

If running testLoadSettings_() prints the settings object as JSON in the execution log, everything is working.

Here’s the test helper:

function testLoadSettings_() {                       // → Test function for loading settings
  const settings = loadSettings_();                  // → Read SETTINGS
  Logger.log(JSON.stringify(settings));              // → Log full settings
}                                                    // 

After running it, you should see something like { "APPT_START_TIME": "09:00", ... } in the “Execution log”.

Normalizing yes/no: handle booleans with _isSettingEnabled()

Once multiple people start editing the SETTINGS sheet, you’ll quickly see many different ways to express “on”: TRUE, true, yes, Y, 1, etc. If you rely on raw string comparison, typos or variations in spelling can easily cause features to be on or off incorrectly. To prevent this, add a single _isSettingEnabled() helper and use it everywhere to convert all variants into a proper boolean.

The function below behaves as follows:

• Trims whitespace and converts the value to uppercase first.
• Treats TRUE, YES, Y, 1, ON, OK, and the Korean “예”, “네” as true.
• Treats FALSE, NO, N, 0, OFF, and the Korean “아니오” as false.
• For anything else (e.g., TURE, YESS, maybe), logs the value and returns false.

function _isSettingEnabled(value) {                  // → Normalize yes/no style values
  const raw = String(value || '').trim();            // → Avoid null and trim whitespace
  if (!raw) {                                       // → Empty value
    return false;                                   // → Treat as off by default
  }                                                 // 
  const v = raw.toUpperCase();                      // → Normalize to uppercase

  const TRUE_SET = [                                // → Allowed truthy tokens
    'TRUE', 'YES', 'Y', '1', 'ON', 'OK', 'Yes', 'Yes'   // → Various expressions
  ];                                                // 
  const FALSE_SET = [                               // → Allowed falsy tokens
    'FALSE', 'NO', 'N', '0', 'OFF', 'No'          // → Various expressions
  ];                                                // 

  if (TRUE_SET.indexOf(v) !== -1) {                 // → If in truthy set
    return true;                                    // → Return true
  }                                                 // 
  if (FALSE_SET.indexOf(v) !== -1) {                // → If in falsy set
    return false;                                   // → Return false
  }                                                 // 

  Logger.log('Unknown on/off setting value: ' + raw); // → Log unexpected value
  return false;                                     // → Safely treat as false
}                                                   // 

Run testIsSettingEnabled_() to see how different example values are interpreted.

function testIsSettingEnabled_() {                  // → Test boolean normalization
  const samples = ['TRUE', 'yes', 'Yes', '0', 'off', 'maybe', '', '  y  ']; // → Sample values
  samples.forEach((v) => {                          // → For each sample
    Logger.log(v + ' → ' + _isSettingEnabled(v));   // → Log conversion result
  });                                               // 
}                                                   // 

In the log, you should see entries like "maybe → false" and "예 → true" showing how each value is interpreted.

In your actual appointment code, you’ll use it like this:

const settings = loadSettings_();                               // → Read SETTINGS
if (_isSettingEnabled(settings.APPT_ENABLE_YARD)) {             // → Decide if yard is enabled
  // Generate yard-related slots.                             // → Yard feature branch
}

This way, whether the SETTINGS sheet has yes, 예, or 1, they’re all treated identically as “on.”

Safe guards for numeric settings: handling NaN, negatives, and zero

In a receiving appointment system, numeric settings like APPT_SLOT_MINUTES or APPT_MAX_APPTS_DAY feed directly into calculations. If these values are strings or malformed, Number('ABC') becomes NaN and all downstream calculations break. In real-world usage, it’s best to always pair numeric conversions with a validate-and-guard pattern.

Here we’ll build a dedicated helper getApptSlotMinutes_() for the slot interval (minutes) setting. This function converts the value to a number and, if it’s invalid, logs a warning and falls back to a default of 30 minutes.

function getApptSlotMinutes_() {                    // → Safely return slot interval (minutes)
  const settings = loadSettings_();                 // → Read SETTINGS
  const raw = String(settings.APPT_SLOT_MINUTES || '').trim();  // → Clean raw string

  // parseInt will treat '30abc' as 30 and '30.5' as 30. That means bad settings
  // can quietly slip through, so first check that the string has only digits.
  if (!/^\d+$/.test(raw)) {                         // → If non-digit characters are present
    Logger.log('Invalid slot interval: ' + raw + ', using default 30 minutes');
    return 30;                                      // → Safe default (minutes)
  }

  const num = Number(raw);                          // → Safe numeric conversion

  if (!Number.isSafeInteger(num) || num <= 0 || num > 1440) {   // → Prevent <=0 or > 1 day
    Logger.log('Invalid slot interval: ' + raw + ', using default 30 minutes'); // → Warning
    return 30;                                      // → Safe default (minutes)
  }                                                 // 

  return num;                                       // → Return validated value
}                                                   // 

You can test this helper like so:

function testGetSlotMinutes_() {                    // → Test slot interval helper
  const m = getApptSlotMinutes_();                  // → Get slot interval
  Logger.log('Slot interval (minutes): ' + m);      // → Log result
}                                                   // 

• If APPT_SLOT_MINUTES is 40 in the SETTINGS sheet, you’ll get 40 back.
• If APPT_SLOT_MINUTES is empty or set to -10, abc, etc., a warning is logged and the function returns 30.
• Because the helper directly returns the numeric default 30, it doesn’t matter that APPT_SETTINGS_CONFIG.DEFAULTS stores the default as the string '30'; everything still behaves correctly.

For other numeric settings like APPT_MAX_APPTS_DAY, copy this pattern into a new helper such as getApptMaxPerDay_() and adjust the validation rules as needed.

Force-clearing the cache: shared onOpen + SETTINGS menu for instant updates

Using CacheService reduces sheet reads, but it can also feel frustrating when operators change the SETTINGS sheet and then wonder whether the new values have taken effect immediately. To solve this, it helps to add a small function and menu item to force-clear the cache on demand.

As mentioned earlier, this series uses one shared onOpen() combined with multiple “menu adder” helpers. If you already created onOpen() in a previous post (such as the dock door generator), just add a call to APPT_addSettingsMenu_(menu) there. If you don’t yet have onOpen(), you can use the shared example below, and in future posts attach new menus using the same add○○Menu_(menu) pattern to avoid conflicts.

1) Shared onOpen: declare exactly once per project

function onOpen() {                                 // → Automatically runs when sheet opens
  const ui = SpreadsheetApp.getUi();                // → Get UI object
  const menu = ui.createMenu('Reservation Tool');            // → Shared series menu (keep name consistent)

  // If code from earlier parts is already present, their menus are added as well.
  // The function names below must match what earlier parts actually defined —
  // a typo here will only fail the typeof check and the menu will silently disappear.
  if (typeof addApptBaseMenu_ === 'function') {     // → Part 1 basic menu
    addApptBaseMenu_(menu);                         // → Initial setup items
  }
  if (typeof APPT_addSeedMenu_ === 'function') {    // → Part 2 dock/yard seed menu
    APPT_addSeedMenu_(menu);                        // → Create door30/yard50 items
  }

  APPT_addSettingsMenu_(menu);                      // → This part’s SETTINGS menu

  menu.addToUi();                                   // → Add menu to sheet
}                                                   // 

• If your project already has an onOpen(), do not replace it with the code above.
• Keep your existing createMenu('Reservation Tool') logic, and
• Just add the block below inside that onOpen():

    if (typeof APPT_addSettingsMenu_ === 'function') {
      APPT_addSettingsMenu_(menu);
    }

• This way, you only have one onOpen and can freely paste in code from multiple posts without clashing.

2) SETTINGS menu helper + cache clear function

Here’s the full code for the helper that adds the SETTINGS menu item, and the function that actually clears the cache. Place it anywhere in your project; it’s usually convenient to keep it near your other settings-related code.

function APPT_addSettingsMenu_(menu) {              // → Define this part’s SETTINGS menu
  menu.addItem(                                     // → Add a menu item
    'Clear settings cache',                               // → menu label shown in UI
    'clearSettingsCache_'                           // → function name to execute
  );                                                // 
}                                                   // 

function clearSettingsCache_() {                    // → Clear SETTINGS cache
  const cache = CacheService.getScriptCache();      // → Get script cache
  cache.remove(APPT_SETTINGS_CONFIG.CACHE_KEY);     // → Remove settings cache entry
  SpreadsheetApp.getActive()                        // → Current spreadsheet
    .toast('Settings cache cleared. New settings are applied immediately.', 'Notification', 5); // → Bottom-right toast
}                                                   // 

• When you reopen the sheet, you should see “예약 도구 → 설정 캐시 비우기” in the top menu.
• Clicking it should show a toast in the bottom right: “설정 캐시를 비웠습니다. 새 설정이 즉시 반영됩니다.”
• After clearing the cache, the next call to loadSettings_() will re-read the SETTINGS sheet and refresh the cache with the latest values.

Practical tips: running the SETTINGS sheet safely in production

Once your code and structure are in place, operational safeguards become important. Here are a few practices that have helped when using a SETTINGS sheet in real environments:

1. Use data validation

Take advantage of Google Sheets data validation in the SETTINGS sheet. For yes/no values in column B, use dropdowns that only allow “TRUE/FALSE” or “예/아니오”. For numeric settings, apply “number only” rules. This means _isSettingEnabled() and getApptSlotMinutes_() act as a second line of defense, while validation prevents many bad inputs from ever being entered.

1. Record change history

The simplest approach is to use columns D and beyond for “Last modified date, modified by, reason for change” and have humans fill them in manually. For more automation, you can extend the approach from another post, such as Automatically logging change history in Google Sheets | Apps Script Part 9, to detect changes in the SETTINGS sheet and write entries to a dedicated log sheet.

1. Separate test and production sheets

Consider having a test SETTINGS sheet and a production SETTINGS sheet with identical structures: for example, SETTINGS_TEST and SETTINGS. Switch between them by changing only APPT_SETTINGS_CONFIG.SETTINGS_SHEET_NAME for each environment. When you want to try a new rule, point the code at the test sheet, verify behavior thoroughly, then flip it back to the production sheet to minimize confusion in live operations.

1. Protect the sheet and design permissions

Use Google Sheets’ Protect range/sheet feature to lock the SETTINGS sheet and grant edit access on only specific cells to designated operator accounts. If everyone can freely edit settings, they might accidentally clear values or enter ABC into a numeric field, breaking all scheduling calculations. On the other hand, if permissions are too strict, operations become cumbersome. In practice, assigning edit rights to just 1–2 responsible people per site tends to be a good balance.

Conclusion: start by building the SETTINGS backbone

In this post we focused on managing settings via a Google Sheets SETTINGS sheet, covering the KEY/VALUE/DESCRIPTION sheet structure, the full loadSettings_() Apps Script code that reads it, the _isSettingEnabled() helper to normalize boolean-style values, the getApptSlotMinutes_() helper to safely use the slot interval setting, and how to cache the settings with CacheService while adding a shared onOpen-based menu to clear the cache. We also showed how to avoid conflicts when you merge code from other posts in this series by using a single shared onOpen() and separate menu helper functions like APPT_addSettingsMenu_(menu).

The overarching goal is to give you a structure where, in environments like receiving appointment systems where settings change often, you can adjust behavior purely through sheet values without touching the code.

If you want one concrete next step: create a new sheet named SETTINGS in your appointment spreadsheet and set up the three-column backbone — key/value/description — using the example table in this post. Then paste in APPT_SETTINGS_CONFIG, loadSettings_(), _isSettingEnabled(), getApptSlotMinutes_(), plus APPT_addSettingsMenu_(menu) and clearSettingsCache_(). Run testLoadSettings_() and testGetSlotMinutes_() to inspect the logs. Once that’s in place, wiring these settings into your appointment creation and slot generation logic becomes much easier. In the next part, we’ll connect these SETTINGS values to the actual receiving appointment and slot creation logic so that operational rules change immediately when you edit the sheet.