Once a brand's marketplace data lands in Google Sheets every morning, the next request is always the same: 'Can we see it properly?' Our client wanted one professional dashboard per channel, in that channel's folder, that anyone on the team could open without a new tool or login. We built it in Google Sheets using formulas only. Scripts fill the data; everything you see is calculated live.
Step 1: one order format for every marketplace
Myntra, Nykaa, Flipkart and Amazon all export orders differently. Before building any chart, we defined a single Orders layout — one row per order line, with date, order and line ID, SKU, product, brand, category, quantity, selling price, status, fulfilment type, state, city and so on. Each channel's data is converted into that layout as it's loaded. Because every dashboard reads the same columns, building the next channel is a copy, not a rebuild.
Step 2: keep the maths in a hidden tab
The Dashboard tab only shows things. A hidden Calc tab turns the pickers into actual date ranges (yesterday, last 7 days, last 30 days, this month, or a custom range) and runs QUERY formulas for each block: totals, the same totals for the previous period, daily trend, top SKUs, states, cities, categories, brands and order statuses. Keeping calculations separate means the visible tab can be restyled without breaking anything.
Step 3: show the comparison, not just the number
The top of the dashboard has twelve cards — orders, units, revenue, average order value, returns and more — each compared with the previous period of the same length. Below them sit the trend chart, top products, and a stock-health section that lists what's about to run out and what hasn't sold at all.
The gotchas
SUMIFS doesn't work row by row inside ARRAYFORMULA — it quietly returns a single total. MAP with LAMBDA (or BYROW) does, so the stock columns use that. Charts showed dates as five-digit serial numbers until the helper column feeding them was formatted as a date. And the custom From/To date boxes confused people when another period was selected, so they are now greyed out unless 'Custom range' is chosen. The first version also felt cramped; smaller fonts, more spacing and taller rows fixed it without removing anything.
Change the look without changing the link
The dashboard is created and restyled through the Google Sheets API. When the layout changes, the script clears and redraws the dashboard tab in place, so the link the team bookmarked keeps working. We back-filled the previous month of orders on day one so the comparisons meant something immediately.
When a Sheet is the wrong tool
Google Sheets is excellent for daily reporting up to a few hundred thousand rows, with a team that already lives in Drive. Once you need to track each order's state over several days, have several systems writing at once, or keep years of history, you want a real database with the Sheet as a view on top. We drew exactly that line for a client this month — the trick is not crossing it too early.
Want this for your own marketplace data? See how the data gets there in our sales and stock reporting case, or book a call.
Frequently asked questions
Can you build a sales dashboard in Google Sheets without Looker Studio or Power BI?
Yes. With a clean data tab, a hidden calculation tab using QUERY and MAP/LAMBDA formulas, and charts on a dashboard tab, Google Sheets handles daily marketplace reporting well for most small and mid-size brands.
Why does SUMIFS not work inside ARRAYFORMULA?
SUMIFS doesn't evaluate row by row inside ARRAYFORMULA; it returns one total. Use MAP with LAMBDA or BYROW to calculate a value per row.
Why does my Google Sheets chart show numbers instead of dates?
The column feeding the chart is formatted as a number, so dates appear as serial numbers. Format that helper column as a date.
When should I move from Google Sheets to a database?
When you need to track each record's state over several days, have several systems writing at the same time, or keep large multi-year history. The Sheet can stay as a view on top of the database.
Amit has 6+ years of experience building data pipelines, AI agents, and automation systems for businesses across India and globally. He founded HowAutomate to make enterprise-grade automation accessible to growing businesses.