VERA

Appearance

Reporting Sheet ​

The reporting spreadsheet is a separate Google file from the database. It contains human-facing dashboards built with Google Sheets formulas. The bot never reads or writes this file.

Architecture ​

Database Sheet (bot reads/writes)
        ↓
   IMPORTRANGE (via IMPORT_DB named function)
        ↓
Hidden mirror sheets (one per database tab)
        ↓
Visible dashboard tabs (built with LET/FILTER/MAP formulas)

Each database tab is mirrored into a hidden sheet in the reporting spreadsheet. Visible dashboard tabs pull from these mirrors — not directly from the database sheet.

IMPORT_DB formulas ​

Place in cell A1 of each hidden mirror sheet:

01. Time Entries:     =IMPORT_DB("01. Time Entries!A:H")
02. Projects:         =IMPORT_DB("02. Projects!A:L")
03. Users:            =IMPORT_DB("03. Users!A:G")
04. Clients:          =IMPORT_DB("04. Clients!A:D")
05. Tasks:            =IMPORT_DB("05. Tasks!A:C")
06. Project_Tasks:    =IMPORT_DB("06. Project_Tasks!A:D")
07. Budgets:          =IMPORT_DB("07. Budgets!A:I")
08. Disciplines:      =IMPORT_DB("08. Disciplines!A:C")
10. Pending_PTO:      =IMPORT_DB("10. Pending_PTO!A:I")
11. Unresolved_Items: =IMPORT_DB("11. Unresolved_Items!A:I")
12. Company_Calendar: =IMPORT_DB("12. Company_Calendar!A:D")
13. Rate_History:     =IMPORT_DB("13. Rate_History!A:D")
14. Liabilities:      =IMPORT_DB("14. Liabilities!A:H")
22. Projects Master:  =IMPORT_DB("'22. Projects Master'!A:P")

IMPORT_DB is a named function wrapping IMPORTRANGE with the database sheet URL. Only 22. Projects Master uses single quotes inside the string.

Dashboard tabs ​

TabPurpose
00. DashboardKPI cards + portfolio health table
01. SummaryClient-level aggregation
02. Projects MasterFull project list with financials
03. Project PerformancePer-project delivery metrics
04. FP VarianceFixed Price margin analysis
05. Retainer AnalysisBurn rate and hours remaining
06. League TablePM performance rankings
07. FinancialsRevenue, cost, margin by project type
08. HistoricalRolling 12-month trends
09. Rate TablesRate history per team member
10. ExportRaw time entries with resolved names

See the column headers and formula patterns for each tab below.

Tab headers ​

00. Dashboard

Project Name, Client, Type, Hours Logged, Hours Budget, % Budget Used, Health Status

01. Summary

Client Name, Active Projects, Total Hours Logged, Total Hours Budget, % Budget Used

02. Projects Master

Project Name, Client, Project Type, Billable, Contract Value ($), Hours Budget, Actual Hours, Labor Cost ($), Hour Variance (hrs), Cost Variance ($), Variance %, Billing Rate ($/hr)

03. Project Performance

Project Name, Client, Project Type, Hours Logged, Hours Budget, % Budget Used, Hour Variance, Cost Variance ($), Health Status

04. FP Variance

Project Name, Client, Contract Value ($), Hours Budget, Actual Hours, % Complete, Earned Revenue ($), Actual Cost ($), Margin ($), Margin Variance ($)

05. Retainer Analysis

Project Name, Client, Contract Value ($), Hours Budget, Hours Used, Burn Rate, Hours Remaining

06. League Table

Project Manager, Active Projects, Under Budget, Under Budget %, At Warning, Warning %, Over Budget, Over Budget %, Avg Utilisation, Performance Score

07. Financials

Project Name, Client, Project Type, Billable, Contract Value ($), Hours Logged, Revenue ($), Cost ($), Margin ($), Margin %

08. Historical

Month, Total Hours, Time Entries, Avg Hours per Entry

09. Rate Tables

Team Member, Employment Type, Hourly Cost ($), Effective From

10. Export

Date, Team Member, Project Name, Task Name, Hours Logged, Notes, Billable

Percentage convention ​

All formulas output percentages as decimals (e.g. 0.85, not 85). Format the column as "Percent" in Google Sheets to display as 85%.

Threshold comparisons use pct >= threshold/100 (thresholds are stored as whole numbers like 80). Over-budget = pct >= 1.

Status values ​

The Projects Master status column uses "Active" (capital A). The 02. Projects sheet uses "active" (lowercase).

  • FILTER = is case-insensitive — both work
  • QUERY WHERE is case-sensitive — use 'Active' not 'active' in QUERY clauses

See Formula Rules for the full list of Google Sheets constraints.

Further reading ​

VERA by talktalkmake