Skip to content

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.

MetricSplit byThe accountant uses it to
Net revenueChannelReconcile recorded revenue
Cost of goodsChannelReconcile stock issued
Marketplace fees, payment feesChannelReconcile settlements and payouts to the wallet
Shipping paid by the shop, return feesChannelRecord selling expenses
Ad spendChannelReconcile ad invoices
Estimated-fee shareWhole shopKnow which part may still change
Orders, cancellation rateChannelExplain 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.

OverwriteAppend rows
Each sendClears the old tab, writes the new tableAdds new rows at the bottom of the tab
Top rowAn "updated at" timestamp, then the header row and the numbersThe first column is each row's update time
Fits"This week's numbers" for a quick look, reference tablesWeek-by-week history for trends and back-reconciliation
RiskManual edits on the tab are lost at the next sendThe 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):

  1. In the Google Cloud console, open the Service accounts page and select a project (a dedicated project for this is tidiest).
  2. Create a new service account, then click its email address.
  3. 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.
  4. Enable the Google Sheets API for that project.
  5. 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

  1. Don't edit the receiving tab by hand. With overwrite, every edit on that tab is lost at the next send.
  2. Work on a different tab. The accountant's report tab references the receiving tab by column name, not by fixed cell coordinates.
  3. 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))
  1. With append, filter by update time instead of taking "the last row": QUERY or FILTER on the timestamp column.
  2. 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

Related posts

Verified dashboards from your own data, this afternoon

Free plan with no time limit · No card required · Sample workspace ready in minutes