Automation · Operations and reporting
Create a weekly Google Ads report in Google Sheets
Send each closed Google Ads week to Sheets with cost, clicks, impressions, and reported conversions, without mixing accounts or retrying duplicates blindly.
How do I send a weekly Google Ads report to Google Sheets without duplicate rows?
The Met works with one account and one calendar week that has ended in that account's timezone. It runs GAQL with explicit ISO dates and reads currency, timezone, cost in micros, impressions, clicks, and the conversions Google Ads already attributed. It does not call those conversions sales or calculate ROI. Before writing, it verifies the file, tab, and headers, then searches for a stable technical key built from the account's opaque alias and the period. It shows the complete row and waits for a new approval. Only then does it read the key again, append one row with INSERT_ROWS, and verify Google's returned range. If the append is ambiguous, it rereads the sheet and never retries blindly.
What starts it
When someone asks for it
It starts when a person provides the account and dates for an already closed week, or runs the saved manual task. It does not install a cron job or promise next week's report will appear on its own. Every period needs a run and approval for the write.
A manual task: someone on the team runs it with a button, or asks the Met for it in chat.
Who runs it
Met for paid-media analytics and reporting
Social team
It has no seeded proper name. Its job is to move one closed, traceable measurement between two systems while preserving units, missing values, and scope, without turning ad metrics into business conclusions the sources do not support.
What you need connected
- Google Ads Create and promote
- Google Workspace Automate and collaborate
5 steps · 5 tools
The procedure, step by step
Each step shows the tool that runs. They belong to the real connector: if one of them stopped existing, this page would not build.
- 1
Choose one account and one closed week
listar_cuentas confirms the accessible IDs. The run selects exactly one and receives week_start and week_end in YYYY-MM-DD, from Monday through Sunday. With several accounts it does not add them together or silently take the first. It also loads a stable opaque account_key from task configuration, such as a UUID with no name, email, or customer ID, to build the report key. It stops without an account, exact period, or configured alias.
- 2
Query the exact measurement from Google Ads
consultar runs GAQL on customer with customer.id, customer.currency_code, customer.time_zone, metrics.cost_micros, metrics.impressions, metrics.clicks, and metrics.conversions, filtered with `segments.date BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'` after substituting the agreed dates. It does not use LAST_7_DAYS because that preset can include an open week. Exactly one account must return, paginacion.hay_mas must be false, and week_end must be fully in the past in customer.time_zone. Extra, missing, or truncated rows stop the flow. Readable cost is cost_micros divided exactly by 1,000,000, while the original value is kept. Currency comes from customer.currency_code and is never converted. metrics.conversions is stored as conversions_reported exactly as received, including a fractional value caused by attribution; it is not renamed sales. If Google omits a field, its cell stays blank and data_notes records the absence instead of replacing it with zero. report_key is built as account_key:week_start:week_end.
- 3
Verify the sheet and find the same period
The spreadsheet_id and Weekly Ads tab are configured in advance. sheets_metadata confirms the file and tab. sheets_get validates this exact order in `'Weekly Ads'!A1:M1`: report_key, account_key, google_ads_customer_id, week_start, week_end, account_timezone, currency_code, cost_micros, cost, impressions, clicks, conversions_reported, and data_notes. It then reads `'Weekly Ads'!A2:M3500` with UNFORMATTED_VALUE and compares report_key. At most 45,487 cells stay under the local 50,000-cell cap. A full range, missing header, or more than one match stops the run rather than assuming the sheet is complete.
- 4
Show the row and wait for another approval
One existing row with the same key and values means the period is already recorded, so it does not write. The same key with different values is a conflict and stops the run; this recipe does not rewrite history with sheets_update. With no match, it shows account, dates, timezone, currency, and all thirteen exact cells. It waits for an explicit yes in a new message. Asking for the report at the start is not approval for the append.
- 5
Read again, append once, and reconcile the result
After approval, sheets_get searches report_key again to close the window between review and write. Only with zero matches does it call sheets_append on `'Weekly Ads'!A:M` with a one-row matrix and INSERT_ROWS. If Google responds, sheets_get reads updatedRange and compares all thirteen cells. After a timeout or ambiguous response, it reads `'Weekly Ads'!A2:M3500` by key: one match with all thirteen exact cells confirms success; zero, one with different values, or several leave the case unresolved and stop the run. It never sends the append again blindly.
Before you start
What you need ready
The Met solves none of these for you. If one is missing, the automation stops there.
-
Google Ads connected and one account selected
Connect the account with Google Ads OAuth permission and configure the developer token. Select one customer ID visible to that credential; process every additional account in a separate run.
-
A stable opaque alias for that account
Store a random account_key, such as a UUID, mapped to the customer ID in task configuration. Do not put a company name, email address, or other personal data in that key.
-
A shared, prepared spreadsheet
Connect Google Workspace through OAuth with Google Sheets access, share the file with that account, and create the Weekly Ads tab with the thirteen headers in the documented order.
-
ISO dates for an already ended week
Provide week_start and week_end from Monday through Sunday in YYYY-MM-DD. The Ads account supplies its timezone. If Sunday has not ended there, the run waits and writes no partial.
The limits
What this automation does NOT solve
Better said here than discovered halfway through the rollout.
-
It does not schedule the next report or create a recurring trigger. This is a manual task for each closed week. Calendar automation is another flow and requires a real trigger.
-
It does not mix accounts or currencies. One row belongs to one customer ID, its currency_code, and one period. Consolidation needs separate rows and conversion rules outside this recipe.
-
It does not call metrics.conversions sales, configure conversion measurement, or calculate ROI. The conversions are those Google Ads already attributed and may be fractional.
-
It does not turn a missing field into zero or invent a reason for changes in cost, clicks, or conversions. It stores a blank with a note and leaves interpretation to a separate review.
-
It does not guarantee atomic uniqueness. sheets_get and sheets_append are separate calls, so two concurrent runs could still pass the pre-read and append the same period.
-
It does not correct or delete existing rows. A matching key with different values is reported as a conflict, and the run stops without calling sheets_update or sheets_clear.
FAQ
Questions about this automation
It is a moving window and can include days from two weeks or a day that is still open. The recipe uses an explicit Monday and Sunday with BETWEEN and checks the account timezone.
No. They are metrics.conversions under the Google Ads setup and attribution model. They may include other actions and fractional values. Reconciling them to sales needs another source.
The account's customer.currency_code. cost_micros keeps the raw value and cost divides it by one million. This recipe neither converts currencies nor adds different accounts together.
It is not retried. The Met first rereads report_key in the configured range. One exact match confirms the write; zero or several leave an ambiguous result for manual review.
Not in one row or one run. Run once per customer ID to preserve currency, timezone, period, and the deduplication key without mixing them.
Keep going
Other automations built on the same tools
Content and publishing
Build a complete Google Ads search campaign
How do I build a complete Google Ads search campaign and review it before it spends?
Social
- Starts:
- when someone asks for it
- Steps:
- 10, with 13 real tools
- Needs:
- Google Ads
Operations and reporting
Build an internal AI dashboard from Google Sheets
How do I build an AI dashboard from data I already keep in Google Sheets?
- Starts:
- when someone asks for it
- Steps:
- 6, with 5 real tools
- Needs:
- Google Workspace · Meteor Pages
Operations and reporting
Save the weekly Mercado Libre order report to Google Sheets
How do I save a verifiable weekly Mercado Libre order report to Google Sheets?
- Starts:
- when someone asks for it
- Steps:
- 4, with 5 real tools
- Needs:
- Mercado Libre · Google Workspace
Operations and reporting
A weekly Search Console report, with the history kept
How do I get the Search Console report every Monday without building it by hand?
- Starts:
- at a set time
- Steps:
- 6, with 12 real tools
- Needs:
- Google Workspace
Want this automation running on your own data?
We will show it to you with your accounts connected, not with a canned demo.