# Google Sheets Learning Studio 50 lessons. Examples use English function names, comma separators and decimal points. Interactive browser models support only the functions listed in the lab; use Google Sheets for advanced examples. ## 1. Meet the grid A spreadsheet stores values in cells and organizes them into rows, columns and tabs. A1 identifies column A, row 1. A range such as B2:B6 describes a group of cells. Use one row per record and one column per attribute so formulas can work consistently. 1. Create a blank sheet and name the tab Orders. 2. Put Item, Qty, Price and Team in A1:D1. 3. Enter one order on each following row. ```text A1:D6 ``` Practice: Find the quantity in B2 in the practice grid. Check: What does B2 identify? Answer: Column B, row 2 — Letters identify columns; numbers identify rows. ## 2. Values, formulas and display A value is stored data; a formula computes a result. Formulas start with =. Formatting changes how a value appears, not usually the stored value. A currency format does not turn a text entry into a valid number. 1. Select a cell and inspect the formula bar. 2. Enter =B2*C2 in a new result column. 3. Change the price and observe the result. ```text =B2*C2 ``` Practice: Calculate the first order total in the formula lab. Check: How does a normal cell formula begin? Answer: = — The equals sign tells Sheets to evaluate an expression. ## 3. Clean headers and records Clear headers make sorting, filtering and formulas understandable. Avoid merged cells, blank separator rows and subtotals inside the raw data area. Put summaries beside or on another tab, not between records. 1. Give every source column a unique header. 2. Keep quantities numeric and team names consistent. 3. Separate source records from report calculations. ```text Item | Qty | Price | Team ``` Practice: Find a field that should be numeric rather than text. Check: Where should a subtotal usually go? Answer: In a separate summary area — A continuous rectangular source table is easier to analyze reliably. ## 4. Freeze headers and navigate Freezing keeps headers visible while you scroll; it does not protect them from editing. Use the name box to jump to a reference and the formula bar to inspect a long expression. Navigation shortcuts vary by platform, so learn the menus as well. 1. Choose View → Freeze → 1 row. 2. Scroll down and check that headers stay visible. 3. Use the name box to jump to A1. ```text View → Freeze → 1 row ``` Practice: Freeze a header row in your own practice sheet. Check: Does freezing a row prevent edits? Answer: No — Freeze affects the viewport. Protected ranges are a separate feature. ## 5. Number formats and percentages Percent is a display convention: 0.15 represents 15%. A number can display rounded while calculations still use its underlying precision. Use ROUND when the calculation itself needs a specified precision. 1. Enter 0.15 in a cell. 2. Apply percentage formatting. 3. Compare the displayed result with the value in the formula bar. ```text =ROUND(12.345,2) ``` Practice: Try rounding 12.345 to two decimal places. Check: Which stored number commonly displays as 15%? Answer: 0.15 — Percentage formatting multiplies the displayed number by 100 and adds a percent sign. ## 6. Locale and dates Spreadsheet locale affects how dates and numbers are interpreted. These lessons use English function names, decimal points and comma argument separators; your locale may use semicolons. Prefer DATE(year,month,day) when a typed date would be ambiguous. 1. Inspect File → Settings for locale and time zone. 2. Use DATE to build an unambiguous date. 3. Apply a date display format. ```text =DATE(2026,10,14) ``` Practice: Compare 10/11/2026 in two possible date conventions. Check: Why prefer DATE(2026,10,14) to ambiguous text? Answer: It specifies year, month and day explicitly — DATE makes the intended components explicit; display still depends on formatting. ## 7. Sort complete records Sorting only one column can disconnect values that belong to the same record. Sort the whole table, and tell Sheets whether your range includes headers. Make a copy before a destructive reorganization. 1. Select the complete source table. 2. Use Data → Sort range and identify the header row. 3. Sort by Qty and verify that item names move with their quantities. ```text Sort A1:D6 by Qty ``` Practice: Predict which sample item has the largest quantity before sorting. Check: What must stay together during sorting? Answer: All cells belonging to a record — An order’s item, quantity, price and team must remain aligned. ## 8. Filters versus filter views Filters hide rows that do not match a condition without deleting them. A filter view is useful when you want a personal or saved view of shared data. A filtered display does not automatically make every summary formula ignore hidden records. 1. Select the table and create a filter or filter view. 2. Show only North records. 3. Remove the filter and verify the other records still exist. ```text Team = North ``` Practice: Use the dashboard explorer to switch between teams. Check: Does filtering delete excluded rows? Answer: No — Filtering controls visibility, not the underlying records. ## 9. Dropdowns and validation Validation helps people enter consistent values. A dropdown is useful for a small controlled vocabulary such as team names. Decide whether invalid input should be rejected or merely warned about; validation is not a substitute for reviewing imported data. 1. Select the team-input cells. 2. Create a dropdown with North and South. 3. Try an invalid entry and inspect the rule behavior. ```text North, South ``` Practice: Describe a field in your work that would benefit from a dropdown. Check: Why use a team dropdown? Answer: To standardize category names — Consistent labels prevent separate categories caused by spelling differences. ## 10. Protect, share and recover Share with the least access needed and use version history to investigate changes. Protected ranges help prevent accidental edits; they are not a way to hide confidential data from people who can access the file. Keep sensitive data out of broadly shared workbooks. 1. Review viewer, commenter and editor roles. 2. Protect a formula range against accidental edits. 3. Open version history before deciding whether to restore an earlier version. ```text Inputs editable → Calculations protected ``` Practice: Choose which columns are inputs and which are calculated outputs. Check: Are protected ranges a confidentiality boundary? Answer: No — Protection controls editing behavior, not confidentiality for authorized file viewers. ## 11. Arithmetic and parentheses Use +, -, *, / and parentheses to express a calculation. Multiplication and division happen before addition and subtraction unless parentheses change the grouping. Test a tiny example where you know the answer before using a formula on a large range. 1. Calculate the first order total. 2. Add 5 after multiplying. 3. Compare that with adding 5 before multiplying. ```text =(B2+5)*C2 ``` Practice: Evaluate the grouped expression in the lab. Check: What makes addition happen first in (B2+5)*C2? Answer: The parentheses — Parentheses explicitly group the addition. ## 12. Relative references A relative reference shifts when copied to another position. A formula in E2 using B2*C2 becomes B3*C3 when filled down one row. Think of the pattern you want repeated rather than typing each row separately. 1. Enter =B2*C2 in E2. 2. Fill down to E6. 3. Inspect E3 to confirm both references moved one row. ```text =B2*C2 → =B3*C3 ``` Practice: Try a one-row movement in the reference explorer. Check: Copying B2 down one row produces… Answer: B3 — An unanchored row reference follows the row offset. ## 13. Absolute and mixed references Dollar signs lock a row, a column, or both during copying. $B$2 locks both; $B2 locks the column; B$2 locks the row. Lock a shared input such as a rate while allowing each record reference to move. 1. Compare B2, $B2, B$2 and $B$2. 2. Move each one row down and one column right in the explorer. 3. Explain which part stays fixed. ```text =B2*$C$2 ``` Practice: Predict the copied references before moving them. Check: Which reference locks both row and column? Answer: $B$2 — Each dollar sign anchors the component immediately after it. ## 14. SUM and bounded ranges SUM adds numbers in a range. A bounded range is easier to audit than a whole-column reference when the intended dataset is small. Remember to extend the source range when new rows are added, or choose an appropriate managed table structure. 1. Identify the first and last quantity cells. 2. Use SUM over B2:B6. 3. Change a quantity and recalculate in the playground. ```text =SUM(B2:B6) ``` Practice: Find the total quantity. Check: Which range contains the five sample quantities? Answer: B2:B6 — Column B is Qty, with records on rows 2 through 6. ## 15. AVERAGE, MIN and MAX AVERAGE describes the arithmetic mean, while MIN and MAX identify the endpoints. Do not assume an average answers every business question: an average unit price differs from total revenue divided by total quantity. Know what one row represents. 1. Calculate the mean unit price. 2. Compare it with the minimum and maximum. 3. Explain why a quantity-weighted mean would differ. ```text =AVERAGE(C2:C6) ``` Practice: Calculate the unweighted average unit price. Check: Does AVERAGE(C2:C6) weight by quantities? Answer: No — It averages the five listed prices without using column B. ## 16. COUNT versus COUNTA COUNT counts numeric values; COUNTA counts nonempty values, including text. A header can accidentally increase a nonempty count. Decide whether you want records, numbers, or nonblank entries before selecting a function. 1. Count quantities with COUNT. 2. Count item names with COUNTA. 3. Compare a range with and without its header. ```text =COUNTA(A2:A6) ``` Practice: Count the item labels. Check: Which function counts nonempty text cells? Answer: COUNTA — COUNTA includes text, whereas COUNT focuses on numeric values. ## 17. ROUND and presentation precision ROUND changes a numeric result to a chosen number of decimal places. Number formatting only changes its presentation. Define a rounding policy before aggregating: summing rounded lines can differ from rounding the final sum. 1. Compute a division with several decimals. 2. Round the result to two places. 3. Compare stored values with display-only rounding. ```text =ROUND(10/3,2) ``` Practice: Calculate 10 divided by 3 rounded to two places. Check: Which changes the calculated numeric result? Answer: ROUND — ROUND returns a rounded number; formatting does not normally change the stored value. ## 18. Join labels with & The & operator joins text and values into a label. Put literal text inside double quotes. Add spaces deliberately. For precise date or number formatting inside a label, use TEXT in real Sheets rather than relying on an implicit conversion. 1. Join the item name and team. 2. Insert a separator with spaces. 3. Change the item to see the label update. ```text =A2&" / "&D2 ``` Practice: Build the first item/team label. Check: Which operator joins text? Answer: & — The ampersand concatenates values. ## 19. Clean text consistently TRIM removes leading/trailing spaces and reduces repeated ordinary spaces within text. LOWER and UPPER standardize letter case. Imported nonbreaking spaces may need additional substitution. Keep a raw source copy when cleaning important records. 1. Try a name with extra spaces. 2. Use TRIM, then UPPER. 3. Keep the cleaned result in a separate column until reviewed. ```text =UPPER(TRIM(" north ")) ``` Practice: Normalize the messy team label. Check: Why preserve raw imported values? Answer: To audit the cleaning — A source copy lets you review or reverse a transformation. ## 20. Debug errors before hiding them Read an error’s message before adding a fallback. A parse error points to syntax, #REF! to a reference issue, and #DIV/0! to an invalid division. IFERROR can provide a useful user-facing result, but a blanket fallback may hide an upstream problem. 1. Try =10/0 in the lab. 2. Read the division error. 3. Wrap it in IFERROR with an explicit message. ```text =IFERROR(10/0,"Check denominator") ``` Practice: Handle an intentional divide-by-zero error. Check: What should you do before hiding an error? Answer: Understand its cause — A fallback should not conceal a broken model. ## 21. IF: turn a rule into a result IF chooses between two outcomes. Write the condition first, then the result for true, then false. Test the exact boundary, not just clearly small and large examples. Here a quantity of 3 is included in the higher group. 1. Decide whether Qty is at least 3. 2. Write the true and false labels. 3. Test a quantity equal to 3. ```text =IF(B2>=3,"Bulk","Small") ``` Practice: Classify the first order. Check: Does >=3 include exactly 3? Answer: Yes — The equals part includes the threshold. ## 22. AND, OR and NOT AND requires every condition; OR requires at least one; NOT reverses a logical result. Write a plain-language rule before nesting conditions. Parentheses and separate helper cells can make a complex rule easier to review. 1. Check whether Qty is at least 3. 2. Also require Team to be North. 3. Combine the tests with AND. ```text =AND(B2>=3,D2="North") ``` Practice: Test both conditions for row 2. Check: Which function requires every test to be true? Answer: AND — AND is true only when all its inputs are true. ## 23. COUNTIF: count matching records COUNTIF counts cells matching a criterion. Quote text criteria and comparisons such as ">=3". Counting a condition is different from summing the matching quantities. Check which question the report asks. 1. Select the Team range. 2. Count North labels. 3. Compare the result with North units in a later SUMIF lesson. ```text =COUNTIF(D2:D6,"North") ``` Practice: Count North orders. Check: COUNTIF counts… Answer: Matching cells — It returns a count, not a numeric sum. ## 24. SUMIF: total one category SUMIF tests a criteria range and adds aligned values from a sum range. Both ranges must describe corresponding records. Keep the condition separate from the values you want to total. 1. Use D2:D6 to test team names. 2. Use B2:B6 for quantities. 3. Sum quantities only for North. ```text =SUMIF(D2:D6,"North",B2:B6) ``` Practice: Calculate North units. Check: Which range supplies the quantities here? Answer: B2:B6 — The third argument is the range being summed. ## 25. SUMIFS: combine criteria SUMIFS places the sum range first, followed by criteria-range/criterion pairs. All criteria must match a row for its value to count. This argument order differs from SUMIF, so read the function help when switching. 1. Sum B2:B6. 2. Require Team = North and Qty >=3. 3. Verify the matching rows manually. ```text =SUMIFS(B2:B6,D2:D6,"North",B2:B6,">=3") ``` Practice: In real Sheets, confirm the expected result is 12 for the sample data. Check: SUMIFS uses which criteria relationship? Answer: All criteria must match — Multiple criteria are combined as an AND condition. ## 26. Exact lookups with XLOOKUP A lookup connects a search key to a result. Use a stable unique key when possible; duplicate keys can make a first match ambiguous. XLOOKUP separates the lookup range from the return range and offers a missing-value result. 1. Search for Pen in A2:A6. 2. Return its price from C2:C6. 3. Supply "Not found" for a missing key. ```text =XLOOKUP("Pen",A2:A6,C2:C6,"Not found",0) ``` Practice: Try this in real Sheets; the result should be 2. Check: Why prefer a unique lookup key? Answer: It avoids ambiguous matches — A unique key identifies one record reliably. ## 27. VLOOKUP and exact-match mode VLOOKUP searches the first column of its table and returns a numbered column from that table. FALSE requests an exact match. Approximate matching has different assumptions, including sorted lookup data, so do not omit the mode casually. 1. Use A2:D6 as the lookup table. 2. Return column 3 for the unit price. 3. Specify FALSE for an exact item match. ```text =VLOOKUP("Pen",A2:D6,3,FALSE) ``` Practice: Find the Pen price and then test an absent name. Check: What does FALSE request here? Answer: Exact match — FALSE selects exact matching. ## 28. INDEX and MATCH together MATCH finds a position; INDEX returns a value at a position. Separating these ideas makes a lookup easier to debug. MATCH with 0 requests an exact match. The returned position is relative to the lookup range, not a sheet row number. 1. Find Pen’s position within A2:A6. 2. Pass that position to INDEX over C2:C6. 3. Compare the result with XLOOKUP. ```text =INDEX(C2:C6,MATCH("Pen",A2:A6,0)) ``` Practice: Explain why Pen’s position is 3 although its sheet row is 4. Check: MATCH returns a position relative to… Answer: Its supplied range — Positions start at the first cell in the selected range. ## 29. FILTER: return the matching rows FILTER returns rows or columns meeting conditions. Keep condition dimensions aligned with the source. Leave room for the returned array to expand. A no-match result needs deliberate handling rather than pretending an empty report contains zero-valued records. 1. Filter A2:D6 using D2:D6="North". 2. Leave the output area empty. 3. Try a team name with no matching records. ```text =FILTER(A2:D6,D2:D6="North") ``` Practice: Predict the three item names returned, then test in Sheets. Check: Why leave neighboring output cells empty? Answer: The result may spill into them — A multi-cell result needs space to expand. ## 30. SORT and UNIQUE for useful lists SORT returns an ordered result without manually rearranging the source. UNIQUE removes repeated rows or values in its input. Clean inconsistent whitespace first, or labels that look similar may remain separate. Keep formula-generated output separate from editable source data. 1. Extract unique teams. 2. Sort the resulting labels. 3. Use the list as a report aid while retaining the original records. ```text =SORT(UNIQUE(D2:D6)) ``` Practice: Predict the two team names before testing in Sheets. Check: Does a SORT formula need to reorder the original source? Answer: No — It produces a separate sorted result. ## 31. Dates as usable values Dates are stored as serial values with date formatting. Subtract dates to measure elapsed days, but define whether endpoints and time-of-day matter. Construct dates explicitly and avoid mixing text dates with numeric dates in one column. 1. Create start and end dates with DATE. 2. Subtract start from end. 3. Apply a number format to the day difference. ```text =DATE(2026,10,20)-DATE(2026,10,14) ``` Practice: Check that the elapsed difference is 6 days. Check: Why might a date-looking string fail in a calculation? Answer: It may still be text — Appearance alone does not establish the stored type. ## 32. Workdays and changing dates TODAY changes as time passes, which makes it useful for an age or overdue report but unsuitable as a permanent timestamp. NETWORKDAYS can count workdays with a supplied holiday range. Specify your business calendar and whether the count includes endpoints. 1. Keep a fixed created date in source data. 2. Use TODAY only for a live age calculation. 3. For working days, supply the relevant holiday list. ```text =NETWORKDAYS(DATE(2026,10,12),DATE(2026,10,16)) ``` Practice: In a default Monday–Friday calendar with no holidays, predict 5. Check: Is TODAY a fixed creation timestamp? Answer: No — It recalculates over time; a stored timestamp is a different requirement. ## 33. Split and combine imported text SPLIT separates text around a delimiter; TEXTJOIN assembles values with a delimiter. Choose a delimiter that does not conflict with the data. A comma inside a quoted CSV field is not a safe place for a naïve text split. 1. Split a simple pipe-delimited label. 2. Inspect how empty pieces should behave. 3. Use a proper import process for CSV with quotes and embedded delimiters. ```text =SPLIT("North|Notebook","|") ``` Practice: Predict the two output cells. Check: Should complex quoted CSV always be split on every comma? Answer: No — CSV quoting rules can make commas part of a field. ## 34. Find patterns with regular expressions REGEXMATCH tests text against a pattern; REGEXEXTRACT returns a matching portion; REGEXREPLACE changes matching text. Start with a narrow pattern and a few representative examples. A pattern match is not proof that an address or identifier exists. 1. Test a simple SKU pattern. 2. Add start and end anchors to require a whole-string match. 3. Test valid, invalid and empty values. ```text =REGEXMATCH("SKU-204","^SKU-[0-9]+$") ``` Practice: Predict whether SKU-204x should match. Check: What do ^ and $ usually constrain? Answer: Start and end of the text — Anchors prevent a small valid substring from accepting an otherwise invalid value. ## 35. ARRAYFORMULA and spill areas Array formulas can calculate many results from one formula. Reserve the output range and avoid typing into its spill area. A bounded example is easier to audit first; extend it intentionally when your source grows. Not every function needs ARRAYFORMULA to return an array. 1. Create an empty result column. 2. Multiply Qty and Price for all five sample rows. 3. Check each result against a manual calculation. ```text =ARRAYFORMULA(B2:B6*C2:C6) ``` Practice: Expected line totals: 36, 16, 10, 24, 20. Check: What can block an expanding array result? Answer: Existing values in its output area — A spill cannot overwrite occupied destination cells. ## 36. QUERY: describe a report QUERY accepts a data range, a query string and a header count. The query language is distinct from a normal cell formula. Mixed data types within a column can produce surprising null values, so keep source types consistent. Start with a simple select before adding grouping. 1. Use A1:D6 with one header row. 2. Group by Team and sum Qty. 3. Compare totals with the dashboard explorer. ```text =QUERY(A1:D6,"select D, sum(B) group by D",1) ``` Practice: Confirm North = 12 and South = 3 in real Sheets. Check: What does the final 1 specify here? Answer: One header row — The header argument tells QUERY how many source header rows to treat as labels. ## 37. Pivot tables: summarize without nesting A pivot table groups records and computes summaries. Start with a clear question such as units by team. Choose the aggregation deliberately: COUNT is not SUM. Make sure the source includes all intended records before trusting a report. 1. Create a pivot from the complete source. 2. Put Team in Rows and Qty in Values. 3. Set the summary to SUM and compare totals to the source. ```text Rows: Team Values: SUM of Qty ``` Practice: Use the dashboard explorer’s team summary as a rehearsal. Check: Why inspect the aggregation choice? Answer: COUNT and SUM answer different questions — A count of orders differs from the number of units ordered. ## 38. Conditional formatting custom formulas A custom formatting formula returns true or false for the first cell in the applied range, then adjusts across the range. Anchoring the tested column is often essential when highlighting entire rows. This changes appearance, not values. 1. Apply a rule to A2:D6. 2. Choose Custom formula is. 3. Use =$B2>=3 to highlight rows with quantity at least 3. ```text =$B2>=3 ``` Practice: Use the reference explorer to explain the locked B and moving row. Check: Why is B anchored in this rule? Answer: Every column should test the same row’s Qty — The column stays B while the row follows each record. ## 39. Choose a chart that answers a question A chart should make a comparison clear. Bars work well for category comparisons; lines show ordered time trends. Label units and avoid decoration that obscures scale. Include the underlying numbers so readers can inspect the result. 1. Summarize units by team. 2. Choose a bar or column chart. 3. Add a descriptive title and retain the summary table. ```text North: 12 units South: 3 units ``` Practice: Switch between units and revenue in the dashboard explorer. Check: Which is a sensible chart for two category totals? Answer: Bar chart — Bars make category magnitudes easy to compare. ## 40. Build a small dashboard A dashboard links a few useful metrics to a clearly defined source. Define each metric, its filter scope and update process. Separate raw inputs, calculations and presentation. Check that all cards and charts use the same intended records. 1. Choose total orders, units and revenue. 2. Add a team filter. 3. Verify that metrics update consistently when the filter changes. ```text Revenue = SUM of each Qty × Price ``` Practice: Change quantities in the lab, then inspect the dashboard. Check: Why define filter scope? Answer: So readers know which records each metric represents — Metrics can mislead when they silently summarize different populations. ## 41. Custom formula or custom function? A custom cell formula combines built-in functions. A conditional-formatting formula returns a rule result. A named function packages a reusable formula; an Apps Script custom function uses JavaScript. Choose the simplest approach that can express the requirement. 1. Write the calculation as a normal formula first. 2. Use a named function for repeated formula logic. 3. Use Apps Script only when JavaScript adds a real benefit. ```text =ROUND(B2*C2,2) ``` Practice: Try the function builder’s LINE_TOTAL recipe. Check: Which option reuses formula logic without JavaScript? Answer: Named function — Named functions package formulas with named inputs. ## 42. Design a named function Give a reusable calculation a clear name and argument contract. In Sheets, use Data → Named functions, define placeholders and a formula, then describe each argument. The builder supplies ready-to-copy recipes; it does not install anything in your account. 1. Open the builder and choose LINE_TOTAL. 2. Create placeholders qty and price in Sheets. 3. Define =ROUND(qty*price,2), then call it with B2 and C2. ```text =LINE_TOTAL(B2,C2) ``` Practice: Change the builder’s test inputs and verify the output. Check: What are qty and price in this definition? Answer: Argument placeholders — Placeholders receive the values passed by the caller. ## 43. Test a reusable function Test ordinary values, zero, negative values and missing or invalid inputs. Decide which are allowed by the contract. Do not add a silent fallback unless it makes sense for the user. A function can be reusable and still require well-structured inputs. 1. Test LINE_TOTAL with 3 and 12. 2. Test a zero quantity. 3. Decide whether negative quantities represent returns or errors. ```text =LINE_TOTAL(0,12) ``` Practice: The builder should return 0 for this case. Check: Which test is easy to overlook? Answer: A boundary such as zero — Boundary cases reveal hidden assumptions. ## 44. LET: name intermediate results LET gives names to intermediate expressions inside one formula. This can reduce repeated calculation and make intent easier to read. Choose meaningful names and keep the formula’s data flow clear. Use a helper column when a single formula becomes too hard to explain. 1. Name the line total subtotal. 2. Name the percentage rate discount. 3. Return a rounded final value. ```text =LET(subtotal,B2*C2,discount,0.1,ROUND(subtotal*(1-discount),2)) ``` Practice: For row 2, predict 32.4 before using Sheets. Check: Why use LET? Answer: To name intermediate calculations — Names make repeated logic easier to follow. ## 45. LAMBDA and MAP LAMBDA describes a calculation with named inputs. MAP applies a LAMBDA to corresponding values in supplied arrays. Keep input dimensions compatible, and define the result for each item. Learn the basic single-row formula before generalizing it. 1. Multiply one quantity and price. 2. Pass both ranges to MAP. 3. Return one rounded total for each paired row. ```text =MAP(B2:B6,C2:C6,LAMBDA(qty,price,ROUND(qty*price,2))) ``` Practice: Expected outputs match the five line totals in lesson 35. Check: MAP pairs values from… Answer: Corresponding positions in its input arrays — Its LAMBDA receives corresponding input values. ## 46. Share and maintain named functions Document units, expected inputs and edge cases. Named functions can be imported into another spreadsheet. Check dependencies and name collisions when importing. A reusable function is easier to maintain when its definition is tested and its purpose is narrow. 1. Write a short description and one example call. 2. Import the named function into a separate practice file. 3. Review any existing definition before replacing it. ```text Name: LINE_TOTAL Inputs: qty, price Output: rounded numeric total ``` Practice: Download a recipe from the builder and use it as setup notes. Check: What should you check before importing? Answer: Existing names and dependencies — An import can conflict with existing definitions or rely on other named functions. ## 47. Write an Apps Script custom function A custom function is a JavaScript function callable from a cell. Open Extensions → Apps Script, add the function, and save. A range argument arrives as a two-dimensional array. The browser code lab runs pure JavaScript only, not Google services. 1. Open the code lab’s starter. 2. Test a scalar and a two-dimensional range. 3. Copy the validated example into a bound Apps Script project. ```text /** @customfunction */ function DOUBLE_VALUES(value) { return Array.isArray(value) ? value.map(row => row.map(n => n === "" ? "" : n * 2)) : value === "" ? "" : value * 2; } ``` Practice: Run all three local fixtures before trying =DOUBLE_VALUES(B2:B6) in Sheets. Check: How does a range arrive in a custom function? Answer: As a two-dimensional array — Each row is an array, even for a single-column range. ## 48. Know custom-function limits Custom functions should return values, not edit arbitrary cells. They have a 30-second execution limit and cannot request authorization for services that need it. Use a menu-driven script for authorized actions such as writing elsewhere. Pass changing cell dependencies directly as arguments. 1. Keep the calculation deterministic. 2. Return a scalar or rectangular array. 3. Choose a normal script workflow when you need side effects. ```text =DOUBLE_VALUES(B2:B6) ``` Practice: Explain why sending email belongs in a different script workflow. Check: Should a cell custom function edit arbitrary unrelated cells? Answer: No — A custom function returns values to its result area; use other script entry points for side effects. ## 49. Make Sheets faster and easier to maintain Bound expensive calculations, avoid redundant imports, and separate repeated cleanup from downstream reports. Batch range processing in custom functions instead of making one slow call per cell. Measure before optimizing, and keep correctness checks when simplifying a workbook. 1. Identify repeated expensive formulas. 2. Reuse a cleaned source or shared intermediate result. 3. Recheck totals and edge cases after the change. ```text One range call → many returned results ``` Practice: Compare a single range custom-function call with many separate calls. Check: What is a useful first optimization? Answer: Remove duplicated expensive work — Reducing repeated work can improve both performance and maintainability. ## 50. Capstone: a dependable order report Build a small report that another person can understand and maintain. Include raw orders, validation, line totals, a team summary, a chart, and a documented reusable calculation. Reconcile the report to the source before sharing. Explain which inputs may be edited and how to add new rows. 1. Import or copy the sample grid. 2. Add line totals, a team summary and a chart. 3. Test zero quantity, a missing team and a new row; document what needs updating. ```text Orders: 5 Units: 15 Revenue: 106 North units: 12 ``` Practice: Use the dashboard and function builder, then complete the report in your own Google Sheet. Check: What makes the report ready to share? Answer: Reconciled totals and clear instructions — A useful report needs accurate results and a maintainable workflow.