google-sheets

Google Sheets authoring

Two coordinate systems, on purpose

• VALUE tools (read_range, write_range, append_rows, clear_range)

address a TAB TITLE plus A1 cells: {sheet: "Data", cells: "A1:B4"}.

• STRUCTURE/FORMAT tools (format_cells, set_borders, merge_cells,

add_chart, add_conditional_formatting, insert_rows_or_columns, delete_rows_or_columns, freeze_rows_or_columns, sort_range, set_data_validation, set_basic_filter, add_banding, set_column_width, rename_sheet_tab, delete_sheet_tab) address the numeric sheetId.

get_spreadsheet returns both (each tab's title AND sheetId) with no cell data - call it first and keep the mapping. A new tab's sheetId is in add_sheet_tab's reply (replies[0].addSheet.properties.sheetId).

Sequencing a styled sheet: values first, then format, chart last

1. create_spreadsheet (optionally with named tabs; it cannot target a

folder - move it after with google-drive google_drive_move_or_rename).

1. write_range the data with parseInput: true when values include

numbers, dates, or formulas (default false stores everything as literal text - a top source of "chart is empty" bugs).

1. Format like a real dashboard, not a bare grid:

• format_cells bold header with a fill color and white text; number

patterns on value columns ($#,##0, 0.0%).

• freeze_rows_or_columns the header row.
• set_basic_filter over the table range - filter dropdowns are what

make it read as a data table.

• set_borders (outer heavier than inner) or add_banding for

alternating row colors; set_column_width so nothing clips.

• add_conditional_formatting for thresholds worth seeing at a glance.

1. add_chart LAST, after the data exists.

Charts

• In add_chart, the FIRST range is the domain (x axis / pie labels) and

later ranges are the series, one column each; anchoring a chart to the wrong or reversed ranges is the most common chart bug. All ranges live on the same tab as the chart. headerCount: 1 uses the first cells as series labels - include the header row in each range when you set it.

• Place the chart over empty cells beside or below the table, not on top of

the data it plots.

• A wrong chart is fixed in place, not worked around: get_spreadsheet

lists every tab's charts (chartId, title), update_chart replaces a chart's spec keeping its position, delete_chart removes one. Never add a second chart to paper over a bad first one.

Named and protected ranges

• add_named_range makes formulas self-documenting (=SUM(Budget) instead

of =SUM(B2:B13)); write the formula with parseInput: true. Names cannot look like cell references.

• protect_range guards headers and formula cells (warningOnly: true by

default warns editors; false locks them). Protect AFTER the last write to that range, or your own writes fight the protection.

• get_spreadsheet lists both, with the ids delete_named_range and

unprotect_range need.

Pitfalls

• No revision guard anywhere: last write wins. Read before writing when a

collaborator may be editing.

• merge_cells keeps only the top-left value; write the text after merging.
• sort_range rewrites cell positions; sort before adding formulas that

reference the range, and exclude the header row from the sorted range.

• insert_rows_or_columns / delete_rows_or_columns positions are 1-based

(A=1); deletes are immediate and not undoable through the API.

• find_replace_sheet is literal (never regex here) and can search inside

formulas with searchFormulas: true.

• Whole-tab reads can blow the byte cap; bound cells (e.g. "1:2000") on

tabs you have not sized and follow nextCells when truncated.

Verify your work

read_range for values (FORMATTEDVALUE shows what the user sees; UNFORMATTEDVALUE the raw numbers - use it to confirm numbers are numbers, not text). get_spreadsheet for structure: tabs, charts, named ranges, protections. google-drive google_drive_export_file (pdf renders every tab including charts and formatting) when the visual result matters.