Documentation
¶
Overview ¶
Package sheets is the built-in Google Sheets store: a Backend and EventLog over one spreadsheet, one tab per table, and a run lease held as a file in a Cloud Storage bucket.
- Every Commit is one spreadsheets.batchUpdate, which Sheets applies all-or-nothing. Rows are changed in place (rewritten tabs keep their sheet ids, so protection, filters and team formulas survive); growing tables are appended to, never rewritten.
- A tab is created by the first write that names it, at exactly its column count with a frozen header row, because Sheets counts every grid cell toward its 10-million-cell cap. Health is the one exception: its cell H1 holds the staleness formula, rewritten by every commit that writes Health.
- Tool tabs are written as raw text and read unformatted, so a value that starts with "=" stays text; people-owned tabs (Overrides, Companies, Leads) are read formatted.
- Raw receiver events go to one tab per UTC month of their received time ("Events 2026-10"). An event's sequence is its (tab, row); a cursor holds a position for every tab.
With `base_url` set in its block (tests), every Google client points there and skips authentication; `_http_client` supplies the HTTP client.
Index ¶
- Constants
- Variables
- func CanEdit(role string) bool
- func Create(ctx context.Context, svc *Services, t Template) (string, error)
- func EventsTab(t time.Time) string
- func Formula(header []string, rows []api.Row) string
- func FormulaRequest(sheetID int64, formula string) *sheetsapi.Request
- func QuoteTab(name string) string
- func Repair(ctx context.Context, svc *Services, id string, acc Accounts) error
- func Share(ctx context.Context, svc *Services, id string, acc Accounts) error
- func SharedWith(ctx context.Context, svc *Services, id string) (map[string]string, error)
- func UTCZone(tz string) bool
- func ViewConfig(store api.Config, spreadsheet string) api.Config
- type Accounts
- type Info
- type Services
- type Store
- func (s *Store) AppendEvents(ctx context.Context, events []api.RawEvent) error
- func (s *Store) Commit(ctx context.Context, writes []api.TableWrite) error
- func (s *Store) DeleteProcessed(ctx context.Context, committed api.Cursor, olderThan time.Time) (api.Cursor, error)
- func (s *Store) Lease(ctx context.Context, owner string, ttl time.Duration) (api.RunLease, error)
- func (s *Store) LeaseBucket() string
- func (s *Store) LeaseBucketAccess(ctx context.Context) (exists, writable bool, err error)
- func (s *Store) LeaseInfo(ctx context.Context) (string, time.Time, error)
- func (s *Store) ReadEvents(ctx context.Context, cursor api.Cursor) ([]api.RawEvent, api.Cursor, error)
- func (s *Store) ReadTable(ctx context.Context, name string) ([]api.Row, error)
- func (s *Store) Services() *Services
- func (s *Store) SpreadsheetID() string
- type TabCells
- type Template
Constants ¶
const ( StaleMessage = "STALE: no successful run in 3 intervals" NoSuccessMessage = "STALE: no successful run yet" )
StaleMessage is what Health!H1 shows when no run succeeded in three schedule intervals; NoSuccessMessage before any run has succeeded.
const ( TimeZone = "Etc/GMT" AutoRecalc = "HOUR" )
Spreadsheet settings setup writes and the `sheets` check expects. The staleness formula compares stored UTC times with NOW(), so the spreadsheet runs on UTC; HOUR recalculation makes NOW() move with no edits.
const HealthNote = "ok: a run succeeded within the last three schedule intervals. " +
"STALE: none did, so leadscore may have stopped; run `leadscore doctor`. " +
"The spreadsheet recalculates this every hour."
HealthNote explains Health!H1 to a person.
const LeaseObject = "leadscore-lease.json"
LeaseObject is the lease file in the lease bucket. Sheets has no compare-and-swap, so the lease lives in Cloud Storage: every write and delete is conditioned on the generation read just before (ifGenerationMatch, 0 when the file is absent), which Cloud Storage refuses if another run changed the file first. Two callers can never both hold it.
const MaxCellChars = 50_000
MaxCellChars is the most characters a Sheets cell holds.
Variables ¶
var LeadsHeaders = []string{"Email", "Full name", "Title", "Company", "Company domain", "LinkedIn URL"}
LeadsHeaders are the starting columns of the template's Leads tab: the built-in fields a team most often has, in words a person reads. The tab is the team's; they add, rename or remove columns freely.
var MaxCommitBytes = 9 << 20 // a variable so tests can reach Sheets' own limit
MaxCommitBytes is the largest encoded batchUpdate a commit sends; a bigger one is ErrTooLarge, under Sheets' 10 MB request cap.
var ReadOnlyScopes = []string{sheetsapi.SpreadsheetsReadonlyScope}
ReadOnlyScopes are what a reader of the team's tabs needs (sheetsource).
var Scopes = []string{ sheetsapi.SpreadsheetsScope, drive.DriveScope, storage.DevstorageReadWriteScope, }
Scopes are the Google sign-in scopes the store and its setup use: Sheets, Drive (sharing and the sheet-access check) and Cloud Storage (the lease).
Functions ¶
func Create ¶
Create makes a new spreadsheet from the template, owned by whoever svc signs in as (the person): every tab at its exact width with a bold, frozen header (notes on the Overrides and Companies headers), the machine-data tabs hidden, the people tabs green with room to type, Ranked and Health colored, hourly recalculation, UTC, editors unable to re-share, the tool tabs protected for the accounts (Events tabs for both, every other tool tab for the run account), and the staleness formula in Health!H1 with its note and a red highlight on STALE. It does not share the spreadsheet; call Share next. It returns the new spreadsheet's id.
func Formula ¶
Formula is the staleness formula for Health!H1, given the Health tab's header and its rows in sheet order (nil for a blank row). It names the cell holding the `last_success_at` result and compares it with NOW() against three `schedule` intervals; the schedule comes from the `schedule` result row (the run writes it), else the 15-minute default. Before any run has succeeded it shows NoSuccessMessage. The times compared are UTC, so setup sets the spreadsheet's time zone to UTC.
func FormulaRequest ¶
FormulaRequest writes a formula into H1 of the tab, as a person typing it would (USER_ENTERED), the one cell the store writes that way.
func QuoteTab ¶
QuoteTab quotes a tab name for an A1 range: 'Seen events', with any apostrophe doubled.
func Repair ¶
Repair puts an existing spreadsheet's settings back (`setup sheet --repair`): hourly recalculation, UTC, editors unable to re-share, protection on every tool tab not already fully protected, and the Health!H1 formula. It does not add or change tabs or rows.
func Share ¶
Share gives each account edit access to the spreadsheet, with no email sent. A Workspace sharing policy that blocks it comes back as Drive's own error.
func SharedWith ¶
SharedWith returns each account the spreadsheet is shared with, by lowercased email, and its Drive role. Listing needs the caller to be allowed to see the sharing; the caller decides what a failure means.
Types ¶
type Accounts ¶
type Accounts struct {
Run, Receiver string
}
Accounts are the service accounts the spreadsheet is shared with: Run writes every tool tab, Receiver appends to the Events tabs. On Docker one account is both.
func AccountsFrom ¶
AccountsFrom works out which accounts the spreadsheet is shared with. A Google Cloud install names them in `hosting` (run_account and receiver_account; a bare name is completed with the project's service-account domain). Otherwise the one account is the service account in `store.credentials`. The view is shared with the run account only.
type Info ¶
type Info struct {
Title string
AutoRecalc string
TimeZone string
Tabs []TabCells // every tab, largest first
Cells int64 // grid cells in the whole spreadsheet
}
Info is what the doctor checks read about a spreadsheet.
type Services ¶
Services are the Google API clients for one install.
func Connect ¶
Connect builds the clients from an adapter block (`store`, or a Sheet-tab source's entry), signing in with scopes (Scopes when none are given). With `base_url` set, every client points there and skips authentication; only tests set it, together with `_http_client` (RunWith adds both), so a `base_url` without a client, as a file could carry, is refused: it would send the team's data to whatever address the file names. Otherwise `_http_client`, when set, is an already-authenticated client (setup signs in as the person); else the store signs in with `credentials` (a service-account key file) or, when that is empty, Google's standard credential loading.
type Store ¶
type Store struct {
// contains filtered or unexported fields
}
Store is a Sheets Backend, EventLog and LeaseInspector.
func Open ¶
Open builds a store from the `store` block: `spreadsheet` is required; `lease_bucket` is needed to take the lease.
func (*Store) AppendEvents ¶
AppendEvents stores a batch in one batchUpdate, so all-or-nothing, each event in the tab of its received time's month, in order, each row with a fresh id in `seq`. It creates a missing month's tab (hidden) in the same request, protected like the existing Events tabs. It retries Google's "slow down" until ctx is done; when another writer created the same tab first, it reads the spreadsheet again and appends.
func (*Store) Commit ¶
Commit applies every write in one batchUpdate, all-or-nothing. It reads the tabs the writes touch, applies the writes to that copy in memory (so a duplicate append key or a bad write fails before anything is sent), and sends the difference as row-level requests: deleted rows, changed cells, and appended rows. It creates a missing tab and appends a missing column the first time a write names it, and returns ErrTooLarge rather than splitting.
People type into Overrides (and the other people-owned tabs) at any time, and a row delete is by position. So when a commit deletes or changes rows of such a tab, it re-reads the tab just before sending and refuses if it changed, and re-reads it after: a row that is gone but was not meant to go (the person sorted rows at that moment) is put back and the commit returns ErrCommittedWithProblems (everything was saved; a person should look).
func (*Store) DeleteProcessed ¶
func (s *Store) DeleteProcessed(ctx context.Context, committed api.Cursor, olderThan time.Time) (api.Cursor, error)
DeleteProcessed deletes whole monthly tabs: a tab goes when every row in it is at or below committed (and its last row is the one committed read), its month ended before olderThan (so every event in it is older), and its month plus the one-hour grace has passed, so no receiver can still append to it. The newest Events tab is never deleted, so a new month's tab always has a protected sibling to copy. Each deleted month is recorded in the same batchUpdate (see deletedMetadataKey). It returns committed without the deleted tabs; the engine saves that cursor. Rows are never deleted one by one: that would move the positions cursors hold.
func (*Store) Lease ¶
Lease takes the run lease, or returns ErrLeaseHeld while another owner's lease has not expired. An expired lease is taken over; the same owner taking it again extends it.
func (*Store) LeaseBucket ¶
LeaseBucket is the lease's Cloud Storage bucket; empty when none is set.
func (*Store) LeaseBucketAccess ¶
LeaseBucketAccess reports, without writing anything, whether the lease bucket exists and whether this account may write, read and delete the lease file in it (the doctor's `lease` check). It asks Cloud Storage's testIamPermissions, which needs no permission of its own, so an account holding only Storage Object Admin on the bucket can still ask.
func (*Store) ReadEvents ¶
func (s *Store) ReadEvents(ctx context.Context, cursor api.Cursor) ([]api.RawEvent, api.Cursor, error)
ReadEvents returns the events after cursor, tab by tab (oldest month first) and row by row, and the cursor after the last one. Each event's Seq is the cursor just after it. It returns ErrEventsShrank when a tab the cursor names is missing (unless DeleteProcessed recorded deleting it), holds fewer rows than the cursor read, or no longer has the last row read at its place (rows were sorted, inserted or deleted).
func (*Store) ReadTable ¶
ReadTable returns every row of a tab, skipping blank rows; a missing tab returns no rows and no error. Columns are the named header cells.
func (*Store) SpreadsheetID ¶
SpreadsheetID returns the spreadsheet the store writes.
type Template ¶
type Template struct {
Title string
View bool // the SQLite view: Ranked, Health and the export tabs only
Accounts Accounts // who the tool tabs are protected for
Now time.Time
// ExportLanes are the rubric's export lane ids; the view gets a tab each
// (the store gets them from the run's first write).
ExportLanes []string
}
Template says what Create makes.
Source Files