Article

Build a weekly sales dashboard from Google Sheets

For small-shop owners, choose a Sheets pivot or a no-cost Data Studio report, then reconcile weekly sales and share with named viewers.

Editorial illustration for Build a weekly sales dashboard from Google Sheets: a document represents the research briefing. Not documentary evidence.

Keep a Google Sheets pivot table if it already answers your shop’s weekly sales question. Add Data Studio when a reusable visual report or report-only access justifies maintaining another interface. As of November 18, 2026, Google lists Data Studio at no charge for creators and viewers; the direct Sheets workflow below needs neither AI nor a Pro subscription.

Assume an illustrative small retail shop with one owner and one assistant, both nontechnical: existing permitted Google accounts, Sheets access, enough storage, one currency and about 300 sales-line records a month. Its additional software budget is US$0, excluding existing subscriptions, internet, equipment and staff time. These are planning assumptions, not a customer case or a product capacity limit.

The goal is a weekly view of recorded sales less refunds—not bookkeeping, profit or automatic transaction imports. Connect a clean reporting worksheet to Data Studio, select a completed week and reconcile its totals before sharing with named viewers.

Is a pivot enough, or do you need Data Studio?

Decide what the report must answer before choosing software: the week’s total, its daily pattern and, if recorded, its sales channels. Then ask whether the source is complete, whether this view will be reused, who should see it and who will maintain it.

Google’s current terms identify Data Studio as formerly Looker Studio.

Option and fit

Additional software cost

Ownership and access

What you maintain

Existing Sheets pivot: a weekly summary that already works

US$0 with the assumed existing access and storage

Existing workbook permissions; name a maintainer

Source range, dates, definitions and record checks

No-charge Data Studio: a reusable visual report

No charge for creators or viewers

Individual ownership; report access and source authorization are separate

The source plus report fields, charts and sharing

Data Studio Pro: an organizational ownership need

Listed US$9 per user per project per month; subscription length affects pricing

Organization-owned content linked to a Cloud project; team workspaces

Paid administration plus the same source-quality checks

Pricing checked November 18, 2026 against Google’s product page; ownership features are described in the Pro overview. Pro is outside this zero-additional-budget setup. Its list rate is not a tax-inclusive local quote or a verified two-person bill: confirm licensing, subscription duration, currency and taxes before purchasing. Two readers do not by themselves establish two paid licences.

To try the existing-tool option, select your clean source cells, including their headers, and choose Insert → Pivot table. Put the reporting date in Rows and the reporting amount in Values, summarized by SUM. Filter to the completed week you want. Add a channel breakdown only if that field exists. Google’s pivot instructions explain the controls and source-range editing.

A pivot refreshes when its source cells change; that does not prove a fixed range includes newly appended rows. Check the range. If the result is accurate and its workbook access suits both people, stop here.

What should the sales worksheet contain?

Make one reporting worksheet with a single header row, unmerged cells and consistent dates and numbers. Google recommends this simple structure in its Sheets preparation tutorial. Keep the original transaction history separately; a reporting copy should not become a second, conflicting record.

For this shop, use the following proposed conventions. They are a small reporting design, not an accounting standard or a set of Google-required column names:

  • Line ID: a unique reference for each sale line or refund adjustment. An order with several items can occupy several rows.

  • Reporting date: a complete date. For this example, record sales on their sale dates and refunds on their refund dates.

  • Reporting amount: a number in one currency, with sales positive and refunds negative. Document how the existing records treat discounts, shipping and tax; do not silently change that meaning.

  • Channel, if available: a consistent label for the recorded sales channel. Omit the breakdown if the information is absent.

Leave customer names, contact details and payment identifiers out of this reporting dataset. Do not mix individual product lines with whole-order totals. Exclude subtotal and grand-total rows: Google’s tutorial shows how totals can be counted twice when they sit alongside the records being summed.

Call the result “recorded sales less refunds” and display the currency and date convention. It is not profit, and counting sale lines is not counting orders. Keep expense records separate; the guide to receipts and reimbursements in Sheets or Zoho Expense addresses that different task.

How do you connect the worksheet?

Use a business-approved account with source access. Check storage eligibility first: Google’s connector documentation currently lists Team Drive files as unsupported. Resolve organizational restrictions with the administrator; do not move governed records just to get around them.

  1. In Data Studio, choose Create → Data Source and select Google Sheets.

  2. Select the spreadsheet and reporting worksheet. Each Sheets source connects to one worksheet. Use the first row as headers.

  3. Choose the whole cleaned worksheet or a deliberate range, then click Connect. Check that any fixed range covers future entries.

  4. Inspect the fields: date as a date, amount as numeric, and identifiers and channel as labels. Use SUM for the reporting amount, not a row count.

In Data Studio, a dimension groups records—for example, by channel—while a metric measures them. Here the metric is the sum of Reporting amount. Inspect these choices rather than assuming every detected field is correct. The data-source tutorial explains field configuration.

Hidden and filtered cells are included by default; the exclusion option does not apply to filtered views. Hiding rows is not access control. If columns change later, refresh the source fields and check the affected charts again.

Which charts and dates should you use?

Choose Create Report from the configured source and add it to the report. Name the report. Google’s report tutorial explains creation and layouts: consider Responsive for regular phone viewing, or Freeform for desktop placement. Have the intended reader check readability.

Using Add a chart and the chart’s Setup panel, build this proposed minimal view with your shop’s fields:

  • One total: a scorecard with SUM of Reporting amount.

  • A daily trend: a time series with Reporting date and SUM of Reporting amount.

  • A channel table, when meaningful: Channel and SUM of Reporting amount. Check missing channel labels before interpreting the breakdown.

Add a short visible explanation of the amount, currency and refund-date convention. Google’s tutorial uses Analytics examples; your report needs the shop’s fields, not website views or sessions.

Next choose Add a control → Date range control. Set explicit start and end dates for one completed business week. The Sheets date default includes all available data, so an impressive-looking initial total may not be a weekly figure at all.

For each chart, set Reporting date as its Date Range Dimension and check that the control changes it. Remove unintended fixed-date overrides or grouping that excludes a chart from the control. Compare the same completed dates in Sheets.

If you later adopt a rolling “last week” setting, select the intended week start deliberately. Google documents Sunday-start defaults and Monday-start alternatives. A rolling seven-day window including today is not necessarily your last completed trading week.

How do you know the totals are ready to use?

Make reconciliation—not a finished layout—the acceptance condition. These are proposed checks for the owner and assistant to perform:

  1. Check that all intended sales and refunds reached the reporting worksheet. Compare it with the original records, not only with another chart.

  2. Match the report total to a checked Sheets total for exactly the same dates and amount convention. Confirm daily and channel subtotals account for the same records.

  3. Investigate missing entries, repeated Line IDs, numbers stored as text, incorrect refund signs, subtotal rows, excluded dates and unintended filters. Preserve the correction history instead of silently deleting suspected duplicates.

For a hypothetical arithmetic check, suppose the week contains sales of 25 and 40 currency units and a refund entered as −10. The result is 25 + 40 − 10 = 55. If a subtotal row of 55 is also included in the sum, the incorrect result is 110. This illustrates the documented double-counting problem, not a result from a tested shop dashboard.

Agreement between a pivot and a dashboard is necessary but insufficient if both omit the same transactions. Check the underlying records and what the number means. The same distinction matters when checking metric definitions in an AI-generated dashboard: a plausible display does not establish a correct calculation.

How can the assistant view the report privately?

There are two access decisions: who may open the report, and whose authorization it uses to read the source. Google’s data-credentials guidance distinguishes them:

  • Owner’s Credentials: the report uses the credential owner’s authorization. Viewers can see the intended report data without direct spreadsheet access; this does not grant them the right to open the source itself.

  • Viewer’s Credentials: each viewer needs their own permission to the underlying dataset. Choose this when source access is intended, not as a workaround for a sharing error.

A separate tab organizes reporting data; it does not create separate workbook permissions. If the assistant should only read the report, do not grant workbook or report-editing access merely to make an error disappear.

Open Share, add the assistant’s named Google account and choose Can view. Keep public link sharing off. Named invitations require a Google login; separately sharing a reusable data source is not required just to view its report. Follow Google’s named-sharing instructions and have the assistant check access using their own account.

Inspect every page before sharing: report access includes all pages, not just the first sales summary. Limit the connected reporting data to what this audience should receive.

Restricted sharing is not local-only processing. Under Google’s standard Data Studio terms, customers retain rights in their data, but processing can occur where Google or its agents have facilities. Use only records the business is permitted to process, and check the agreement applicable to its account; do not assume an existing Workspace contract covers this service.

Who updates it, and what does refresh actually mean?

Track two separate things: when the sales log was checked for completeness, and when the report last fetched data. A current report can still display an unfinished sales log.

As checked November 18, 2026, the Sheets freshness default is 15 minutes, with one-, four- and twelve-hour options. Data Studio can reuse stored query results within that interval; an expired interval or new query can trigger a fetch. This is not an automatic import from a till and does not certify that every sale has been entered.

An editor can use More options → Refresh data. Viewer refresh depends on the report setting, and manual refresh has a one-minute cooldown. Refresh after the source is ready, then reconcile; repeatedly reloading cannot supply missing transactions.

For the illustrative two-person shop, assign the assistant to maintain the log and a dated completeness note, and the owner to review the completed week and resolve discrepancies. Arrange cover for absences. If the assistant maintains the source, grant the necessary source permissions deliberately; report-viewing rights alone are a different role.

After the review, retain the authoritative records and permitted original exports, plus a dated PDF report snapshot if useful. A PDF is neither a complete data backup nor an editable dashboard; it will not change when a later correction changes the sheet. Keep a discrepancy note and review credentials during staff handover.

When are Pro or AI worth reconsidering?

For this workflow, neither is a prerequisite. Pro’s organization-owned content and team workspaces may matter if individual ownership creates a real handover problem. Its 30-day trial begins billing automatically unless cancelled; technical support also requires a Google Cloud support plan. Those are separate budget decisions, not steps needed to total a worksheet.

Optional AI assistance is more narrowly available than “having Gemini.” As of November 18, 2026, calculated-field assistance is a preview requiring Pro, report-editor access, field editing and Gemini enabled for the project. Google says Gemini is unsupported for Pro subscriptions bought through the Workspace Admin console. Google says additional fees may apply after the preview; do not assume a permanent AI allowance.

If you already have eligible assistance, treat a proposed formula as something to verify against known records. It cannot decide what your shop means by a sale or refund. Do not buy a new AI subscription just to sum a maintained numeric column.

Keep the pivot if it answers the question. Add the report only when its reusable view or separate viewer access earns the upkeep. If the real problem is missing transactions, multiple-system imports or accounting, address that problem first: a new chart is not the missing record.