[Google ADS]Obtain operational reports from all MCC accounts
This will be the way to get the report from your MCC account.
AdsApp.report can be generated using any of the query
目次
Key Advantages of Using AdsApp.report
- Access to a variety of statistical information not available to regular Google advertising entities (including segments).
- Ability to access large amounts of data. Currently, reports are not subject to assignment, but can retrieve 1,000,000 rows of reports.
- Performance – Downloading reports can take a long time, but iterating through report rows is very fast.
What to do when you cannot do AdsApp.report() with MCC account
When using AdsApp.report() with MCC accounts, only the data for the first account could be spread out. The cause remains a mystery, but it was improved by changing report.exportToSheet to a different function.
Script
Fill in the code in the script for your GoogleAds MCC account.
Obtain the MCC account list and set the AdsApp account.
var accountIterator = AdsManagerApp.accounts().get();
account = accountIterator.next();
AdsManagerApp.select(account);Retrieving Reports
report = AdsApp.report(
"SELECT segments.date, segments.hour, campaign.name,metrics.impressions,metrics.cost_micros,metrics.clicks,metrics.all_conversions " +
"FROM campaign " +
"WHERE segments.date BETWEEN '2021-12-01' AND '2021-12-31'");
The above example is a query that retrieves hourly data for each campaign.
For more information on queries,click here.
Output to spreadsheet
var spreadsheet = SpreadsheetApp.openById("1slZ********")
var temp_sheet = spreadsheet.getSheetByName('temp');
const db_sheet = spreadsheet.getSheetByName('data');
report.exportToSheet(temp_sheet);
if (temp_sheet.getLastRow() > 1) {
const values = temp_sheet.getRange(2, 1, temp_sheet.getLastRow() - 1, temp_sheet.getLastColumn()).getValues();
const ranges = db_sheet.getRange(db_sheet.getLastRow() + 1, 1, values.length, values[0].length);
ranges.setValues(values);
}report.exportToSheet() overwrites the data on the sheet, so we have the data output once on the “temp" sheet copied to the sheet for storage.
Entire Script
function main() {
var accountIterator = AdsManagerApp.accounts().get();
Logger.log('アカウント件数 : ' + accountIterator.totalNumEntities());
while (accountIterator.hasNext()) {
account = accountIterator.next();
AdsManagerApp.select(account);
report = AdsApp.report(
"SELECT segments.date, segments.hour, campaign.name,metrics.impressions,metrics.cost_micros,metrics.clicks,metrics.all_conversions " +
"FROM campaign " +
"WHERE segments.date BETWEEN '2021-12-27' AND '2021-12-27'");
set_record(report)
}
}
function set_record(report) {
var spreadsheet = SpreadsheetApp.openById("1slZ********")
var temp_sheet = spreadsheet.getSheetByName('temp');
const db_sheet = spreadsheet.getSheetByName('data');
report.exportToSheet(temp_sheet);
if (temp_sheet.getLastRow() > 1) {
const values = temp_sheet.getRange(2, 1, temp_sheet.getLastRow() - 1, temp_sheet.getLastColumn()).getValues();
const ranges = db_sheet.getRange(db_sheet.getLastRow() + 1, 1, values.length, values[0].length);
ranges.setValues(values);
}
}