GridPath

Docs / Rows, columns and sheets

Rows, columns and sheets.

Seven tools change the shape of a workbook. Formula references shift the way they do in Excel, and every change is a pending batch the user can reject.

Rows

insert_rows inserts count empty rows before 1-indexed row before; content from that row down shifts. delete_rows removes count rows from start; content below shifts up, and the removed rows are gone.

{ "path": "~/models/forecast.xlsx", "sheet": "Model", "before": 43, "count": 2 }
{ "path": "~/models/forecast.xlsx", "sheet": "Scratch", "start": 10, "count": 5 }

Columns

Same shape, with column letters. insert_columns inserts before column before; delete_columns removes from column start.

{ "path": "~/models/forecast.xlsx", "sheet": "Model", "before": "G", "count": 1 }

Adding a period to a model is usually insert one column, then copy_range the previous period's column into it, then set the header.

What shifts, and what might not

  • Formula references on every sheet shift to follow the moved cells, including sheet-qualified references from other sheets.
  • Conditional formatting and data validation ranges on the edited sheet follow the shift.
  • Chart series references and conditional-formatting rules elsewhere in the workbook may not adjust. Check the review page, and check the chart in Excel after saving, when inserting into a region a chart reads from.
  • Deleting rows or columns that formulas reference turns those references into #REF!, as in Excel. The readback and read_range show it.

Sheets

create_sheet adds an empty tab, optionally with a tab_color. Writing to a sheet name that does not exist creates it too, so an explicit create is only needed for a colored tab or an empty tab made ahead of time.

{ "path": "~/models/forecast.xlsx", "name": "Assumptions", "tab_color": "#22c55e" }

rename_sheet renames a tab and updates cross-sheet references in other sheets automatically.

{ "path": "~/models/forecast.xlsx", "old_name": "Sheet1", "new_name": "Inputs" }

delete_sheet removes a tab and every cell on it. Cross-sheet references to it become #REF!. Like every other write it is a pending batch until saved, so it can be rejected on the review page.

{ "path": "~/models/forecast.xlsx", "name": "Scratch" }

Not available inside run_script

None of these operations are in the run_script API. Scripts write cells; structure changes go through these tools, before or after the script.

Found a mistake? Open an issue or email support@gridpath.dev.