Bootstrapping Tools

Search snippets

Append an Object as a Sheet Row


function appendObjectAsRow(sheet, obj) {
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  var row = headers.map(function (h) {
    return obj.hasOwnProperty(h) ? obj[h] : '';
  });
  sheet.appendRow(row);
  return sheet.getLastRow();
}
// appendObjectAsRow(sheet, { name: 'Ada', email: 'ada@example.com' });

Learn More →

Auto-Sorting Your Google Sheet


function sortSheet(sheet, column, direction) {
  sheet.sort(column, new RegExp('asc', 'ig').test(direction) ? true : false)
}
// sortSheet(sheet, 2, 'Ascending')

Learn More →

Auto-Sorting a Specific Google Sheet Range


// Sort by Column B ascending
range.sort(2)

// Sort by Column B ascending and then Column A ascending
range.sort([2, 1])

// Sort by Column B descending and then Column A ascending
range.sort([{column: 2, ascending: false}, {column: 1, ascending: true}])

Learn More →

Basic REST API GET Call Using JSON


function basicJsonApiGetRequest(url) {
  const result = JSON.parse(UrlFetchApp.fetch(url, {method: 'get'}).getContentText())
  return result
}
// basicJsonApiGetRequest('https://rickandmortyapi.com/api')

Learn More →

Basic REST API POST Request Using JSON


function basicJsonApiPostRequest(url, payload) {
  const result = JSON.parse(UrlFetchApp.fetch(url, {headers: {"Content-Type": "application/json"}, method: 'post', payload: payload}).getContentText())
  return result
}
// basicJsonApiPostRequest('https://dummy.restapiexample.com/api/v1/create', {name:"test",salary:"123",age:"23"})

Learn More →

Basic REST API POST Request Using XML


function basicXmlApiPostRequest(url, payload) {
  const result = XmlService.parse(UrlFetchApp.fetch(url, {headers: {"Content-Type": "application/xml"}, method: 'post', payload: payload}))
  console.log(result.getRootElement().getChildren().map(element => element.getText()))
  return result
}

Learn More →

Basic REST API Request Call Using XML


function basicXmlApiGetRequest(url) {
  const result = XmlService.parse(UrlFetchApp.fetch(url, {headers: {"Content-Type": "application/xml"}, method: 'get'}))
  console.log(result.getRootElement().getChildren().map(element => element.getText()))
  return result
}

Learn More →

Build a Header → Column Index Map


function buildHeaderMap(sheet, headerRow) {
  headerRow = headerRow || 1;
  var lastCol = sheet.getLastColumn();
  if (lastCol < 1) return {};
  var headers = sheet.getRange(headerRow, 1, 1, lastCol).getValues()[0];
  var map = {};
  for (var i = 0; i < headers.length; i++) {
    var name = String(headers[i] == null ? '' : headers[i]).trim();
    if (!name) continue;
    if (map.hasOwnProperty(name)) {
      throw new Error('buildHeaderMap: duplicate header "' + name + '"');
    }
    map[name] = i + 1; // 1-based column index for getRange
  }
  return map;
}
// var cols = buildHeaderMap(sheet); sheet.getRange(2, cols.email).getValue();

Learn More →

Build a Query String for UrlFetch


function buildQueryString(params) {
  params = params || {};
  var parts = [];
  Object.keys(params).forEach(function (key) {
    var v = params[key];
    if (v == null || v === '') return;
    parts.push(encodeURIComponent(key) + '=' + encodeURIComponent(String(v)));
  });
  return parts.length ? '?' + parts.join('&') : '';
}
// buildQueryString({ q: 'ada love', page: 2 }) // → '?q=ada%20love&page=2'

Learn More →

Cache JSON with a TTL


function cacheGetJson(key) {
  var raw = CacheService.getScriptCache().get(key);
  if (raw == null) return null;
  return JSON.parse(raw);
}

function cachePutJson(key, value, ttlSeconds) {
  ttlSeconds = ttlSeconds || 600;
  CacheService.getScriptCache().put(key, JSON.stringify(value), ttlSeconds);
}
// cachePutJson('rates', rates, 300);
// var rates = cacheGetJson('rates');

Learn More →

Claim the Next Queue Row


function claimNextQueueRow(sheet, statusHeader, claimedBy, options) {
  options = options || {};
  var pending = options.pendingStatus || 'pending';
  var claimed = options.claimedStatus || 'claimed';
  var headerRow = options.headerRow || 1;
  return withDocumentLock(function () {
    var lastCol = sheet.getLastColumn();
    var lastRow = sheet.getLastRow();
    if (lastRow <= headerRow) return null;
    var headers = sheet.getRange(headerRow, 1, 1, lastCol).getValues()[0];
    var statusCol = headers.indexOf(statusHeader);
    if (statusCol === -1) throw new Error('claimNextQueueRow: missing status header "' + statusHeader + '"');
    var values = sheet.getRange(headerRow + 1, 1, lastRow - headerRow, lastCol).getValues();
    for (var i = 0; i < values.length; i++) {
      if (String(values[i][statusCol]).toLowerCase() !== String(pending).toLowerCase()) continue;
      var row = headerRow + 1 + i;
      sheet.getRange(row, statusCol + 1).setValue(claimed);
      if (options.claimedByHeader) {
        var byCol = headers.indexOf(options.claimedByHeader);
        if (byCol !== -1) sheet.getRange(row, byCol + 1).setValue(claimedBy || Session.getActiveUser().getEmail());
      }
      if (options.claimedAtHeader) {
        var atCol = headers.indexOf(options.claimedAtHeader);
        if (atCol !== -1) sheet.getRange(row, atCol + 1).setValue(new Date());
      }
      var obj = { _row: row };
      for (var c = 0; c < headers.length; c++) {
        var key = String(headers[c] == null ? '' : headers[c]).trim();
        if (!key) continue;
        obj[key] = values[i][c];
      }
      obj[statusHeader] = claimed;
      return obj;
    }
    return null;
  });
}
// var job = claimNextQueueRow(sheet, 'status', null, { claimedByHeader: 'claimedBy', claimedAtHeader: 'claimedAt' });

Learn More →

Clear a Sheet but Keep Header Row


function clearSheetKeepHeaders(sheet, headerRows) {
  headerRows = headerRows || 1;
  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();
  if (lastRow <= headerRows || lastCol < 1) return;
  sheet.getRange(headerRows + 1, 1, lastRow - headerRows, lastCol).clearContent();
}
// clearSheetKeepHeaders(SpreadsheetApp.getActiveSheet(), 1);

Learn More →

Confirm a Dangerous Action (Yes/No)


function confirmDangerousAction(title, prompt) {
  var ui = SpreadsheetApp.getUi();
  var result = ui.alert(
    title || 'Confirm',
    prompt || 'Are you sure?',
    ui.ButtonSet.YES_NO
  );
  return result === ui.Button.YES;
}
// if (!confirmDangerousAction('Clear sheet', 'Wipe all data rows?')) return;

Learn More →

Convert Sheet Values to CSV Text


function escapeCsvCell(value) {
  var s = value == null ? '' : String(value);
  if (/[",\n\r]/.test(s)) {
    s = '"' + s.replace(/"/g, '""') + '"';
  }
  return s;
}

function valuesToCsv(values) {
  return values.map(function (row) {
    return row.map(escapeCsvCell).join(',');
  }).join('\n');
}
// var csv = valuesToCsv(sheet.getDataRange().getValues());

Learn More →

Converting Columns between a Number or a Letter


function convertColumn(target, value) {
    if (target == 'letter') {
        let temp, letter = ''
        while (value > 0) {
            temp = (value - 1) % 26
            letter = String.fromCharCode(temp + 65) + letter
            value = (value - temp - 1) / 26
        }
        return letter
    } else if (target == 'number') {
        let column = 0, length = value.length;
        for (i=0; i<length; i++) {
            column += (value.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
        }
        return column
    } else {
        return 0
    }
}

Learn More →

Converting JSON into a flat Array


function convertJsonToArray(object) {
    return new Array(Object.keys(object), Object.values(object))
}

Learn More →

Converting an Array into an Object Array


function convertArrayToObjectArray(keys, data) {
  let jsonArray = []
  for(let item in data) {
      let object = {}
      let row = data[item]
      for(let value in keys) {
        let key = keys[value]
        object[key] = row[value]
      }
      jsonArray.push(object)
  }
  return jsonArray
}

Learn More →

Converting an Object Array into a flat Array


function convertObjectArrayToArray(objectArray) {
    const finalArray = []
    for(let item in objectArray) {
        let object = objectArray[item]
        if (item == 0) {
            finalArray.push(Object.keys(object))
        }
        let array = []
        for(let key in object) {
            array.push(object[key])
        }
        finalArray.push(array)
    }
    return finalArray
}

Learn More →

Copy Values To External Sheet


function copyValuesToExternalSheet(targetId, tabName, values, opts) {
  opts = opts || {};
  var headerRows = opts.headerRows == null ? 1 : opts.headerRows;
  var ss = SpreadsheetApp.openById(targetId);
  // Get or create the destination tab
  var sheet = ss.getSheetByName(tabName) || ss.insertSheet(tabName);
  if (opts.headers && sheet.getLastRow() === 0) {
    sheet.getRange(1, 1, 1, opts.headers.length).setValues([opts.headers]);
  }
  // Clear old data below the header row(s); headers and formatting stay
  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();
  if (lastRow > headerRows && lastCol > 0) {
    sheet.getRange(headerRows + 1, 1, lastRow - headerRows, lastCol).clearContent();
  }
  if (!values || !values.length) return 0;
  var numRows = values.length;
  var numCols = values[0].length;
  // Grow the tab if the block is bigger than the grid
  var needRows = headerRows + numRows - sheet.getMaxRows();
  if (needRows > 0) sheet.insertRowsAfter(sheet.getMaxRows(), needRows);
  var needCols = numCols - sheet.getMaxColumns();
  if (needCols > 0) sheet.insertColumnsAfter(sheet.getMaxColumns(), needCols);
  sheet.getRange(headerRows + 1, 1, numRows, numCols).setValues(values);
  return numRows;
}

Learn More →

Copy a Template Sheet Tab


function copyTemplateSheet(ss, templateName, newName, options) {
  options = options || {};
  var template = ss.getSheetByName(templateName);
  if (!template) throw new Error('copyTemplateSheet: missing template "' + templateName + '"');
  if (ss.getSheetByName(newName)) throw new Error('copyTemplateSheet: sheet already exists "' + newName + '"');
  var copy = template.copyTo(ss).setName(newName);
  if (options.clearDataRows !== false) {
    var lastRow = copy.getLastRow();
    if (lastRow > 1) copy.getRange(2, 1, lastRow - 1, copy.getLastColumn()).clearContent();
  }
  if (options.createdHeader) {
    var headers = copy.getRange(1, 1, 1, copy.getLastColumn()).getValues()[0];
    var idx = headers.indexOf(options.createdHeader);
    if (idx !== -1) copy.getRange(2, idx + 1).setValue(new Date());
  }
  return copy;
}
// copyTemplateSheet(ss, 'Client Template', 'Acme — 2026-09', { createdHeader: 'Created' });

Learn More →

Create a Custom Menu Option


function onOpen() {
  // FormApp.getUi() // For Google Forms
  // DocumentApp.getUi() // For Google Docs
  // SlidesApp.getUi() // For Google Slides
  SpreadsheetApp.getUi() // For Google Sheets
    .createMenu('Custom Menu Option')
    .addItem('Option One', 'functionName')
    .addToUi()
}

Learn More →

Create a Daily Time-Driven Trigger


function createDailyTrigger(handlerName, hour) {
  hour = (hour == null) ? 6 : hour;
  ScriptApp.newTrigger(handlerName)
    .timeBased()
    .everyDays(1)
    .atHour(hour)
    .create();
}
// createDailyTrigger('runNightlySync', 2);

Learn More →

Creating Backup Files of a Google Sheet


function createLogFile() {
  let snapshot = createSnapshot()
  let sheetName = snapshot.sheetName
  let dataBackup = snapshot.sheetData
  let filename = `'${sheetName}' Snapshot - ${Utilities.formatDate(new Date(), 'GMT', 'yyyy-MM-dd HH:mm')}`
  let log_file = SpreadsheetApp.create(filename);
  log_file.getSheetByName('Sheet1').setName(`${sheetName} Snapshot`).getRange(1, 1, dataBackup.length, dataBackup[0].length).setValues(dataBackup);
  DriveApp.getFileById(log_file.getId()).moveTo(getDataBackupFolder())
}

Learn More →

Debounce Or Skip


function debounceOrSkip(actionKey, ttlSeconds) {
  ttlSeconds = ttlSeconds == null ? 5 : ttlSeconds;
  var cache = CacheService.getScriptCache();
  var token = 'debounce:' + actionKey;
  if (cache.get(token)) return false;
  cache.put(token, '1', Math.max(1, Math.min(ttlSeconds, 21600)));
  return true;
}
// if (!debounceOrSkip('onEdit:Status', 3)) return; // quiet window

Learn More →

Delete Triggers by Handler Name


function deleteTriggersByHandler(handlerName) {
  var triggers = ScriptApp.getProjectTriggers();
  var removed = 0;
  for (var i = 0; i < triggers.length; i++) {
    if (triggers[i].getHandlerFunction() === handlerName) {
      ScriptApp.deleteTrigger(triggers[i]);
      removed++;
    }
  }
  return removed;
}
// deleteTriggersByHandler('runNightlySync');

Learn More →

Email Yourself an Error Alert


function emailErrorAlert(subject, err, context) {
  var to = Session.getActiveUser().getEmail();
  if (!to) throw new Error('emailErrorAlert: no active user email');
  var msg = (err && err.message) ? err.message : String(err);
  var stack = (err && err.stack) ? String(err.stack).substring(0, 800) : '';
  var ctx = '';
  if (context != null) {
    try { ctx = JSON.stringify(context).substring(0, 500); }
    catch (e) { ctx = String(context).substring(0, 500); }
  }
  var body = [
    'Apps Script error alert',
    '',
    'Message: ' + msg,
    stack ? ('Stack:\n' + stack) : '',
    ctx ? ('Context: ' + ctx) : '',
    '',
    'Time: ' + new Date().toISOString()
  ].filter(Boolean).join('\n');
  GmailApp.sendEmail(to, subject || 'Apps Script error', body);
}
// try { nightlySync(); } catch (e) { emailErrorAlert('nightlySync failed', e, { rows: 42 }); throw e; }

Learn More →

Ensure Header Row Exists


function ensureHeaders(sheet, expectedHeaders) {
  expectedHeaders = expectedHeaders || [];
  if (!expectedHeaders.length) throw new Error('ensureHeaders: expectedHeaders required');
  var lastCol = Math.max(sheet.getLastColumn(), expectedHeaders.length);
  var existing = lastCol < 1 ? [] : sheet.getRange(1, 1, 1, lastCol).getValues()[0];
  var hasAny = existing.some(function (c) { return c !== '' && c !== null; });
  if (!hasAny) {
    sheet.getRange(1, 1, 1, expectedHeaders.length).setValues([expectedHeaders]);
    sheet.setFrozenRows(1);
    return { created: true, headers: expectedHeaders.slice() };
  }
  var missing = expectedHeaders.filter(function (h) { return existing.indexOf(h) === -1; });
  if (missing.length) {
    throw new Error('ensureHeaders: missing columns: ' + missing.join(', '));
  }
  return { created: false, headers: existing.slice(0, expectedHeaders.length) };
}
// ensureHeaders(sheet, ['email', 'name', 'status']);

Learn More →

Escape HTML for HtmlService


function escapeHtml(s) {
  return String(s == null ? '' : s)
    .replace(/&/g, '&amp;')
    .replace(/</g, '&lt;')
    .replace(/>/g, '&gt;')
    .replace(/"/g, '&quot;')
    .replace(/'/g, '&#39;');
}
// escapeHtml('<Ada & co>') // → '&lt;Ada &amp; co&gt;'

Learn More →

Extract a Drive File ID from a URL


function extractDriveFileId(input) {
  if (input == null) return '';
  var s = String(input).trim();
  if (!s) return '';
  if (/^[a-zA-Z0-9_-]{25,}$/.test(s) && s.indexOf('/') === -1 && s.indexOf('?') === -1) {
    return s;
  }
  var patterns = [
    /\/file\/d\/([a-zA-Z0-9_-]+)/,
    /\/folders\/([a-zA-Z0-9_-]+)/,
    /\/spreadsheets\/d\/([a-zA-Z0-9_-]+)/,
    /\/document\/d\/([a-zA-Z0-9_-]+)/,
    /\/presentation\/d\/([a-zA-Z0-9_-]+)/,
    /[?&]id=([a-zA-Z0-9_-]+)/,
    /open\?id=([a-zA-Z0-9_-]+)/
  ];
  for (var i = 0; i < patterns.length; i++) {
    var m = s.match(patterns[i]);
    if (m && m[1]) return m[1];
  }
  return '';
}
// extractDriveFileId('https://drive.google.com/file/d/1AbC…/view') // → '1AbC…'

Learn More →

Fill Html Template


function fillHtmlTemplate(html, values, opts) {
  opts = opts || {};
  var onMissing = opts.onMissing || 'blank'; // 'blank' | 'throw'
  values = values || {};
  function escapeHtml(s) {
    return String(s == null ? '' : s)
      .replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;')
      .replace(/"/g, '&quot;').replace(/'/g, '&#39;');
  }
  // {{{token}}} = raw HTML; {{token}} = escaped
  return String(html).replace(/\{\{\{([^{}]+)\}\}\}|\{\{([^{}]+)\}\}/g, function (_, rawKey, escKey) {
    var key = String(rawKey || escKey).trim();
    var has = Object.prototype.hasOwnProperty.call(values, key);
    if (!has) {
      if (onMissing === 'throw') throw new Error('fillHtmlTemplate: missing key "' + key + '"');
      return '';
    }
    var val = values[key];
    if (rawKey) return String(val == null ? '' : val); // raw / unescaped
    return escapeHtml(val);
  });
}
// fillHtmlTemplate('<p>Hi {{name}}</p>', { name: 'Ada <3' });

Learn More →

Filter Out Blank Rows


function filterOutBlankRows(data, targetColumnNum) {
    const filteredData = data.filter((row) => row[targetColumnNum-1] !== "")
    return filteredData
}
// filterOutBlankRows([['100', 'a'], ['101','b'], ['','c'], ['103','d']], 1)

Learn More →

Filter Out Rows Using Regex


function filterOutWithRegex(data, targetColumn, regexPattern, regexOptions) {
    // For targetColumn, start from position 0
    // For help with regex, try regex101.com
    const regex = new RegExp(regexPattern, regexOptions)
    const filteredData = data.filter((row) => {return regex.test(row[targetColumn]) == false})
    return filteredData
}
// filterOutWithRegex([[1,'hi','HELLO'],[2,'bye','goodbye']], 2, /hello/, 'ig')

Learn More →

Find Column Number By Column Header Name


function getColumnByName(sheet, column_name, header_row) {
    var data = sheet.getDataRange().getValues();
    var col_index = data[header_row-1].indexOf(column_name);
    return col_index+1
}
// getColumnByName(sheet, 'id', 1)

Learn More →

Find The Difference Between Two Arrays


function diffArrays(array1, array2) {
    array1 = array1.flat(Infinity), array2 = array2.flat(Infinity)
    return [...new Set([array1.filter(x => !array2.includes(x)), array2.filter(x => !array1.includes(x))].flat())]
}
// diffArrays([1,2,3],[3,4,5])

Learn More →

Find the Max Length of Nested Arrays


function findMaxLengthOfNestedArrays(nestedArray) {
    return Math.max(...nestedArray.map(row => row.length))
}
// findMaxLengthOfNestedArrays([[1,1,1],[2,2]])

Learn More →

For Loop Using Dynamic Set


function forLoopItems(data) {
    for(let item in data) {
        console.log(data[item])
    }
}
// forLoopItems([1,2,3,4,5,6,7])

Learn More →

For Loop Using Increments


function forLoopIncremental(data, start, end) {
for(let i = start; i < end; i++) {
    console.log(data[i])
}
}
// forLoopIncremental([1,2,3,4,5], 2, 4)

Learn More →

Format in Spreadsheet Timezone


function formatInSpreadsheetTimezone(date, pattern) {
  if (date == null) return '';
  pattern = pattern || 'yyyy-MM-dd HH:mm:ss';
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var tz = (ss && ss.getSpreadsheetTimeZone()) || Session.getScriptTimeZone();
  return Utilities.formatDate(date instanceof Date ? date : new Date(date), tz, pattern);
}
// Logger.log(formatInSpreadsheetTimezone(new Date())); // matches the grid

Learn More →

Get Data from a Sheet


function getSheetData() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  let sheet = spreadsheet.getSheetByName('TARGET_SHEET_NAME')
  let sheetData = sheet.getDataRange().getValues()
  return sheetData
}

Learn More →

Get UI Safe


function getUiSafe() {
  try {
    return SpreadsheetApp.getUi();
  } catch (e) {
    return null;
  }
}
// var ui = getUiSafe(); if (ui) ui.createMenu('Ops').addItem('Run', 'runJob').addToUi();

Learn More →

Get Unique Values from Array


function createUniqueArray(data) {
    return [...new Set(data)]
}
// createUniqueArray([1,1,2,3,4,5,5])

Learn More →

Get or Create a Drive Folder by Name


function getOrCreateFolder(parent, name) {
  var folders = parent.getFoldersByName(name);
  if (folders.hasNext()) {
    return folders.next();
  }
  return parent.createFolder(name);
}
// getOrCreateFolder(DriveApp.getRootFolder(), 'Exports');

Learn More →

Get or Create a Nested Drive Folder Path


function getOrCreateFolderPath(parent, path) {
  if (!parent) throw new Error('getOrCreateFolderPath: parent folder required');
  var parts = String(path || '')
    .split(/[/\\]+/)
    .map(function (p) { return p.trim(); })
    .filter(Boolean);
  var folder = parent;
  for (var i = 0; i < parts.length; i++) {
    var name = parts[i];
    var kids = folder.getFoldersByName(name);
    folder = kids.hasNext() ? kids.next() : folder.createFolder(name);
  }
  return folder;
}
// getOrCreateFolderPath(DriveApp.getFolderById(ID), 'Exports/2026/09');

Learn More →

Get or Create a Sheet by Name


function getOrCreateSheet(ss, name) {
  var sheet = ss.getSheetByName(name);
  if (!sheet) {
    sheet = ss.insertSheet(name);
  }
  return sheet;
}
// getOrCreateSheet(SpreadsheetApp.getActiveSpreadsheet(), 'Import');

Learn More →

Get the Active Row as an Object


function getActiveRowAsObject(sheet, headerRow) {
  sheet = sheet || SpreadsheetApp.getActiveSheet();
  headerRow = headerRow || 1;
  var row = sheet.getActiveRange().getRow();
  if (row <= headerRow) throw new Error('getActiveRowAsObject: active row is in the header');
  var lastCol = sheet.getLastColumn();
  if (lastCol < 1) return {};
  var headers = sheet.getRange(headerRow, 1, 1, lastCol).getValues()[0];
  var values = sheet.getRange(row, 1, 1, lastCol).getValues()[0];
  var obj = { _row: row };
  for (var i = 0; i < headers.length; i++) {
    var key = String(headers[i] == null ? '' : headers[i]).trim();
    if (!key) continue;
    obj[key] = values[i];
  }
  return obj;
}
// var lead = getActiveRowAsObject(); // { _row: 12, email: 'ada@…', status: 'new' }

Learn More →

Get the Last Data Row in a Column


function getLastDataRow(sheet, column) {
  column = column || 1;
  var values = sheet.getRange(1, column, sheet.getMaxRows(), 1).getValues();
  for (var i = values.length - 1; i >= 0; i--) {
    var cell = values[i][0];
    if (cell !== '' && cell !== null) {
      return i + 1;
    }
  }
  return 0;
}
// getLastDataRow(sheet, 1); // last non-blank in column A

Learn More →

Group Objects By Key


function groupObjectsByKey(rows, keyOrFn) {
  var out = {};
  if (!rows || !rows.length) return out;
  var getKey = typeof keyOrFn === 'function'
    ? keyOrFn
    : function (row) { return row[keyOrFn]; };
  rows.forEach(function (row) {
    var key = getKey(row);
    // Blank / missing keys become '' so they still land in one bucket
    if (key == null) key = '';
    key = String(key);
    if (!out[key]) out[key] = [];
    out[key].push(row);
  });
  return out;
}
// var byClient = groupObjectsByKey(rows, 'Client');

Learn More →

HTML Table From Objects


function htmlTableFromObjects(rows, columns) {
  function esc_(s) {
    return String(s == null ? '' : s)
      .replace(/&/g, '&amp;').replace(/</g, '&lt;')
      .replace(/>/g, '&gt;').replace(/"/g, '&quot;').replace(/'/g, '&#39;');
  }
  // … build <table> from rows + columns …
}
// var html = htmlTableFromObjects(rows, [{key:'name',label:'Name'}, 'email']);

Learn More →

Hash String SHA-256


function hashStringSha256(text) {
  var bytes = Utilities.computeDigest(
    Utilities.DigestAlgorithm.SHA_256,
    text == null ? '' : String(text),
    Utilities.Charset.UTF_8
  );
  return bytes.map(function (b) {
    var v = (b < 0 ? b + 256 : b).toString(16);
    return v.length === 1 ? '0' + v : v;
  }).join('');
}
// var key = 'lead:' + hashStringSha256(email); // cache/dedupe without raw PII

Learn More →

How To Get All Sheets From A Google Sheet Using Apps Script


function getAllSheets() {
    const spreadsheet = SpreadsheetApp.openByUrl(`${YOUR_SPREADSHEET_URL}`)
    const sheets = spreadsheet.getSheets()
    return sheets
}

Learn More →

HtmlService Template Include Helper


function includeHtml(filename) {
  return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

function openIframeDialog(title, width, height) {
  var html = HtmlService.createTemplateFromFile('Dialog')
    .evaluate()
    .setWidth(width || 480)
    .setHeight(height || 360);
  SpreadsheetApp.getUi().showModalDialog(html, title || 'Dialog');
}
// In Dialog.html: <?!= includeHtml('Styles'); ?>

Learn More →

Injecting Data Into an Array using Splice


// Array.splice(starting_position, positions_to_delete, new_value)
// injecting a new value
yourArray.splice(2,0,"new value")
// replacing an existing value
yourArray.splice(0,1,"new value")

Learn More →

Is Within Business Hours


function isWithinBusinessHours(startHHmm, endHHmm, opts) {
  opts = opts || {};
  var ss = opts.spreadsheet || (typeof SpreadsheetApp !== 'undefined' ? SpreadsheetApp.getActive() : null);
  var tz = (ss && ss.getSpreadsheetTimeZone && ss.getSpreadsheetTimeZone())
    || Session.getScriptTimeZone()
    || 'America/New_York';
  var when = opts.when || new Date();
  // Weekday: 1=Mon … 7=Sun in spreadsheet timezone (Utilities 'u')
  var weekday = Number(Utilities.formatDate(when, tz, 'u'));
  var allowed = opts.weekdays || [1, 2, 3, 4, 5];
  if (allowed.indexOf(weekday) === -1) return false;
  var mins = function (hhmm) {
    var p = String(hhmm).split(':');
    return Number(p[0]) * 60 + Number(p[1] || 0);
  };
  var nowMins = Number(Utilities.formatDate(when, tz, 'H')) * 60
    + Number(Utilities.formatDate(when, tz, 'm'));
  var start = mins(startHHmm);
  var end = mins(endHHmm);
  // Half-open window [start, end) so 17:00 is "closed"
  if (end <= start) return nowMins >= start || nowMins < end; // overnight window
  return nowMins >= start && nowMins < end;
}
// if (!isWithinBusinessHours('09:00', '17:00')) return;

Learn More →

JSON OK / ERR helpers for Web Apps


function jsonOk(data) {
  return ContentService
    .createTextOutput(JSON.stringify({ ok: true, data: data }))
    .setMimeType(ContentService.MimeType.JSON);
}
function jsonErr(message, code) {
  return ContentService
    .createTextOutput(JSON.stringify({
      ok: false,
      error: message,
      code: code == null ? 400 : code
    }))
    .setMimeType(ContentService.MimeType.JSON);
}
// return jsonOk({ id: rowId });

Learn More →

Lookup The Matching Row Index Using a Search Term


function findRowIndexByTerm(lookupSheet, columnIndex, lookupValue) {
  let values = lookupSheet.getDataRange().getValues()
  let index = values.findIndex(row => row[columnIndex] === lookupValue)
  return index + 1
}

Learn More →

Mask Secret


function maskSecret(secret, keepStart, keepEnd) {
  keepStart = keepStart == null ? 4 : keepStart;
  keepEnd = keepEnd == null ? 4 : keepEnd;
  if (secret == null) return '';
  var s = String(secret);
  if (s.length <= keepStart + keepEnd) return s.replace(/./g, '•');
  return s.slice(0, keepStart) + '…' + s.slice(s.length - keepEnd);
}
// Logger.log('key=' + maskSecret(props.getProperty('API_KEY'))); // abcd…wxyz

Learn More →

Move a Row to Another Sheet


function moveRowToSheet(sourceSheet, rowNumber, targetSheet) {
  if (rowNumber < 2) throw new Error('moveRowToSheet: refuse to move header row');
  var lastCol = sourceSheet.getLastColumn();
  if (lastCol < 1) throw new Error('moveRowToSheet: source sheet has no columns');
  var values = sourceSheet.getRange(rowNumber, 1, 1, lastCol).getValues()[0];
  targetSheet.appendRow(values);
  sourceSheet.deleteRow(rowNumber);
  return targetSheet.getLastRow();
}
// moveRowToSheet(active, 5, archiveSheet);

Learn More →

Normalize an Email Address


function normalizeEmail(email) {
  if (email == null) return '';
  return String(email).trim().toLowerCase();
}
// normalizeEmail(' Ada@Example.com ') // → 'ada@example.com'

Learn More →

Open By URL Or Id


function openByUrlOrId(urlOrId) {
  var s = String(urlOrId || '').trim();
  var m = s.match(/\/spreadsheets\/d\/([a-zA-Z0-9-_]+)/);
  var id = m ? m[1] : s;
  if (!id) throw new Error('openByUrlOrId: empty urlOrId');
  return SpreadsheetApp.openById(id);
}
// var ss = openByUrlOrId(Browser.inputBox('Spreadsheet URL or id'));

Learn More →

Parse CSV Text


function parseCsvText(text, options) {
  options = options || {};
  var delim = options.delimiter != null ? options.delimiter : ',';
  var rows = [];
  var row = [];
  var field = '';
  var i = 0;
  var inQuotes = false;
  text = String(text == null ? '' : text);
  while (i < text.length) {
    var ch = text.charAt(i);
    if (inQuotes) {
      if (ch === '"') {
        if (text.charAt(i + 1) === '"') { field += '"'; i += 2; continue; }
        inQuotes = false; i++; continue;
      }
      field += ch; i++; continue;
    }
    if (ch === '"') { inQuotes = true; i++; continue; }
    if (ch === delim) { row.push(field); field = ''; i++; continue; }
    if (ch === '\r') { i++; continue; }
    if (ch === '\n') { row.push(field); rows.push(row); row = []; field = ''; i++; continue; }
    field += ch; i++;
  }
  if (field.length || row.length) { row.push(field); rows.push(row); }
  return rows;
}
// var values = parseCsvText(DriveApp.getFileById(id).getBlob().getDataAsString('UTF-8'));

Learn More →

Read / Write a Job Checkpoint


function readWriteCheckpoint(key) {
  return {
    get: function () { return getCheckpoint(key); },
    set: function (cursor, extra) { return setCheckpoint(key, cursor, extra); },
    clear: function () { clearCheckpoint(key); }
  };
}
// var cp = readWriteCheckpoint('job:sync'); var cur = (cp.get() || {}).cursor || 0; cp.set(cur + 50);

Learn More →

Require a Script Property (fail loud)


function requireScriptProperty(key) {
  var v = PropertiesService.getScriptProperties().getProperty(key);
  if (v == null || String(v).trim() === '') {
    throw new Error('Missing Script Property: ' + key);
  }
  return v;
}
// var sheetId = requireScriptProperty('ACTIVE_SPREADSHEET_ID');

Learn More →

Retry UrlFetch with Exponential Backoff


function retryUrlFetch(url, options, maxAttempts) {
  maxAttempts = maxAttempts || 5;
  options = options || {};
  var lastError;
  for (var attempt = 1; attempt <= maxAttempts; attempt++) {
    try {
      var response = UrlFetchApp.fetch(url, options);
      var code = response.getResponseCode();
      if (code === 429 || code >= 500) {
        throw new Error('Retryable HTTP ' + code + ': ' + response.getContentText().substring(0, 200));
      }
      return response;
    } catch (err) {
      lastError = err;
      if (attempt === maxAttempts) break;
      var waitMs = Math.min(30000, Math.pow(2, attempt - 1) * 500 + Math.floor(Math.random() * 250));
      Utilities.sleep(waitMs);
    }
  }
  throw new Error('retryUrlFetch failed after ' + maxAttempts + ' attempts: ' + lastError);
}
// retryUrlFetch('https://httpbin.org/status/500', { muteHttpExceptions: true }, 4)

Learn More →

Safe JSON Parse


function safeJsonParse(text, fallback, onError) {
  if (text == null || text === '') return fallback;
  try {
    return JSON.parse(text);
  } catch (err) {
    if (typeof onError === 'function') {
      try { onError(err, text); } catch (ignore) {}
    }
    return fallback;
  }
}
// var cfg = safeJsonParse(props.getProperty('config'), { mode: 'safe' });

Learn More →

Sanitize a Sheet Tab Name


function sanitizeSheetName(name, options) {
  options = options || {};
  var maxLen = options.maxLength || 100;
  var fallback = options.fallback || 'Sheet';
  var s = String(name == null ? '' : name).trim();
  s = s.replace(/[:\\\/\?\*\[\]]/g, ' ');
  s = s.replace(/\s+/g, ' ').trim();
  if (s.charAt(0) === "'") s = s.substring(1);
  if (!s) s = fallback;
  if (s.length > maxLen) s = s.substring(0, maxLen).trim();
  if (!s) s = fallback;
  return s;
}
// sanitizeSheetName('Acme / Q3: Final*') // → 'Acme Q3 Final'

Learn More →

Send an HTML Email with GmailApp


function sendHtmlEmail(to, subject, htmlBody, options) {
  options = options || {};
  var msg = {
    to: to,
    subject: subject,
    htmlBody: htmlBody,
    name: options.name || undefined,
    cc: options.cc || undefined,
    bcc: options.bcc || undefined,
    replyTo: options.replyTo || undefined
  };
  Object.keys(msg).forEach(function (k) { if (msg[k] === undefined) delete msg[k]; });
  GmailApp.sendEmail(msg.to, msg.subject, stripHtml_(htmlBody), msg);
}
function stripHtml_(html) {
  return String(html).replace(/<[^>]+>/g, ' ').replace(/\s+/g, ' ').trim();
}
// sendHtmlEmail('ada@example.com', 'Welcome', '<p>Hello <b>Ada</b></p>', { name: 'Ops', cc: 'team@example.com' });

Learn More →

Sending Emails Using Apps Script


function sendEmail(recipient, subject, emailBody) {
  GmailApp.sendEmail(recipient, subject, emailbody)
}
// sendEmail('email@example.com', 'Awesome Subject', `Send this awesome email`)

Learn More →

Show a Sheet Toast for Progress


function sheetToast(message, title, timeoutSeconds) {
  title = title || 'NitroGAS';
  timeoutSeconds = (timeoutSeconds == null) ? 5 : timeoutSeconds;
  SpreadsheetApp.getActiveSpreadsheet().toast(message, title, timeoutSeconds);
}
// sheetToast('Synced 42 rows', 'Import', 3);

Learn More →

Split an Array into Parts


function createBatchedArray(data, chunkSize) {
    const result = []
    for(let i=0; i<data.length; i+=chunkSize) {
        let chunk = data.slice(i, i+chunkSize)
        result.push(chunk)
    }
    return result
}

Learn More →

Stamp Last Successful Run


function stampLastSuccessfulRun(target, status, opts) {
  opts = opts || {};
  status = String(status || 'OK').toUpperCase();
  var ss = opts.spreadsheet || SpreadsheetApp.getActive();
  // Named range first, then A1 notation like 'Status!B2'
  var cell = (ss.getRangeByName(target) || ss.getRange(target)).getCell(1, 1);
  var tz = ss.getSpreadsheetTimeZone();
  var now = Utilities.formatDate(new Date(), tz, opts.format || 'yyyy-MM-dd HH:mm:ss z');
  var sheet = cell.getSheet();
  var row = cell.getRow();
  var col = cell.getColumn();
  // [last OK time] [status] [last run time] [message] — stored as plain text
  sheet.getRange(row, col, 1, 4).setNumberFormat('@');
  if (status === 'OK') cell.setValue(now); // FAIL never overwrites the last OK time
  sheet.getRange(row, col + 1, 1, 3).setValues([[status, now, opts.message || '']]);
  return now;
}

Learn More →

Store and Load JSON in Script Properties


function getJson(key, fallback) {
  var raw = PropertiesService.getScriptProperties().getProperty(key);
  if (raw == null || raw === '') return fallback;
  return JSON.parse(raw);
}

function setJson(key, value) {
  PropertiesService.getScriptProperties().setProperty(key, JSON.stringify(value));
}
// setJson('config', { apiBase: 'https://example.com' });
// var config = getJson('config', {});

Learn More →

Truncate For Cell


function truncateForCell(text, maxLen) {
  maxLen = maxLen == null ? 50000 : Number(maxLen);
  if (text == null) return '';
  var s = String(text);
  if (!(maxLen > 0) || s.length <= maxLen) return s;
  var suffix = '…[truncated]';
  var keep = Math.max(0, maxLen - suffix.length);
  return s.slice(0, keep) + suffix;
}
// sheet.getRange('A1').setValue(truncateForCell(err.stack));

Learn More →

Try Lock Or Skip


function tryLockOrSkip(kind, timeoutMs) {
  timeoutMs = timeoutMs == null ? 0 : timeoutMs;
  var lock = (kind === 'document')
    ? LockService.getDocumentLock()
    : LockService.getScriptLock();
  var ok = timeoutMs > 0 ? lock.tryLock(timeoutMs) : lock.tryLock(1);
  if (!ok) return null;
  return lock;
}
// var lock = tryLockOrSkip('script', 0); if (!lock) return; try { /* work */ } finally { lock.releaseLock(); }

Learn More →

Upsert a Row by Key Column


function upsertRowByKey(sheet, keyHeader, keyValue, valuesByHeader) {
  var data = sheet.getDataRange().getValues();
  var headers = data[0];
  var keyCol = headers.indexOf(keyHeader);
  if (keyCol === -1) throw new Error('Missing header: ' + keyHeader);
  var rowIndex = -1;
  for (var r = 1; r < data.length; r++) {
    if (String(data[r][keyCol]) === String(keyValue)) { rowIndex = r; break; }
  }
  var row = headers.map(function (h) {
    return valuesByHeader.hasOwnProperty(h) ? valuesByHeader[h] : (rowIndex === -1 ? '' : data[rowIndex][headers.indexOf(h)]);
  });
  if (rowIndex === -1) { sheet.appendRow(row); return sheet.getLastRow(); }
  sheet.getRange(rowIndex + 1, 1, 1, row.length).setValues([row]);
  return rowIndex + 1;
}
// upsertRowByKey(sheet, 'email', 'ada@example.com', { email: 'ada@example.com', name: 'Ada' });

Learn More →

Write Block Leaving Formula Cols


function writeBlockLeavingFormulaCols(sheet, rows, dataHeaders, opts) {
  opts = opts || {};
  if (!rows || !rows.length) return { startRow: null, numRows: 0, runs: [] };
  var headerRow = opts.headerRow || 1;
  // Header name -> 1-based column number (read from the sheet if not passed in)
  var map = opts.headerMap;
  if (!map) {
    map = {};
    sheet.getRange(headerRow, 1, 1, sheet.getLastColumn()).getValues()[0]
      .forEach(function (h, i) { var k = String(h).trim(); if (k && !map[k]) map[k] = i + 1; });
  }
  // Only allowlisted headers get written; everything else (formulas) is left alone
  var cols = dataHeaders.filter(function (h, i) { return dataHeaders.indexOf(h) === i; })
    .map(function (h) {
      if (!map[h]) throw new Error('writeBlockLeavingFormulaCols: header not found: ' + h);
      return { header: h, col: map[h] };
    })
    .sort(function (a, b) { return a.col - b.col; });
  // Group side-by-side columns into runs so each run is one setValues call
  var runs = [];
  cols.forEach(function (c) {
    var last = runs[runs.length - 1];
    if (last && c.col === last.startCol + last.headers.length) last.headers.push(c.header);
    else runs.push({ startCol: c.col, headers: [c.header] });
  });
  var startRow = opts.startRow || Math.max(sheet.getLastRow(), headerRow) + 1;
  runs.forEach(function (run) {
    var values = rows.map(function (obj) {
      return run.headers.map(function (h) { return obj[h] == null ? '' : obj[h]; });
    });
    sheet.getRange(startRow, run.startCol, rows.length, run.headers.length).setValues(values);
  });
  return { startRow: startRow, numRows: rows.length, runs: runs };
}

Learn More →

Write Values as a CSV File in Drive


function writeValuesAsCsvToDrive(values, folder, filename) {
  var csv = valuesToCsv(values); // see Convert Sheet Values to CSV Text
  var existing = folder.getFilesByName(filename);
  if (existing.hasNext()) {
    var file = existing.next();
    file.setContent(csv);
    return file;
  }
  return folder.createFile(filename, csv, MimeType.CSV);
}
// writeValuesAsCsvToDrive(values, folder, 'Exports/2026-09-18.csv'.split('/').pop());

Learn More →

rowsToObjects and objectsToRows


function rowsToObjects(rows) {
  if (!rows || !rows.length) return [];
  var headers = rows[0].map(function (h) { return String(h).trim(); });
  return rows.slice(1).map(function (row) {
    var obj = {};
    for (var i = 0; i < headers.length; i++) {
      obj[headers[i]] = row[i];
    }
    return obj;
  });
}

function objectsToRows(objects, headers) {
  if (!objects || !objects.length) return [headers || []];
  headers = headers || Object.keys(objects[0]);
  var body = objects.map(function (obj) {
    return headers.map(function (key) {
      return obj.hasOwnProperty(key) ? obj[key] : '';
    });
  });
  return [headers].concat(body);
}
// rowsToObjects([['id','name'],[1,'Ada'],[2,'Lin']]);
// objectsToRows([{id:1,name:'Ada'}], ['id','name']);

Learn More →

withDocumentLock Helper


function withDocumentLock(fn, waitMs) {
  waitMs = waitMs || 30000;
  var lock = LockService.getDocumentLock();
  if (!lock.tryLock(waitMs)) {
    throw new Error('Could not acquire document lock within ' + waitMs + 'ms');
  }
  try {
    return fn();
  } finally {
    lock.releaseLock();
  }
}
// withDocumentLock(function () { /* mutate the bound spreadsheet safely */ });

Learn More →

withScriptLock Helper


function withScriptLock(fn, waitMs) {
  waitMs = waitMs || 30000;
  var lock = LockService.getScriptLock();
  if (!lock.tryLock(waitMs)) {
    throw new Error('Could not acquire script lock within ' + waitMs + 'ms');
  }
  try {
    return fn();
  } finally {
    lock.releaseLock();
  }
}
// withScriptLock(function () { /* project-wide critical section */ });

Learn More →

withUserLock Helper


function withUserLock(fn, waitMs) {
  waitMs = waitMs || 30000;
  var lock = LockService.getUserLock();
  if (!lock.tryLock(waitMs)) {
    throw new Error('Could not acquire user lock within ' + waitMs + 'ms');
  }
  try {
    return fn();
  } finally {
    lock.releaseLock();
  }
}
// withUserLock(function () { /* per-user critical section */ });

Learn More →