Edit and debug one Apps Script sheet automation

Quick answer

Open Extensions > Apps Script from a private spreadsheet, use a function that reads two numeric cells and writes one dedicated output, and inspect Execution log before changing anything else. A breakpoint before the write lets you compare the inputs and computed result without guessing which line failed.

Set up a bounded practice file

Use desktop Google Sheets with edit access to a file you own. This tutorial creates a container-bound script, not a web app, add-on or standalone automation. It does not need Gmail, external URLs, Drive-wide searches, API keys, deployments or scheduled triggers. If organizational policy blocks Apps Script, stop rather than trying another identity.

Create a tab named exactly DebugLab. Enter A1 Input, B1 Value, A2 First, B2 12, A3 Second, B3 8, A5 Output, and leave B5 empty. The expected sum is 20. B5 is reserved for script output; keep anything valuable out of it. A plain sheet formula could solve this small problem more simply, but the deliberately tiny calculation makes debugging behavior easy to inspect.

Before editing an existing project, preserve its current code. For this exercise use a fresh practice spreadsheet so there is no unrelated script or trigger to disturb.

Add the function

Choose Extensions > Apps Script and enter the following into the bound project. Keep the file-level annotation. Save the project and select updatePracticeTotal in the function selector.

/** @OnlyCurrentDoc */
function updatePracticeTotal() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('DebugLab');
  if (!sheet) throw new Error('Missing DebugLab sheet');
  const values = sheet.getRange('B2:B3').getValues().flat();
  if (!values.every(v => typeof v === 'number' && Number.isFinite(v))) {
    throw new Error('B2 and B3 must contain numbers');
  }
  const total = values[0] + values[1];
  console.log('Practice total: ' + total);
  sheet.getRange('B5').setValue(total);
}

The sheet-name check makes a missing tab understandable. The numeric check deliberately rejects blanks and text rather than silently treating them as zero. All validation happens before the write, so a bad input leaves the previous output untouched. That is a useful failure behavior, but it also means an old output is not evidence that the latest run succeeded.

The code is an original, bounded teaching example. No Apps Script execution is claimed here. Its mathematical and branch behavior can be inspected independently; the actual service calls still need reader testing in the authorized practice account.

Run and reconcile the first result

Click Run. If the account requires authorization, review the exact current-file spreadsheet access request before proceeding. The annotation narrows the relevant document scope, but does not make every possible script safe. Cancel if the request introduces unrelated services or an unexpected security warning; do not follow a bypass sequence.

Open Execution log at the top of the editor. A successful run should log a practice total of 20 and place 20 in DebugLab!B5. Check both. A log entry alone does not prove the final write completed, and a cell containing 20 might be a stale value from an earlier run.

Change B3 to 11 and run again. Expect a log total of 23 and B5 equal to 23. B2 stays 12. Restore B3 to 8 and rerun to return to 20. These are three explicit outcomes against which to compare the tool, not screenshots fabricated as test evidence.

Pause before writing

In the editor, point beside the line number for sheet.getRange('B5').setValue(total); and click the breakpoint circle. Click Debug. The debugger should pause before that line executes. Inspect values and total: for the restored fixture they should contain 12 and 8, and 20 respectively.

Use the Debugger panel’s Step over control to advance the write, then inspect B5. Step in and Step out are useful when your own code contains nested function calls, but the single write is sufficient here. Remove the breakpoint after the exercise so an unexpected pause does not confuse later testing.

Introduce a failure you can explain

Temporarily change the string 'DebugLab' in the code to 'MissingTab', save, and run. The expected failure is your own Missing DebugLab sheet error; B5 should retain the last successful value. Restore the exact sheet-name string before doing anything else.

For a data failure, put the text eight in B3 and run. Expect the numeric-input error and no new output write. Restore numeric 8, save any code changes, and run once successfully. This final recovery run matters: an error followed only by an edit is not proof that the repaired workflow works.

Keep diagnosis separate from authorization

A syntax error prevents normal execution; repair the indicated code. A missing-sheet error concerns the fixture. An authorization error concerns account access. A quota error concerns execution limits. Repeatedly granting more permissions does not fix the first two, and rapid retries can worsen the last.

Do not log real personal or confidential cell contents while adapting this example. The single synthetic total is sufficient for the exercise. Close and reopen the spreadsheet, reopen its bound project, and verify the saved function and fixture. Downloading the spreadsheet does not export a runnable Apps Script workflow into another spreadsheet application.

Official references

Official references: bound scripts, debugging and troubleshooting, Execution log, and authorization. No native execution, authorization or debugger screenshot was performed for this guide.

API references for the bounded example: Spreadsheet, Sheet, and Range.