1. Prep Your Spreadsheet:
- Open your Google Sheet and create two tabs: "Spend Settings" and "Spend".
- In "Spend Settings", populate the following cells:
- A2: Your Ad Account ID (no
act_
prefix needed; we’ll handle it). - B2: Your access token (straight from Meta, no fluff).
- C2/D2: Optional start/end dates (
YYYY-MM-DD
). - E2: Breakdown level (e.g.,
campaign
). - F2: Breakdowns (e.g.,
age,gender
). - G2: Data fields (e.g.,
spend,clicks,impressions
).
- A2: Your Ad Account ID (no
2. Copy-Paste the Script:
- Go to Extensions > Apps Script and drop the script in there like it’s hot.
- Save it.
- Run the script and authorize it to access your Google Sheets and Meta data.
function fetchMetaAdSpendInsights() {
// 1. Get references to your spreadsheet and sheets
const ss = SpreadsheetApp.getActiveSpreadsheet();
const settingsSheet = ss.getSheetByName("Spend Settings");
const dataSheet = ss.getSheetByName("Spend");
// 2. Read values from the Settings sheet
const adAccountIdRaw = settingsSheet.getRange("A2").getValue().toString().trim();
const accessToken = settingsSheet.getRange("B2").getValue().toString().trim();
// If you want a custom date range (C2, D2):
const startDateCell = settingsSheet.getRange("C2").getValue(); // e.g., 2025-01-01
const endDateCell = settingsSheet.getRange("D2").getValue(); // e.g., 2025-01-31
if (!isNaN(startDateCell.getTime()) && !isNaN(endDateCell.getTime())) { // Ensure valid dates
startDateCell.setDate(startDateCell.getDate() + 1); // Add 1 day to start date
endDateCell.setDate(endDateCell.getDate() + 1); // Add 1 day to end date
} else {
throw new Error("Invalid date format in C2 or D2"); // Stop execution if dates are invalid
}
// Level & Breakdowns from dropdown
const level = settingsSheet.getRange("E2").getValue().toString().trim(); // e.g. "campaign"
const breakdowns = settingsSheet.getRange("F2").getValue().toString().trim(); // e.g. "age,gender"
// Fields
// e.g., "spend,clicks,impressions", or "spend,clicks,impressions,actions,cpc"
const fields = settingsSheet.getRange("G2").getValue().toString().trim();
// Ensure the Ad Account ID has "act_" prefix if not included
let finalAdAccountId = adAccountIdRaw;
if (!finalAdAccountId.startsWith("act_")) {
finalAdAccountId = "act_" + finalAdAccountId;
}
// 3. Convert the Start/End dates to "YYYY-MM-DD" format if they are actual date objects in the sheet
const startDateObj = new Date(startDateCell);
const endDateObj = new Date(endDateCell);
const startDateStr = Utilities.formatDate(startDateObj, "UTC", "yyyy-MM-dd");
const endDateStr = Utilities.formatDate(endDateObj, "UTC", "yyyy-MM-dd");
// 4. Build the URL
// We'll use the Insights endpoint with a custom time range, plus the 'level' param:
let baseUrl = "https://graph.facebook.com/v16.0/";
let insightsEndpoint = `${finalAdAccountId}/insights`;
// Required params:
// - fields (comma-separated)
// - time_range (JSON with "since" and "until")
// - access_token
// - level (campaign, adset, ad) or default is account level
// time_range must be URL-encoded
const timeRangeParam = encodeURIComponent(
JSON.stringify({
since: startDateStr,
until: endDateStr
})
);
// Construct the base query
let queryParams = [
`fields=${fields}`,
`time_range=${timeRangeParam}`,
`access_token=${accessToken}`,
`level=${level}`, // add level
`limit=500` // Maximize the number of records per request
];
// If the user selected something other than "none" for breakdowns
if (breakdowns && breakdowns.toLowerCase() !== "none") {
// e.g. breakdowns=age,gender
queryParams.push(`breakdowns=${breakdowns}`);
}
// Join them with & to form the final URL
let url = `${baseUrl}${insightsEndpoint}?${queryParams.join("&")}`;
// 5. Fetch data from the API
let response;
try {
response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
} catch (error) {
Logger.log("Fetch error: " + error);
return;
}
// Parse the JSON
let rawText = response.getContentText();
let json = JSON.parse(rawText);
if (json.error) {
Logger.log("API Error: " + json.error.message);
return;
}
// 6. Extract insights data
let insights = json.data || [];
// 7. Write the data to the "Data" sheet
dataSheet.clearContents();
// For simplicity, let's write basic columns
// We'll try to map whatever fields were requested
// but for that, we need to parse each field from the results.
// We'll put a basic header row
// If you used dynamic fields, you might parse them differently.
// For demonstration, let's just do date_start, date_stop, spend, clicks, impressions,
// plus breakdown columns if they exist.
dataSheet.getRange(1, 1).setValue("date_start");
dataSheet.getRange(1, 2).setValue("date_stop");
// Let’s store an array of columns we want
// (If the user typed "spend,clicks,impressions" in G2, let's split that.)
const fieldsArr = fields.split(",").map(f => f.trim());
// We'll put them in columns 3, 4, 5, etc.
let colIndex = 3;
for (let f of fieldsArr) {
dataSheet.getRange(1, colIndex).setValue(f); // e.g. "spend"
colIndex++;
}
// If breakdowns exist, we can also write them after that
let breakdownArr = [];
if (breakdowns && breakdowns.toLowerCase() !== "none") {
breakdownArr = breakdowns.split(",").map(b => b.trim());
for (let b of breakdownArr) {
dataSheet.getRange(1, colIndex).setValue(b); // e.g. "age"
colIndex++;
}
}
// Now let's fill the sheet row by row:
for (let i = 0; i < insights.length; i++) {
let row = i + 2;
let item = insights[i];
// Write date_start, date_stop
dataSheet.getRange(row, 1).setValue(item.date_start || "");
dataSheet.getRange(row, 2).setValue(item.date_stop || "");
// For each requested field
colIndex = 3;
for (let f of fieldsArr) {
dataSheet.getRange(row, colIndex).setValue(item[f] || "");
colIndex++;
}
// For each breakdown
for (let b of breakdownArr) {
// Typically, breakdown values end up in the item itself, e.g. item.age or item.gender
dataSheet.getRange(row, colIndex).setValue(item[b] || "");
colIndex++;
}
}
Logger.log(`Success: fetched ${insights.length} rows from Facebook Insights.`);
}
3. Run the Script, Get the Goods:
- Hit Run in the Apps Script editor.
- If all goes well, the "Spend" sheet will get a makeover with your Meta data – fields as headers, insights as rows.
- If all does not go well, check the Logs for any existential crises (or API errors).
📦 Moral of the Story:
Conjuring Meta ad spend data into a Google Sheet is like hacking the matrix – except instead of Neo, you’re just really good at spreadsheets.