Google Sheets Automation with AI: Clean, Sort, Format and Chart Marketing Data

1. Read the spreadsheet before making changes

Start with the spreadsheet ID, tab name, header row and data range. Ask the assistant to inspect the values and any existing formulas before cleaning the file.

Keep the original export in a Raw tab and prepare the report in a Clean tab. The original gives you a reference if a campaign label, date or value changes during preparation.

For this example, use the following columns:

Read numeric fields as unformatted values so display symbols do not become part of the calculation. Inspect formula cells separately before overwriting an existing report.

Read Raw!A1:I121 in the selected spreadsheet. Identify the headers, row count, date range, currencies and values stored as text. Inspect any existing formulas in the destination area. Keep Raw unchanged and prepare a cleaning plan for Clean. Flag values whose meaning is unclear.

2. Clean data using explicit rules

SourcetoMCP's cleaning operation supports trimming whitespace and removing duplicate rows. Apply those actions to a bounded data range, excluding the header.

Define an exact duplicate as a repeated row across all imported fields. Selecting only a campaign-name column for duplicate removal can remove legitimate records from different dates. Google's duplicate-removal request compares the selected columns when identifying repeated rows. Google Sheets request reference.

If two rows share the same account, date and campaign but have different metrics, decide which version to retain before rebuilding the complete table. A revised export and an older export may describe the same reporting record with different conversion values.

Number formatting does not turn every text value into a valid number. A value such as 1,250 can mean different things under different spreadsheet locales. Resolve the locale and currency before converting it, and report anything ambiguous.

Prepare Clean from the imported fields in Raw. Trim whitespace in the data area and remove only duplicate rows that match across all nine imported columns. Report the row count before and after cleaning. Flag conflicting records with the same date, account, currency and campaign. Preserve the original campaign names in Raw.

3. Sort complete rows

Sort the full report range so campaign names, costs and conversions stay together. If the table spans columns A to L, use all twelve columns in the sorting range and start below the header.

Start with spend descending. After calculating CPA in the next step, use CPA descending as a secondary sort. Check zero-conversion rows separately because their CPA is undefined.

Sort the populated data rows in Clean!A2:L121 by cost descending. After the CPA formulas have been added, apply CPA descending as a secondary sort. Keep every field in each row together and leave the header in place. Identify campaigns with spend and zero conversions in a separate review summary.

4. Calculate and format marketing KPIs

With the column structure above, enter these formulas for the first data row and repeat them through the populated rows:

J2, CTR:  =IF(E2=0,"",F2/E2)
K2, CPA:  =IF(H2=0,"",G2/H2)
L2, ROAS: =IF(G2=0,"",I2/G2)

Format CTR as a percentage, CPA and cost in the account's currency, and ROAS as a number. A stored CTR of 0.025 should display as 2.5%.

Keep accounts and currencies separate when calculating financial totals. Adding AUD and USD directly produces a total without a valid currency basis.

Here is a hypothetical example using one currency and one reporting period:

The totals use the underlying counts and amounts. Total CTR is 600 / 24,000. Total CPA is $1,000 / 26. Total ROAS is $4,400 / $1,000.

An unweighted average of the two campaign CTRs would be 3.5%, which misstates the combined result. Ask the assistant to show the inputs behind every summary ratio.

Add CTR, CPA and ROAS formulas to the populated rows only. Create totals by account and currency using summed clicks, impressions, cost, conversions and conversion value. Calculate summary ratios from those totals. Format the header, freeze row 1, add a filter and apply percentage, currency and number formats to the relevant columns.

5. Create charts that answer a specific question

Use a column chart for spend by campaign, a bar chart for CPA by campaign or a line chart for daily spend. Each chart should use a prepared summary range with matching labels and numeric values.

SourcetoMCP currently creates charts with one data series. Use separate spend and conversion charts for this workflow. Include the date range and currency in their titles.

A CPA chart also needs a clear rule for campaigns with zero conversions. Leave their CPA blank and include those campaigns in the review summary so they remain visible to the team.

From the account-and-currency summary, create a column chart of spend by campaign and a separate bar chart of CPA. Use matching label and value ranges. Include the reporting period and currency in the titles. Record the chart IDs so the existing charts can be updated on the next run.

6. Rerun the workflow and check the result

Reuse the same Raw, Clean and Summary tabs for the next reporting period. Refresh only the agreed data area and remove leftover rows if the new export is shorter. Update existing chart ranges rather than creating another copy of every chart.

After the refresh, check the imported row count, removed duplicates, date coverage and financial totals against the source. Include the refresh time and unresolved data issues in the report.

Does Google Sheets automation with AI require Apps Script?

These connected operations can run through SourcetoMCP without writing an Apps Script for each cleaning, sorting or formatting task. A workflow that must run unattended on a schedule still needs a separately configured trigger or runner.

To build the reporting layout, use the Google Sheets marketing dashboard guide. To work on your connected file, start with Google Sheets for Claude or Google Sheets for ChatGPT.