Google Sheets

It can read a range, total or average a column, and look up the row matching a value you name. The point is not that it replaces a formula, it is that you ask in words and get the number back, which is faster than remembering how a lookup argument is ordered. Nothing in the sheet changes and no formula is added.

What you end up with. The number you wanted, with the rows it came from, and no new column of working left behind in your sheet.

Start with it bringing you the information and you deciding.

Never without. Ask which rows it counted. A total that quietly skipped a blank or a text entry in a number column looks exactly like a correct one.

Built on: Get spreadsheet values / Aggregate Column Data / Look up spreadsheet row / Get sheet names / Batch get spreadsheet

Your assistant can search your spreadsheets by name, content, and date, then list the tabs inside the ones it finds. This is the answer to having four files called something like budget and no memory of which is current. It reads metadata and values, so it can tell you which was updated last and what the tabs are actually called.

What you end up with. The right file and the right tab, found by describing what is in it rather than remembering what you named it.

Start with it bringing you the information and you deciding.

Never without. Confirm the file it found is the one in use before acting on it. The most recently edited copy is not always the copy your team treats as current.

Built on: Search Spreadsheets / Get spreadsheet info / Get sheet names / Batch Get Spreadsheet Values by Data Filter

It can append rows to the end of a table, and it can do the smarter version: match on a key column such as an invoice number or a lead ID, update the row if that key already exists, and add a new one if it does not. Appending only ever adds. The keyed version is where duplicates get prevented and also where they get created if the key is wrong.

What you end up with. This week's records in the tracker, in the right columns, without a paste that lands one column over.

Start with it doing the work and checking with you.

Never without. Pick a key column that is genuinely unique and check it. Matching on a name or an email that repeats will overwrite a row that belonged to someone else.

Built on: Append Values to Spreadsheet / Upsert Rows (Smart Update/Insert) / Get spreadsheet values / Append Dimension

This is a bounded routine rather than a one-off, and spreadsheets are one of the better places to try one. It reads the current rows, matches on a key, updates what changed and adds what is new. Run it by hand for a month and read the difference each time before it runs unattended, because the failure you are looking for is not an error message, it is a column that started arriving in a different order.

What you end up with. A tracker that is current on Monday morning without anyone having exported, sorted, and pasted anything.

Start with it doing the work and checking with you.

Never without. Keep a dated copy of the sheet before each unattended run. A routine that writes into a spreadsheet other people are also editing needs a version you can go back to.

Built on: Upsert Rows (Smart Update/Insert) / Get spreadsheet values / Batch update spreadsheet values / Append Values to Spreadsheet / Get spreadsheet info

It can find and replace across the sheet, sort a range on several columns at once, put a filter on the header row, and delete the empty rows and columns an export leaves behind. Sorting and replacing are recoverable through version history. Deleting rows or columns is the step that changes the shape of the sheet.

What you end up with. An export that is actually usable: sorted, consistent, and without the blank blocks that break every formula pointed at it.

Start with it doing the work and checking with you.

Never without. Have it tell you which rows or columns it is about to delete and wait. Deleting a dimension shifts everything below or beside it, so formulas elsewhere in the file start pointing at the wrong cells and no error appears.

Built on: Find and Replace in Spreadsheet / Sort Range / Set Basic Filter / Delete Dimension (Rows/Columns) / Auto-Resize Rows or Columns

It can write values into a named range, or into several ranges in one operation, which is the monthly figures update that otherwise gets done cell by cell. Clearing a range first removes the values and leaves the formatting in place. What you cannot get back through the connector is whatever was in those cells, including a formula that gets replaced by a pasted number.

What you end up with. The current month's figures in place, in the same cells the rest of the workbook already refers to.

Start with it doing the work and checking with you.

Never without. Have it read the range back to you before it writes. Clearing a range that held formulas rather than typed numbers silently turns a calculated model into a set of stale constants.

Built on: Update spreadsheet values / Batch update spreadsheet values / Batch Clear Spreadsheet Values / Get spreadsheet values

It can create a chart from a range, list the charts already in the workbook, and move or resize one, including onto its own sheet. Useful when you know what comparison you want to show and do not want to work through the chart editor to get it. The chart stays linked to the range, so it updates when the data does.

What you end up with. A chart on the sheet, pointed at the right range, ready to copy into a document or a deck.

Start with it doing the work and checking with you.

Never without. Check the axis and the range it picked. A chart that reads well and plots the wrong column is more convincing than a spreadsheet full of the same mistake.

Built on: Create Chart in Google Sheets / List Charts in Google Sheets / Move or Resize Chart in Google Sheets / Update Chart in Google Sheets / Get spreadsheet values

It can add a tab, put dropdowns and validation rules on the columns people type into, apply conditional formatting so problems show up in color, hide working columns, and lock the layout with sensible row heights. This is the setup work that makes a shared sheet survive contact with a team, and it is the same set of steps every time.

What you end up with. A shared sheet with dropdowns where free text used to be, so the data comes back in a shape you can actually total.

Start with it doing the work and checking with you.

Never without. Have it read the existing rules back before it changes any. Conditional format rules are addressed by their position in a list, so a delete aimed at the wrong index removes a rule somebody else set up and there is no record of what it was.

Built on: Set Data Validation Rule / Mutate conditional format rules / Add Sheet to Existing Spreadsheet / Update Sheet Properties / Update Dimension Properties (Hide/Unhide & Resize) / Get Data Validation Rules / Get conditional format rules