Automated weekly reports to Google Sheets for accounting
Send sales numbers to Google Sheets for your accountant on a schedule: pick metrics, overwrite or append, set up a service account, keep formulas intact.
By The DataxLand team· · 5 min read
Accountants at many small businesses live in spreadsheets. They don't need another dashboard to open; they need the right numbers already sitting in the file they use to reconcile, post entries and report to the owner. In practice it often goes like this: every Monday, someone exports numbers from three or four places, pastes them into Google Sheets and sends the link over chat. On the Monday that person is off, the accountant waits.
This post shows how to set up an automatic weekly flow of numbers into Google Sheets: which metrics to send, whether to overwrite or append rows, how to grant access to the spreadsheet safely, and how to organize the file so the accountant's formulas don't break every time new numbers arrive.
Which numbers the accountant needs each week
Don't send everything. A weekly sheet for accounting should answer "what were revenue, fees and cost of goods this week, by channel" — enough to reconcile against cash received and the books.
| Metric | Split by | The accountant uses it to |
|---|---|---|
| Net revenue | Channel | Reconcile recorded revenue |
| Cost of goods | Channel | Reconcile stock issued |
| Marketplace fees, payment fees | Channel | Reconcile settlements and payouts to the wallet |
| Shipping paid by the shop, return fees | Channel | Record selling expenses |
| Ad spend | Channel | Reconcile ad invoices |
| Estimated-fee share | Whole shop | Know which part may still change |
| Orders, cancellation rate | Channel | Explain revenue movements |
The estimated-fee share is the row people forget, but it matters to the accountant: fees on orders not yet fully settled will still change. Send this share along so the accountant knows the most recent week shouldn't be closed yet.
Overwrite or append rows
The two write modes serve two different purposes.
| Overwrite | Append rows | |
|---|---|---|
| Each send | Clears the old tab, writes the new table | Adds new rows at the bottom of the tab |
| Top row | An "updated at" timestamp, then the header row and the numbers | The first column is each row's update time |
| Fits | "This week's numbers" for a quick look, reference tables | Week-by-week history for trends and back-reconciliation |
| Risk | Manual edits on the tab are lost at the next send | The tab grows longer; old weeks don't update when fees are finalized |
Note the "Risk" column for appending: last week's row is written with the fees estimated at the time it was sent. When fees are finalized later, the old row doesn't change. If the accountant needs finalized numbers, use a last month report that runs at the start of the following month, when most fees have been settled.
On size: Google allows up to 20 million cells or 100 MB per spreadsheet created in Google Sheets (Google Drive Help, accessed 16 Nov 2026). Illustrative example: 3 channels × 10 columns × 52 weeks ≈ 1,560 cells a year for a weekly sheet — nowhere near the limit.
Granting access to the spreadsheet: a service account
To let a system write to Google Sheets without using your Google password, the common approach is a service account: a machine account in your Google Cloud project, with its own email address, that can only write to spreadsheets you share with it.
Create a key for the service account following Google's documentation (Google Cloud — Create and delete service account keys, accessed 16 Nov 2026):
- In the Google Cloud console, open the Service accounts page and select a project (a dedicated project for this is tidiest).
- Create a new service account, then click its email address.
- Open the Keys tab, choose Add key → Create new key, select JSON and click Create. The key file downloads to your computer; Google lets you download it only once.
- Enable the Google Sheets API for that project.
- Open the target spreadsheet, click Share, and add the service account's email as Editor.
According to the same documentation, service account keys must be stored securely and should be rotated regularly; some organizations block key creation with an organization policy, in which case a Google Workspace admin needs to allow it for this project. Share only the spreadsheet that needs writing with the service account, never a whole folder.
Organizing the file so formulas don't break
- Don't edit the receiving tab by hand. With overwrite, every edit on that tab is lost at the next send.
- Work on a different tab. The accountant's report tab references the receiving tab by column name, not by fixed cell coordinates.
- Look up by column header, so adding a metric doesn't shift your formulas:
=INDEX(weekly!A:Z, MATCH("Shopee", weekly!A:A, 0), MATCH("Net revenue", weekly!3:3, 0))- With append, filter by update time instead of taking "the last row":
QUERYorFILTERon the timestamp column. - Keep definitions on a separate tab: what net revenue means, which fees belong to which group. A newcomer can read it and understand.
How DataxLand does it
Google Sheets is a destination under Settings → Notifications → Notification destinations: enter a Google Sheets destination name, paste the Spreadsheet link or ID and the Service account key (JSON) (DataxLand shows the email you need to share the spreadsheet with), choose a Write mode of Overwrite or Append rows, and click Connect Google Sheets. Then, under Reports and alerts → Scheduled reports, create a report with a Data period (for example last week), Content set to a dashboard page or Choose metrics, Send to the Google Sheets destination, and a weekly Frequency on the day and time you choose. Each table of numbers in the report is written to a tab with the same name, created if it doesn't exist yet. See Reports and alerts.
Numbers sent to Sheets go through the same metric layer as dashboards and Si, so "net revenue" in the accountant's file is exactly the number the owner sees on the dashboard, and it contains no personal data about buyers. The Google Sheets destination is available from the Small Business plan. To read a spreadsheet back into DataxLand (cost of goods, targets), use the Google Sheets connector. Compare the spreadsheet approach with DataxLand on Excel and Google Sheets.
Frequently asked questions
Does the accountant need a DataxLand account?
Not necessarily. The accountant only needs access to the Google Sheets spreadsheet. If they want to ask questions or view dashboards themselves, invite them to the workspace with a suitable role.
Does overwrite delete formulas on other tabs?
No. Overwrite only clears and rewrites the receiving tab. Formulas on other tabs that reference it remain; they just need to look up by column header rather than fixed coordinates.
Can I send daily?
Yes, scheduled reports run daily, weekly or monthly. For accounting, weekly and monthly are usually enough; daily suits operational tracking sheets better.
What if the JSON key file leaks?
Disable and delete that key in the Google Cloud console, create a new key and reconnect the Google Sheets destination. Unshare the spreadsheet from the service account if you no longer use it.
Read next: Automated reports via Telegram · Alerts and scheduled reports · Estimated fees and finalized fees