Google Apps Script

Four short recipes for Google Sheets. Paste them into an Apps Script project bound to the spreadsheet and change the columns before you run them.

Guide: first steps with Apps Script

A menu in the sheet

Adds a menu when the spreadsheet opens and runs a function on the selected row.

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Tools')
    .addItem('Process active row', 'processActiveRow')
    .addToUi();
}

function processActiveRow() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = sheet.getActiveCell().getRow();
  if (row === 1) {
    throw new Error('Select a data row, not the header.');
  }
  SpreadsheetApp.getActive().toast('Row ' + row);
}

onOpen is a simple trigger. The menu function can ask for permission the first time you run it.

Rows as objects

Reads a sheet: the first row is the header, the rest become objects. Blank rows are skipped.

function readSheetObjects(sheetName) {
  var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  if (!sheet) throw new Error('Sheet not found: ' + sheetName);
  var values = sheet.getDataRange().getValues();
  if (values.length < 2) return [];
  var headers = values[0].map(function (header) { return String(header).trim(); });
  var objects = [];
  for (var r = 1; r < values.length; r++) {
    var row = values[r];
    var blank = row.every(function (cell) { return String(cell).trim() === ''; });
    if (blank) continue;
    var item = {};
    for (var c = 0; c < headers.length; c++) {
      if (!headers[c]) continue;
      item[headers[c]] = row[c];
    }
    objects.push(item);
  }
  return objects;
}

Column names need to be unique. Empty cells stay empty strings.

Timestamp when column B changes

Writes the current date in column E when someone edits column B, below the header.

function onEdit(e) {
  if (!e || !e.range) return;
  var range = e.range;
  if (range.getColumn() !== 2 || range.getRow() === 1) return;
  range.getSheet().getRange(range.getRow(), 5).setValue(new Date());
}

This is a simple trigger: it runs for edits made in the UI, not for changes made by another script or the API.

Email the active row

Sends an email from columns A (name), B (email), and C (note) on the selected row.

function emailActiveRow() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = sheet.getActiveCell().getRow();
  if (row === 1) throw new Error('Select a data row, not the header.');
  var values = sheet.getRange(row, 1, 1, 3).getValues()[0];
  var name = String(values[0] || '').trim();
  var email = String(values[1] || '').trim();
  var note = String(values[2] || '').trim();
  if (!email) throw new Error('Column B is empty.');
  MailApp.sendEmail({
    to: email,
    subject: 'Update',
    body: 'Hello ' + name + ',\n\n' + note + '\n'
  });
}

Call it from a menu, not from onEdit. MailApp needs authorization and is not allowed in a simple trigger. Change the subject before you use it.