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).

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.