如何用Google Ads Scripts获取30天数据并按天循环处理

Got it, let's adjust your existing code to pull daily data for the last 30 days instead of just yesterday. The key is to generate each date in the 30-day range, fetch reports for that single day, and write each day's results to your sheet.

Step 1: Core Approach #

• Calculate the date range for the last 30 days (from 29 days ago up to yesterday, since YESTERDAY is the most recent full day)
• Loop through each individual day, using Google Ads' DURING [startDate],[endDate] parameter to fetch daily-specific data
• Reuse your existing appendARow function to write each day's data to the sheet

Step 2: Modified Full Code #

function main() {
  const mql = "MQL";
  const daysToFetch = 30; // Number of days to pull data for

  // Loop through each day in the last 30 days
  for (let i = daysToFetch - 1; i >= 0; i--) {
    // Calculate the target date (i days ago from today, minus 1 to exclude today)
    const targetDate = new Date();
    targetDate.setDate(targetDate.getDate() - i - 1);

    // Format date to YYYY-MM-DD (required format for Google Ads reports)
    const formattedDate = Utilities.formatDate(
      targetDate,
      AdsApp.currentAccount().getTimeZone(),
      "yyyy-MM-dd"
    );

    // Fetch daily MQL conversion data
    const conversionReport = AdsApp.report(`
      SELECT Conversions, Date 
      FROM ACCOUNT_PERFORMANCE_REPORT 
      WHERE ConversionTypeName CONTAINS "${mql}" 
      DURING ${formattedDate},${formattedDate}
    `);

    // Fetch daily cost data
    const costReport = AdsApp.report(`
      SELECT Cost, Date 
      FROM ACCOUNT_PERFORMANCE_REPORT 
      DURING ${formattedDate},${formattedDate}
    `);

    // Process cost data (there will always be a row for the day)
    const costRows = costReport.rows();
    const costRow = costRows.next();
    const costJson = JSON.parse(JSON.stringify(costRow));
    const dailyCost = costJson.Cost;

    // Process conversion data (handle days with no MQL conversions)
    const conversionRows = conversionReport.rows();
    let dailyConversions = 0;
    if (conversionRows.hasNext()) {
      const conversionRow = conversionRows.next();
      const conversionJson = JSON.parse(JSON.stringify(conversionRow));
      dailyConversions = conversionJson.Conversions;
    }

    // Write the day's data to your Google Sheet
    appendARow(formattedDate, dailyConversions, dailyCost);
  }
}

function appendARow(date, conversion, cost) {
  const SPREADSHEET_URL = 'YOUR_SPREADSHEET_URL_HERE'; // Replace with your actual sheet URL
  const SHEET_NAME = 'Sheet1';
  const ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  const sheet = ss.getSheetByName(SHEET_NAME);
  sheet.appendRow([date, conversion, cost]);
}

Key Changes Explained #

• Date Generation: We use Utilities.formatDate to match Google Ads' required YYYY-MM-DD format, and calculate each target day by subtracting days from the current date (excluding today since it's not a full day).
• Loop Logic: The loop runs 30 times, each time targeting one specific day in the past 30-day window.
• Daily-Specific Reports: Instead of pulling a bulk 30-day report, we fetch separate conversion and cost reports for each individual day to get accurate daily breakdowns.
• Edge Case Handling: We explicitly account for days with no MQL conversions, defaulting to 0 for conversions while still writing the day's cost data.

Quick Notes #

• Don't forget to replace YOUR_SPREADSHEET_URL_HERE with your actual Google Sheet URL in the appendARow function.
• Google Ads Scripts have generous API call limits, so 30 daily report calls will not hit any quotas.
• If you want to start from a specific date instead of the last 30 days, adjust the targetDate calculation to use your desired start date instead of subtracting from today.

内容的提问来源于stack exchange，提问作者Einstein Villamor