No More Visual Basic... Use AppScript & JavaScript ES5 In Google Sheets!

If your spreadsheet automation experience starts with Excel macros, Google Sheets can feel like familiar territory, until you try to bring your VBA code along. Sheets does not run Visual Basic for Applications. A VBA workflow that interacts with Google Sheets depends on external tooling, not a native macro engine inside the spreadsheet.

The native alternative is Google Apps Script: a hosted scripting environment that lets you read spreadsheet data, build menus, generate reports, and connect Google services using JavaScript. Instead of asking colleagues to install a separate script, you can attach an Apps Script project directly to a spreadsheet.

This tutorial uses the 2019 Apps Script environment; tools, interfaces, and runtime capabilities may have changed since then.

We’ll build a practical replacement for a common VBA task: turning a table of order lines into a formatted summary with one menu command. Along the way, we’ll separate actual spreadsheet formatting from CSS and explain what sharing a script does, and does not, solve.

Apps Script Is Not VBA with Different Syntax

The official name is Google Apps Script, although you may see it shortened informally to “AppScript.” It runs on Google’s servers and exposes services such as SpreadsheetApp, DriveApp, and MailApp.

For a VBA developer, the biggest change is the object model:

• Instead of Workbook and Worksheet, you work with Spreadsheet and Sheet objects.
• Instead of using Excel’s application object, you access services such as SpreadsheetApp.
• Instead of assuming desktop execution, you account for authorization, execution limits, and remote service calls.

For this 2019 workflow, use conservative, ES5-style JavaScript: var, ordinary functions, arrays, and objects. Do not assume that newer syntax such as arrow functions, template literals, or let is available in the legacy runtime. That runtime also has its own quirks, so “JavaScript” does not mean every browser or Node.js library will work unchanged.

Apps Script’s spreadsheet service is available regardless of a collaborator’s browser JavaScript features. However, any HTML interface you build runs client-side in the browser, with a separate set of considerations.

Build a One-Click Order Summary

Our example reads an Orders sheet and writes a Summary sheet containing each item’s calculated line total and a grand total. The script validates the input before replacing the previous report.

1. Prepare the source sheet

Create a Google spreadsheet and rename its first tab Orders. Add these headers and sample values:

| Item | Quantity | Unit price | | --- | ---: | ---: | | Notebook | 3 | 4.50 | | Pen pack | 2 | 6.00 | | Folder | 5 | 1.25 |

Put the headers in row 1 and the data in columns A through C. Enter quantities and prices as numbers, not text containing currency labels. Applying currency formatting to a numeric cell is fine; the underlying value remains numeric.

The script will reserve the Summary tab for generated output. Do not store unrelated information there.

2. Create a spreadsheet-bound script

In the 2019 Sheets interface, open Tools → Script editor. This creates a script project bound to the current spreadsheet.

Replace the default code with the following:

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Order tools')
    .addItem('Build summary', 'buildSummary')
    .addToUi();
}

function buildSummary() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var source = spreadsheet.getSheetByName('Orders');

  if (!source) {
    throw new Error('Create a sheet named Orders first.');
  }

  var lastRow = source.getLastRow();
  if (lastRow < 2) {
    throw new Error('Add at least one order below the headers.');
  }

  var rows = source.getRange(2, 1, lastRow - 1, 3).getValues();
  var output = [['Item', 'Quantity', 'Unit price', 'Line total']];
  var grandTotal = 0;

  rows.forEach(function (row, index) {
    var item = row[0];
    var quantity = row[1];
    var price = row[2];
    var sheetRow = index + 2;

    if (item === '' && quantity === '' && price === '') {
      return;
    }

    if (typeof item !== 'string' || item.trim() === '') {
      throw new Error('Row ' + sheetRow + ': enter an item name.');
    }

    if (typeof quantity !== 'number' || !isFinite(quantity) ||
        quantity <= 0 || Math.floor(quantity) !== quantity) {
      throw new Error('Row ' + sheetRow + ': quantity must be a positive integer.');
    }

    if (typeof price !== 'number' || !isFinite(price) || price < 0) {
      throw new Error('Row ' + sheetRow + ': price must be a nonnegative number.');
    }

    // Round each line to cents for this example's pricing rule.
    var lineTotal = Math.round(quantity * price * 100) / 100;
    grandTotal += lineTotal;

    // Prevent item names beginning with = from becoming formulas.
    var safeItem = item.charAt(0) === '=' ? "'" + item : item;
    output.push([safeItem, quantity, price, lineTotal]);
  });

  if (output.length === 1) {
    throw new Error('No order lines were found.');
  }

  grandTotal = Math.round(grandTotal * 100) / 100;
  output.push(['Grand total', '', '', grandTotal]);

  var report = spreadsheet.getSheetByName('Summary');
  if (!report) {
    report = spreadsheet.insertSheet('Summary');
  }

  // This tab is dedicated to generated output.
  report.clear();
  report.getRange(1, 1, output.length, 4).setValues(output);

  report.getRange(1, 1, 1, 4)
    .setFontWeight('bold')
    .setBackground('#e8f0fe');

  report.getRange(2, 3, output.length - 1, 2)
    .setNumberFormat('0.00');

  report.getRange(output.length, 1, 1, 4)
    .setFontWeight('bold');

  report.setFrozenRows(1);
  report.autoResizeColumns(1, 4);
}

This example deliberately rejects fractional quantities and negative prices. Change those rules if your business supports quantities measured by weight, refunds, or credit lines.

3. Save, authorize, and run

Save the project. In the script editor, select buildSummary and run it. Follow the authorization prompts, checking that you are using the intended Google account and understand the requested access.

After execution, return to the spreadsheet. The Summary tab should contain line totals of 13.50, 12.00, and 6.25, with a grand total of 31.75.

Reload the spreadsheet to trigger onOpen(). You should now see Order tools → Build summary. That menu becomes the normal way to regenerate the report.

The onOpen() function only adds the menu. The report runs when someone explicitly selects the menu command; it does not automatically rebuild whenever a cell changes.

4. Test failure cases before sharing

Try a blank row between orders, a quantity entered as text, and a missing item name. Completely blank rows should be skipped. Invalid populated rows should produce an error identifying the source row.

Because validation happens before report.clear(), invalid input leaves the previous report intact. That is useful, but it also means the old report may be stale. Tell users that a failed run does not refresh the output.

For a more polished internal tool, add a generated-at timestamp or a dialog reporting success. For important reports, consider writing to a temporary tab first and publishing only after every operation succeeds.

What the JavaScript Is Doing

Work with arrays, not individual cells

getValues() returns a two-dimensional array: one array for each row, containing that row’s cell values. Here, each row contains an item, a quantity, and a price.

We process those values in memory using forEach() and assemble another two-dimensional array for setValues().

This pattern matters because calls to spreadsheet services have overhead. Reading and writing one cell at a time inside a large loop is usually much slower than one bulk read and one bulk write.

The output range must match the array dimensions exactly. Every output row in this example contains four elements, so the destination range has four columns.

Preserve data types deliberately

A cell that looks numeric is not necessarily a number. Imported data may contain strings such as "3", while date cells may return JavaScript Date objects.

The example uses strict type checks rather than silently coercing values. Blind conversion can hide mistakes: Number(''), for instance, produces zero.

The rounding rule is also explicit. JavaScript uses binary floating-point arithmetic, so decimal calculations can have small representation errors. Rounding each line to two decimal places is adequate for this demonstration, not a universal accounting policy. Financial workflows should define currency precision, rounding, and reconciliation requirements before implementation.

There Is No Spreadsheet “stylesheet” Property

Apps Script does not expose a stylesheet property for styling Google Sheets cells. CSS3 rules cannot directly change a spreadsheet grid’s appearance.

Use the spreadsheet API for cell formatting:

function formatOrderHeaders() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('Orders');

  if (!sheet) {
    throw new Error('Orders sheet not found.');
  }

  sheet.getRange('A1:C1')
    .setBackground('#263238')
    .setFontColor('#ffffff')
    .setFontWeight('bold');
}

Other range methods control alignment, borders, wrapping, and number formats. For formatting that responds to cell values, conditional formatting is often better than repeatedly running a script.

CSS belongs in HTML interfaces, such as sidebars and dialogs created with HtmlService. A sidebar can have styled buttons and forms, but its stylesheet affects the sidebar, not the cells behind it. Client-side code can call server-side Apps Script functions using google.script.run, which is asynchronous.

Sharing, Permissions, and Automation Pitfalls

A bound script travels with its spreadsheet: collaborators do not need to manually paste the same code into separate projects just to use that shared file. This is convenient, but it is not unrestricted deployment.

Keep these distinctions in mind:

• Editing access matters. Spreadsheet editors can generally access and modify the bound project. Do not treat it as a place to hide credentials from collaborators.
• Authorization is per user where required. A menu action normally runs as the person invoking it. Another editor may need to authorize the script and must have access to any other files it touches.
• Simple triggers are restricted. Our onOpen() can add a menu, but simple triggers cannot freely use services that require authorization. Installable triggers have different capabilities and run under the account that created them.
• Copies are independent. Copying a spreadsheet can copy its bound script, but later fixes to the original project do not automatically update those copies.
• Limits still apply. Apps Script has execution and service quotas. Keep operations bounded, batch spreadsheet access, and check the documented limits for your account.

Two users can also launch a report simultaneously. For a busy shared workbook, use LockService to coordinate script executions that write to the same output, and release locks in a finally block. Locks coordinate cooperating scripts; they do not prevent people from manually editing cells.

If you need the same tool across many spreadsheets, consider a published Sheets add-on rather than distributing a collection of independent bound scripts. That introduces deployment and authorization work, but provides a more maintainable distribution model.

Takeaway

Replacing a VBA-style workflow in Google Sheets means adopting Apps Script’s services, permissions, and execution model, not simply translating keywords. Start with a bound script, use ES5-style JavaScript appropriate to the 2019 runtime, batch your reads and writes, and validate before changing output. Format cells through SpreadsheetApp; reserve CSS for HTML interfaces. That gives you a practical, shareable foundation for spreadsheet automation.