Google Sheets - Super Menu w/ Apps Script

Add a sweet fuckin' menu in Google Sheets that will shave off hours of your life. After all, time is the only resource you can never get back.

Replace fill color (replaceFillColor) - Finds all cells with hex color and replaces the hex color of your choosing Select matching cells (selectMatchingCells) - This one is so tight, you can find all cells that match certain conditions and bulk edit them. Copy (Formula Safe) - this copies formats and formulas of a selection Paste (Formula Safe) - this copies formats and formulas of a selection, formulas references are absolute and aren't transposed (unless they are referenced within the copied selection, and then the self-reference updates). You're welcome!!!

Here's the Apps Script

/**

 * Tech Almanac Tools — utilities under a sub menu:

 *   1. Replace fill color   (replaceFillColor)

 *   2. Select matching cells (selectMatchingCells)

 *   3. Copy (Formula Safe) - this copies formats and formulas of a selection

 *   4. Paste (Formula Safe) - this copies formats and formulas of a selection, formulas references are absolute and aren't transposed (unless they are referenced within the copied selection, and then the self-reference updates). You're welcome!!!

 * "Select matching cells" needs a companion HTML file named "MatchDialog".

 */

function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Tech Almanac Tools')
.addItem('Replace fill color…', 'replaceFillColor')
.addItem('Select matching cells…', 'selectMatchingCells')
.addSeparator()
.addItem('Copy (formula-safe)', 'formulaSafeCopy')
.addItem('Paste (formula-safe)', 'formulaSafePaste')
.addToUi();
}

/* ============================================================
* TOOL 1 — Replace fill color
* ============================================================ */

function replaceFillColor() {
const ui = SpreadsheetApp.getUi();
const sheet = SpreadsheetApp.getActiveSheet();

const findResp = ui.prompt(
'Replace fill color',
'Color to FIND (hex, e.g. #cfecff):',
ui.ButtonSet.OK_CANCEL
);
if (findResp.getSelectedButton() !== ui.Button.OK) return;
const findColor = normalizeHex(findResp.getResponseText());
if (!findColor) {
ui.alert('That doesn\'t look like a valid hex color. Try something like #cfecff.');
return;
}

const replaceResp = ui.prompt(
'Replace fill color',
'Color to REPLACE it with (hex, e.g. #ffffff):',
ui.ButtonSet.OK_CANCEL
);
if (replaceResp.getSelectedButton() !== ui.Button.OK) return;
const replaceColor = normalizeHex(replaceResp.getResponseText());
if (!replaceColor) {
ui.alert('That doesn\'t look like a valid hex color. Try something like #ffffff.');
return;
}

const range = sheet.getDataRange();
const backgrounds = range.getBackgrounds();
const startRow = range.getRow();
const startCol = range.getColumn();

if (matchedA1.length === 0) {
ui.alert('No cells with fill ' + findColor + ' found on "' + sheet.getName() + '".');
return;
}

sheet.setActiveRangeList(sheet.getRangeList(matchedA1));

const confirm = ui.alert(
'Confirm replacement',
'Found ' + matchedA1.length + ' cell(s) with ' + findColor +
' on "' + sheet.getName() + '".\n\nReplace their fill with ' + replaceColor + '?',
ui.ButtonSet.OK_CANCEL
);
if (confirm !== ui.Button.OK) return;

range.setBackgrounds(backgrounds);
SpreadsheetApp.getActive().toast('Replaced ' + matchedA1.length + ' cell(s).', 'Done', 5);
}

/**
* Normalize a user-entered hex string to lowercase 6-digit form (#rrggbb).
* Accepts "#cfecff", "cfecff", "#abc", "abc". Returns null if invalid.
*/
function normalizeHex(input) {
if (!input) return null;
let h = input.trim().toLowerCase().replace(/^#/, '');
if (/^[0-9a-f]{3}$/.test(h)) {
h = h.split('').map(function (ch) { return ch + ch; }).join('');
}
if (/^[0-9a-f]{6}$/.test(h)) return '#' + h;
return null;
}

/* ============================================================
* TOOL 2 — Select matching cells
* ============================================================ */

function selectMatchingCells() {
const html = HtmlService.createHtmlOutputFromFile('MatchDialog')
.setWidth(320)
.setHeight(460);
SpreadsheetApp.getUi().showModalDialog(html, 'Select matching cells');
}

/** Called by the dialog on load, to show which cell is the reference. */
function getActiveCellAddress() {
const sheet = SpreadsheetApp.getActiveSheet();
return sheet.getName() + '!' + sheet.getActiveCell().getA1Notation();
}

/**
* Called by the dialog. `options` is an object of booleans keyed by attribute.
* Selects every cell on the active sheet that matches the reference cell on
* all enabled attributes. Returns { count } or { error }.
*/
function runMatch(options) {
const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getDataRange();
const startRow = range.getRow();
const startCol = range.getColumn();
const numRows = range.getNumRows();
const numCols = range.getNumColumns();

// Reference cell position relative to the data range.
const active = sheet.getActiveCell();
let refR = active.getRow() - startRow;
let refC = active.getColumn() - startCol;
if (refR < 0 || refC < 0 || refR >= numRows || refC >= numCols) {
return { error: 'Select a cell that contains data first, then run this again.' };
}

// Read only the attribute matrices we actually need.
const data = {};
if (options.background) data.background = range.getBackgrounds();
if (options.fontColor) data.fontColor = range.getFontColors();
if (options.fontWeight) data.fontWeight = range.getFontWeights();
if (options.fontStyle) data.fontStyle = range.getFontStyles();
if (options.fontFamily) data.fontFamily = range.getFontFamilies();
if (options.fontSize) data.fontSize = range.getFontSizes();
if (options.numberFormat) data.numberFormat = range.getNumberFormats();
if (options.hAlign) data.hAlign = range.getHorizontalAlignments();
if (options.vAlign) data.vAlign = range.getVerticalAlignments();
if (options.wrap) data.wrap = range.getWraps();

const hasAttr = Object.keys(data).length > 0;
if (!hasAttr && !options.merge) {
return { error: 'Pick at least one condition to match on.' };
}

// Build merge maps: dimensions string per cell + which cells are anchors.
const mergeDims = [];
const isAnchor = [];
for (let r = 0; r < numRows; r++) {
mergeDims[r] = new Array(numCols).fill('1x1');
isAnchor[r] = new Array(numCols).fill(true);
}
const merges = range.getMergedRanges();
merges.forEach(function (m) {
const mr = m.getRow() - startRow;
const mc = m.getColumn() - startCol;
const rows = m.getNumRows();
const cols = m.getNumColumns();
const dim = rows + 'x' + cols;
for (let r = mr; r < mr + rows; r++) {
for (let c = mc; c < mc + cols; c++) {
if (r >= 0 && c >= 0 && r < numRows && c < numCols) {
mergeDims[r][c] = dim;
isAnchor[r][c] = (r === mr && c === mc);
}
}
}
// Snap the reference to its merge anchor if it sits inside this merge.
if (refR >= mr && refR < mr + rows && refC >= mc && refC < mc + cols) {
refR = mr;
refC = mc;
}
});

// Reference values.
const ref = {};
Object.keys(data).forEach(function (k) {
ref[k] = norm(k, data[k][refR][refC]);
});
const refMerge = mergeDims[refR][refC];

// Scan and collect anchor cells that match on every enabled attribute.
const matched = [];
for (let r = 0; r < numRows; r++) {
for (let c = 0; c < numCols; c++) {
if (!isAnchor[r][c]) continue; // skip interior cells of merges
let ok = true;
for (const k in data) {
if (norm(k, data[k][r][c]) !== ref[k]) { ok = false; break; }
}
if (ok && options.merge && mergeDims[r][c] !== refMerge) ok = false;
if (ok) matched.push(sheet.getRange(startRow + r, startCol + c).getA1Notation());
}
}

if (matched.length === 0) return { count: 0 };
sheet.setActiveRangeList(sheet.getRangeList(matched));
return { count: matched.length };
}

/** Normalize values so equivalent formats compare equal (colors are case-folded). */
function norm(key, val) {
if (key === 'background' || key === 'fontColor') return String(val).toLowerCase();
return val;
}

// ── Formula-Safe Copy ────────────────────────────────────────────────────────

function formulaSafeCopy() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const range = sheet.getActiveRange();

// Store the A1 notation and sheet name in script properties
const props = PropertiesService.getScriptProperties();
props.setProperty('FSC_RANGE', range.getA1Notation());
props.setProperty('FSC_SHEET', sheet.getName());

SpreadsheetApp.getUi().alert(
`Copied ${range.getA1Notation()} — now select your destination cell and run Paste (formula-safe).`
);
}

// ── Formula-Safe Paste ───────────────────────────────────────────────────────

function formulaSafePaste() {
const ui = SpreadsheetApp.getUi();
const props = PropertiesService.getScriptProperties();
const srcRangeNotation = props.getProperty('FSC_RANGE');
const srcSheetName = props.getProperty('FSC_SHEET');

if (!srcRangeNotation || !srcSheetName) {
ui.alert('Nothing copied yet — run Copy (formula-safe) first.');
return;
}

const ss = SpreadsheetApp.getActiveSpreadsheet();
const srcSheet = ss.getSheetByName(srcSheetName);
const srcRange = srcSheet.getRange(srcRangeNotation);

// Grab formulas (as strings) and display values separately
const formulas = srcRange.getFormulas(); // raw formula strings, e.g. "=A1+B1"
const values = srcRange.getDisplayValues(); // what the cell shows if not a formula

// Source range bounds
const srcRow = srcRange.getRow();
const srcCol = srcRange.getColumn();
const srcRows = srcRange.getNumRows();
const srcCols = srcRange.getNumColumns();

// Destination anchor
const dest = ss.getActiveSheet().getActiveRange();
const destRow = dest.getRow();
const destCol = dest.getColumn();

// Row/col offset from source to destination
const rowOffset = destRow - srcRow;
const colOffset = destCol - srcCol;

// Regex matches cell addresses like B4, $B$4, B$4, $B4
const cellRef = /(\$?)([A-Za-z]{1,3})(\$?)(\d+)/g;

/**
* If the address falls within the source range, shift it by the paste offset.
* Anchored ($) axes are never shifted, matching normal paste behaviour.
* Addresses outside the source range are left untouched.
*/
function shiftIfInRange(colAnchor, colLetter, rowAnchor, rowNum) {
const col = columnLetterToIndex(colLetter.toUpperCase());
const row = parseInt(rowNum, 10);
if (row >= srcRow && row < srcRow + srcRows &&
col >= srcCol && col < srcCol + srcCols) {
const newCol = colAnchor ? col : col + colOffset;
const newRow = rowAnchor ? row : row + rowOffset;
return (colAnchor ? '$' : '') + columnIndexToLetter(newCol) +
(rowAnchor ? '$' : '') + newRow;
}
return (colAnchor ? '$' : '') + colLetter.toUpperCase() +
(rowAnchor ? '$' : '') + rowNum;
}

// Build payload: rewrite intra-range references, pass external refs through as-is
const payload = formulas.map((row, r) =>
row.map((formula, c) => {
if (formula === '') return values[r][c];
return formula.replace(cellRef, (match, ca, cl, ra, rn) =>
shiftIfInRange(ca, cl, ra, rn)
);
})
);

const destRange = ss.getActiveSheet().getRange(
destRow, destCol, payload.length, payload[0].length
);

// Copy formatting first, then overwrite with values/formulas
srcRange.copyTo(destRange, SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
destRange.setValues(payload);
}

// ── Helpers ──────────────────────────────────────────────────────────────────

function columnLetterToIndex(letters) {
let n = 0;
for (let i = 0; i < letters.length; i++) {
n = n * 26 + (letters.charCodeAt(i) - 64);
}
return n;
}

function columnIndexToLetter(n) {
let s = '';
while (n > 0) {
const rem = (n - 1) % 26;
s = String.fromCharCode(65 + rem) + s;
n = Math.floor((n - 1) / 26);
}
return s;
}

Here's the MarchDialog.html for the Select Matching Cells Modal

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <style>
    body {
      font-family: Arial, Helvetica, sans-serif;
      font-size: 13px;
      color: #202124;
      margin: 12px;
    }
    .ref {
      background: #f1f3f4;
      border-radius: 6px;
      padding: 8px 10px;
      margin-bottom: 12px;
      font-size: 12px;
      color: #5f6368;
    }
    .ref b { color: #202124; }
    label {
      display: block;
      padding: 4px 0;
      cursor: pointer;
    }
    label input { margin-right: 8px; }
    .toggle {
      margin-bottom: 6px;
      padding-bottom: 6px;
      border-bottom: 1px solid #e8eaed;
    }
    .toggle a {
      font-size: 12px;
      color: #1a73e8;
      text-decoration: none;
      cursor: pointer;
    }
    .toggle a:hover { text-decoration: underline; }
    .buttons {
      margin-top: 14px;
      display: flex;
      gap: 8px;
    }
    button {
      font-size: 13px;
      padding: 7px 14px;
      border-radius: 6px;
      border: 1px solid #dadce0;
      background: #fff;
      cursor: pointer;
    }
    button.primary {
      background: #1a73e8;
      color: #fff;
      border-color: #1a73e8;
    }
    button:disabled { opacity: 0.5; cursor: default; }
    #status { margin-top: 12px; font-size: 12px; min-height: 16px; }
    #status.err { color: #c5221f; }
    #status.ok { color: #188038; }
  </style>
</head>
<body>
  <div class="ref">
    Matching against reference cell: <b id="refCell">…</b>
  </div>

  <div class="toggle">
    <a href="#" id="toggleAll" onclick="toggleAll(); return false;">Select all</a>
  </div>

  <div id="opts">
    <label><input type="checkbox" id="background" checked> Fill color</label>
    <label><input type="checkbox" id="fontColor"> Font color</label>
    <label><input type="checkbox" id="fontWeight"> Bold (font weight)</label>
    <label><input type="checkbox" id="fontStyle"> Italic (font style)</label>
    <label><input type="checkbox" id="fontFamily"> Font family</label>
    <label><input type="checkbox" id="fontSize"> Font size</label>
    <label><input type="checkbox" id="numberFormat"> Number format</label>
    <label><input type="checkbox" id="hAlign"> Horizontal alignment</label>
    <label><input type="checkbox" id="vAlign"> Vertical alignment</label>
    <label><input type="checkbox" id="wrap"> Text wrap</label>
    <label><input type="checkbox" id="merge"> Merge size</label>
  </div>

  <div class="buttons">
    <button class="primary" id="run" onclick="run()">Select matching cells</button>
    <button onclick="google.script.host.close()">Cancel</button>
  </div>

  <div id="status"></div>

  <script>
    var KEYS = ['background','fontColor','fontWeight','fontStyle','fontFamily',
                'fontSize','numberFormat','hAlign','vAlign','wrap','merge'];

    google.script.run.withSuccessHandler(function (addr) {
      document.getElementById('refCell').textContent = addr;
    }).getActiveCellAddress();

    function setStatus(msg, cls) {
      var s = document.getElementById('status');
      s.textContent = msg;
      s.className = cls || '';
    }

    function syncToggleLabel() {
      var allChecked = KEYS.every(function (k) {
        return document.getElementById(k).checked;
      });
      document.getElementById('toggleAll').textContent = allChecked ? 'Select none' : 'Select all';
    }
    KEYS.forEach(function (k) {
      document.getElementById(k).addEventListener('change', syncToggleLabel);
    });
    syncToggleLabel();

    function toggleAll() {
      var allChecked = KEYS.every(function (k) {
        return document.getElementById(k).checked;
      });
      KEYS.forEach(function (k) { document.getElementById(k).checked = !allChecked; });
      syncToggleLabel();
    }

    function run() {
      var options = {};
      KEYS.forEach(function (k) { options[k] = document.getElementById(k).checked; });

      var btn = document.getElementById('run');
      btn.disabled = true;
      setStatus('Scanning…');

      google.script.run
        .withSuccessHandler(function (result) {
          btn.disabled = false;
          if (result.error) { setStatus(result.error, 'err'); return; }
          if (result.count === 0) { setStatus('No matching cells found.', 'err'); return; }
          setStatus('Selected ' + result.count + ' cell(s). Closing…', 'ok');
          setTimeout(google.script.host.close, 700);
        })
        .withFailureHandler(function (err) {
          btn.disabled = false;
          setStatus(err.message || String(err), 'err');
        })
        .runMatch(options);
    }
  </script>
</body>
</html>