VBA to Apps Script cheat sheet

Sub ShowLastRow()
    Dim last As Long
    last = Cells(Rows.Count, 1).End(xlUp).Row
    Range("C1").Value = last
End Sub

function ShowLastRow() {
  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange('C1').setValue(lastRowInColumn_(sheet, 1));
}

// Last non-empty row in one column (1 if the column is empty).
function lastRowInColumn_(sheet, col) {
  const values = sheet.getRange(1, col, Math.max(sheet.getLastRow(), 1), 1).getValues();
  for (let r = values.length - 1; r >= 0; r--) if (values[r][0] !== '') return r + 1;
  return 1;
}

Sub AddTax()
    Dim i As Long
    For i = 2 To 4
        Cells(i, 2).Value = Int(Cells(i, 1).Value * 1.1)
    Next i
End Sub

function AddTax() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getRange(2, 1, 3, 2);
  const rows = range.getValues().map(([price]) => [price, Math.floor(price * 1.1)]);
  range.setValues(rows);
}

Sub Hello()
    MsgBox "Done"
End Sub

function Hello() {
  SpreadsheetApp.getUi().alert('Done');
}

Sub ConfirmClear()
    If MsgBox("Clear the sheet?", vbYesNo) = vbYes Then
        Range("A1").Value = ""
    End If
End Sub

function ConfirmClear() {
  const ui = SpreadsheetApp.getUi();
  if (ui.alert('Confirm', 'Clear the sheet?', ui.ButtonSet.YES_NO) === ui.Button.YES) {
    SpreadsheetApp.getActiveSheet().getRange('A1').setValue('');
  }
}

Sub AskName()
    Dim s As String
    s = InputBox("Your name?")
    If s = "" Then Exit Sub
    Range("A1").Value = s
End Sub

function AskName() {
  const ui = SpreadsheetApp.getUi();
  const res = ui.prompt('Your name?');
  if (res.getSelectedButton() !== ui.Button.OK || res.getResponseText() === '') return;
  SpreadsheetApp.getActiveSheet().getRange('A1').setValue(res.getResponseText());
}

Sub CopyTotal()
    Worksheets("Report").Range("A1").Value = Worksheets("Data").Range("B2").Value
End Sub

function CopyTotal() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const data = ss.getSheetByName('Data');
  const report = ss.getSheetByName('Report');
  if (!data || !report) throw new Error('Sheet "Data" or "Report" not found');
  report.getRange('A1').setValue(data.getRange('B2').getValue());
}

Sub RoundDemo()
    Range("A1").Value = Round(2.5)
    Range("A2").Value = Round(3.5)
    Range("A3").Value = Round(2.345, 2)
End Sub

function RoundDemo() {
  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange('A1:A3').setValues([[vbaRound_(2.5)], [vbaRound_(3.5)], [vbaRound_(2.345, 2)]]);
}

// VBA Round: halves go to the nearest even digit.
function vbaRound_(value, digits = 0) {
  const f = Math.pow(10, digits);
  const x = Number((value * f).toPrecision(15));
  const r = Math.round(x);
  return (Math.abs(x % 1) === 0.5 && r % 2 !== 0 ? r - 1 : r) / f;
}

Sub MathDemo()
    Range("A1").Value = Int(-2.5)
    Range("A2").Value = Fix(-2.5)
    Range("A3").Value = 12.6 Mod 5
End Sub

function MathDemo() {
  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange('A1:A3').setValues([[Math.floor(-2.5)], [Math.trunc(-2.5)], [vbaMod_(12.6, 5)]]);
}

function vbaMod_(a, b) {
  const toInt = (x) => (Math.abs(x % 1) === 0.5 ? 2 * Math.round(x / 2) : Math.round(x));
  return toInt(a) % toInt(b);
}

Sub StringDemo()
    Dim s As String: s = "INV-2026-001"
    Range("A1").Value = Left(s, 3)
    Range("A2").Value = Mid(s, 5, 4)
    Range("A3").Value = Right(s, 3)
    Range("A4").Value = InStr(s, "2026")
    Range("A5").Value = InStr(s, "X")
End Sub

function StringDemo() {
  const s = 'INV-2026-001';
  const out = [
    s.slice(0, 3),
    s.substr(5 - 1, 4),   // Mid(s, 5, 4): start is 1-based
    s.slice(-3),
    s.indexOf('2026') + 1, // InStr: 1-based, 0 when missing
    s.indexOf('X') + 1,
  ];
  SpreadsheetApp.getActiveSheet().getRange(1, 1, out.length, 1).setValues(out.map((v) => [v]));
}

Sub DateLabel()
    Range("A1").Value = "Issued " & Format(DateSerial(2026, 4, 1), "yyyy/mm/dd")
End Sub

function DateLabel() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const d = new Date(2026, 3, 1); // months are 0-based in JavaScript
  const text = Utilities.formatDate(d, ss.getSpreadsheetTimeZone(), 'yyyy/MM/dd');
  ss.getActiveSheet().getRange('A1').setValue('Issued ' + text);
}

Sub Dates()
    Range("A1").Value = Format(DateSerial(2026, 3, 0), "yyyy/mm/dd")
    Range("A2").Value = Format(DateAdd("m", 1, DateSerial(2026, 1, 31)), "yyyy/mm/dd")
End Sub

function Dates() {
  const tz = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();
  const fmt = (d) => Utilities.formatDate(d, tz, 'yyyy/MM/dd');
  SpreadsheetApp.getActiveSheet().getRange('A1:A2').setValues([
    [fmt(new Date(2026, 2, 0))],
    [fmt(addMonths_(new Date(2026, 0, 31), 1))],
  ]);
}

// Like VBA DateAdd("m"): keeps the day, clamped to the target month's length.
function addMonths_(date, n) {
  const d = new Date(date.getFullYear(), date.getMonth() + n, 1);
  const last = new Date(d.getFullYear(), d.getMonth() + 1, 0).getDate();
  d.setDate(Math.min(date.getDate(), last));
  return d;
}

Sub TotalsByCustomer()
    Dim d As Object, i As Long
    Set d = CreateObject("Scripting.Dictionary")
    For i = 2 To 5
        d(Cells(i, 1).Value) = d(Cells(i, 1).Value) + Cells(i, 2).Value
    Next i
    Range("D1").Value = d("A")
    Range("D2").Value = d.Count
End Sub

function TotalsByCustomer() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const totals = new Map();
  for (const [name, amount] of sheet.getRange(2, 1, 4, 2).getValues()) {
    totals.set(name, (totals.get(name) || 0) + amount);
  }
  sheet.getRange('D1:D2').setValues([[totals.get('A')], [totals.size]]);
}

Sub UnitPrice()
    On Error GoTo Failed
    Range("C1").Value = Range("A1").Value / Range("B1").Value
    Exit Sub
Failed:
    Range("C1").Value = "error"
End Sub

function UnitPrice() {
  const sheet = SpreadsheetApp.getActiveSheet();
  try {
    const [[total, qty]] = sheet.getRange('A1:B1').getValues();
    if (qty === 0 || qty === '') throw new Error('Division by zero');
    sheet.getRange('C1').setValue(total / qty);
  } catch (e) {
    sheet.getRange('C1').setValue('error');
  }
}

Sub RemoveDone()
    Dim i As Long
    For i = 5 To 2 Step -1
        If Cells(i, 2).Value = "done" Then Rows(i).Delete
    Next i
End Sub

function RemoveDone() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getRange(2, 1, 4, 2);
  const keep = range.getValues().filter(([, status]) => status !== 'done');
  range.clearContent();
  if (keep.length) sheet.getRange(2, 1, keep.length, 2).setValues(keep);
}

Sub Trace()
    Debug.Print "rows: " & 3
End Sub

function Trace() {
  console.log('rows: ' + 3);
}