Microsoft Ads Script to Upload Data About Your Campaigns to Google Sheets

If you want to add data to a Google Sheet from Microsoft Ads, this may be a challenging task because they are in different ecosystems. And the main challenge here isn't the code itself; it's about security. Here's the code you can use to upload Basic Metrics to a Google Sheet from Microsoft Ads, but only if you're the only person who has access to your account. In case of agencies, this approach isn't working because you're sharing your tokens with your clients, meaning they have access to your Drive!

You need to replace XX and YY with credentials and your Google Sheet ID (this doc should include the 'CAMP-BM' sheet). How to receive credentials, you can read here:


function main() {
  var payload = { "channel": "bing", "reports": [] };

  // -------------------------
  // Collect daily stats since 2025-08-01 until today
  // -------------------------
  var today = new Date();
  var startDate = new Date("2025-08-01T00:00:00Z"); // Fixed start date

  // Iterate over each day in the range
  for (var d = new Date(startDate); d <= today; d.setDate(d.getDate() + 1)) {
    var formattedDate = formatDate(d);              // YYYYMMDD
    var readableDate = formatReadableDate(formattedDate); // YYYY-MM-DD

    // Get all campaigns with >0 impressions that day
    var rows = AdsApp.campaigns()
      .forDateRange(formattedDate, formattedDate)
      .withCondition("Impressions > 0")
      .get();

    while (rows.hasNext()) {
      var campaign = rows.next();
      var stats = campaign.getStats();

      // Row schema matches Google Ads export
      var row = [
        campaign.getName(),                     // campaign.name
        readableDate,                           // segments.date
        stats.getImpressions(),                 // metrics.impressions
        stats.getClicks(),                      // metrics.clicks
        stats.getCost() * 1000000,              // metrics.cost_micros
        stats.getTopImpressionRate(),           // metrics.top_impression_percentage
        stats.getConversions(),                 // metrics.conversions
        stats.getRevenue(),                     // metrics.revenue
      ];

      payload["reports"].push(row);
    }
  }

  // -------------------------
  // Clear old data and push new rows
  // -------------------------
  clearExistingData("CAMP-BM");
  // Add header row first
  pushToSpreadsheet([[
    "campaign.name",
    "segments.date",
    "metrics.impressions",
    "metrics.clicks",
    "metrics.cost_micros",
    "metrics.top_impression_percentage",
    "metrics.conversions",
    "metrics.revenue"
  ]], 1, "CAMP-BM");
  // Add all collected rows
  pushToSpreadsheet(payload["reports"], 2, "CAMP-BM");
}


function clearExistingData(sheetName) {
  const credentials = {
    accessToken: '',
    clientId: 'XX',
    clientSecret: 'XX',
    refreshToken: 'XX'
  };
  var spreadsheetId = 'YY'; // Your Spreadsheet should have the 'CAMP-BM' sheet
  var sheetsApi = GoogleApis.createSheetsService(credentials);
  sheetsApi.spreadsheets.values.clear({
    spreadsheetId: spreadsheetId,
    range: `${sheetName}!A1:Z`
  });
}

function pushToSpreadsheet(data, startRow, sheetName) {
  const credentials = {
    accessToken: '',
    clientId: 'XX',
    clientSecret: 'XX',
    refreshToken: 'XX'
  };
  var spreadsheetId = 'YY';
  var sheetsApi = GoogleApis.createSheetsService(credentials);
  sheetsApi.spreadsheets.values.append(
    {
      spreadsheetId: spreadsheetId,
      valueInputOption: 'USER_ENTERED',
      insertDataOption: 'INSERT_ROWS',
      range: `${sheetName}!A${startRow}:Z`
    },
    { range: `${sheetName}!A${startRow}:Z`, values: data }
  );
  Logger.log(`Added ${data.length} rows starting from row ${startRow} in sheet "${sheetName}".`);
}