Pushing All Cost/account spend into Google sheets

[Chris]

I am trying to push the total account spend for a client a push it into a google sheet on a monthly basis. The script is added to the clients account directly and not through the MCC. 

I tried using just the AdsApp.campaigns and it wouldn't get the cost for campaigns like Pmax, Demand Gen, local service ads. I also tried using AdsApp.currentAccounts and AdsApp.accounts but those just give me and error saying they are not a function. 

Using the code below I can push Pmax cost and search campaigns cost, but still missing how to pull in the other campaign types. Wondering what I am doing wrong to have all costs pushed. 

 var pmaxvar = AdsApp.performanceMaxCampaigns()

      .withCondition("Clicks > 1")

      .forDateRange("20240101", "20241231")

      .get();

 

  var camvar = AdsApp.campaigns()

      .withCondition("Clicks > 1")

      .forDateRange("20240101", "20241231")

      .get();  

 

  while (pmaxvar.hasNext()) {

    var cost = pmaxvar.next();

    sheetarray.push([

        cost.getName(),

        cost.getStatsFor("20241201", "20241231").getCost(),

        cost.getStatsFor("20241101", "20241130").getCost(), 

        cost.getStatsFor("20241001", "20241031").getCost(),  

        cost.getStatsFor("20240901", "20240930").getCost(),

        cost.getStatsFor("20240801", "20240831").getCost(),

        cost.getStatsFor("20240701", "20240731").getCost(),

        cost.getStatsFor("20240601", "20240630").getCost(),

        cost.getStatsFor("20240501", "20240531").getCost(),      

        cost.getStatsFor("20240401", "20240430").getCost(),      

        cost.getStatsFor("20240301", "20240331").getCost(),      

        cost.getStatsFor("20240201", "20240229").getCost(),      

        cost.getStatsFor("20240101", "20240131").getCost()

      ]);

  }

  while (camvar.hasNext()) {

    var cost = camvar.next();

    sheetarray.push([

        cost.getName(),

        cost.getStatsFor("20241201", "20241231").getCost(),

        cost.getStatsFor("20241101", "20241130").getCost(), 

        cost.getStatsFor("20241001", "20241031").getCost(),  

        cost.getStatsFor("20240901", "20240930").getCost(),

        cost.getStatsFor("20240801", "20240831").getCost(),

        cost.getStatsFor("20240701", "20240731").getCost(),

        cost.getStatsFor("20240601", "20240630").getCost(),

        cost.getStatsFor("20240501", "20240531").getCost(),      

        cost.getStatsFor("20240401", "20240430").getCost(),      

        cost.getStatsFor("20240301", "20240331").getCost(),      

        cost.getStatsFor("20240201", "20240229").getCost(),      

        cost.getStatsFor("20240101", "20240131").getCost()

      ]);

  } 

[Google Ads Scripts Forum]

Hi,

Thank you for reaching out to the Google Ads Scripts support team.

In order to investigate further on your issue, kindly provide us with the below details.

• Google Ads account ID/CID
• Name of the affected script
• Shareable spreadsheet link if you are using in your script

You can share the requested details via Reply privately to the author option or a direct private reply to this email.

Thanks,
Google Ads scripts team

[Sigurd Fabrin]

You can't get all cost data using those methods. Use API reports instead. See e.g. https://developers.google.com/google-ads/api/fields/v17/campaign_query_builder

Try smith like this code to get you started

const settings = {
      url: 'https://docs.google.com/spreadsheets/etc', // Replace with spreadsheet URL
      sheet: 'Sheet1', // name of sheet
      lookBackDays: 90 // Number of days to fetch data for (excluding today i.e. until yesterday)
}
    

function main() {
    const dates = getDates(settings.lookBackDays);
    const query =
      `SELECT
        campaign.name,
        campaign.status,
        campaign.advertising_channel_type,
        campaign.advertising_channel_sub_type,
        metrics.cost_micros
      FROM
        campaign
      WHERE
        segments.date BETWEEN "${dates.startDate}" AND "${dates.endDate}"
        AND metrics.cost_micros > 0`;
    const response = AdsApp.search(query);
    const data = [];
    while (response.hasNext()) {
      const row = response.next();
      data.push([
        row.campaign.name,
        row.campaign.status,
        row.campaign.advertisingChannelType,
        row.campaign.advertisingSubChannelType,
        row.metrics.costMicros/1000000 // convert to account currency
      ]);
    }
    data.unshift(['Campaign name','Campaign status','Campaign type','Campaign sub type','Cost']); // prepend headlines
    const sheet = SpreadsheetApp.openByUrl(settings.url).getSheetByName(settings.sheet);
    sheet.clearContents(); // delete any old data
    sheet.getRange(1,1,data.length,data[0].length).setValues(data);
    console.log(`Spreadsheet: ${settings.url} updated with data for ${dates.startDate} - ${dates.endDate}`);
}
function getDates(days) {
  const format = 'yyyy-MM-dd';
  const timeZone = AdsApp.currentAccount().getTimeZone();
  const today = new Date();
  const oneDay = 1000*60*60*24;
  return {
    startDate:Utilities.formatDate(new Date(today-(oneDay*days)),timeZone,format),
    endDate:Utilities.formatDate(new Date(today-oneDay),timeZone,format)
  }
}

Sigurd