Weekly Metricool social post archive in Google Sheets

Every Monday at 6am, export last week's social post performance from Metricool into a Google Sheet so my whole team works off one shared history. Build this as a code workflow on a cron trigger. Every step is fixed, so there should be no AI summarization anywhere in it: the point is a clean, append-only archive I can pivot and chart myself instead of re-exporting by hand.

Start by listing my brands with Metricool's Get Admin Simple Profiles. Metricool is brand-scoped, so use this to resolve the numeric blogId for each brand before pulling any stats, and carry the brand name through to the output rows.

Work out the date window once per run: the previous seven days, meaning the Monday to Sunday week that has just ended. Apply that same window to every brand and every network so the archive never overlaps itself or leaves a gap between runs.

For each brand, pull that window from all five sources: Get Stats Instagram Posts, Get Stats Instagram Reels, Get Stats Instagram Stories, Get Stats Facebook Posts and Get Stats Fbgroup Posts. Not every brand has every network connected. If a network returns no access or no data for a brand, skip it and carry on with the rest rather than failing the whole run.

Flatten every post from every network into one row with a fixed column order: brand name, network, publish date, post link, post type, impressions or reach, engagement, engagement rate. Use whichever of impressions or reach that network reports for that post type so the column is always populated, and where Metricool does not return an engagement rate directly, calculate it as engagement divided by impressions or reach.

Append the rows to my tracking spreadsheet with Google Sheets Append Values, one row per post, written after the last existing row. Never overwrite or rewrite rows that are already there. Each week's run just adds to the bottom, so the sheet builds into a rolling archive.