Skip to content

Instantly share code, notes, and snippets.

@siliconvallaeys
Last active July 9, 2022 16:52
Show Gist options
  • Star 8 You must be signed in to star a gist
  • Fork 4 You must be signed in to fork a gist
  • Save siliconvallaeys/4a198d30f080f76afe4bc0eff24e0797 to your computer and use it in GitHub Desktop.
Save siliconvallaeys/4a198d30f080f76afe4bc0eff24e0797 to your computer and use it in GitHub Desktop.
/* Put Google Ads Data in Google Spreadsheet
* -----------------------------------------
*
* Script by Optmyzr.com
*
* v3 (20190702)
* - updated to API v201809
* v2 (20180810)
* - updated to newer reporting version in ads API
*
*/
// Instructions:
// You can edit the following settings
// - query: this is the AWQL query that tells AdWords what data to include. We’ve added a few sample queries in the code or you can write your own just like your write SQL.
// - spreadsheetUrl: the Url of the Google spreadsheet that this script will update.
// - tabName: the name of the sheet (tab) in the spreadsheet that should be updated.
// - reportVersion: the version of the AdWords API reports you’re using. The data available in the Ads API changes periodically so this ensures our script talks to the right version of AdWords.
var QUERIES = [{'query' : 'SELECT AdGroupId, Id, Conversions, ConversionTypeName ' +
'FROM KEYWORDS_PERFORMANCE_REPORT ' +
'DURING LAST_30_DAYS',
'spreadsheetUrl' : 'https://docs.google.com/spreadsheets/d/1dTabZAFRsguG6WQP0VofAynEcsPOtYdiktz5VSB77pw/edit#gid=0',
'tabName' : 'Conversion Types',
'reportVersion' : 'v201809'
},
{'query' : 'SELECT CampaignName, Clicks, Impressions, Cost ' +
'FROM CAMPAIGN_PERFORMANCE_REPORT ' +
'WHERE Impressions > 10 ' +
'DURING LAST_30_DAYS',
'spreadsheetUrl' : 'https://docs.google.com/spreadsheets/d/1dTabZAFRsguG6WQP0VofAynEcsPOtYdiktz5VSB77pw/edit#gid=0',
'tabName' : 'Campaigns',
'reportVersion' : 'v201809'
}
];
function main() {
for(var i in QUERIES) {
var queryObject = QUERIES[i];
var query = queryObject.query;
var spreadsheetUrl = queryObject.spreadsheetUrl;
var tabName = queryObject.tabName;
var reportVersion = queryObject.reportVersion;
//Logger.log(spreadsheetUrl + " " + query);
var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
var sheet = spreadsheet.getSheetByName(tabName);
var report = AdWordsApp.report(query, {apiVersion: reportVersion});
report.exportToSheet(sheet);
}
}
@monkevlar
Copy link

monkevlar commented May 5, 2020

One more question for you. I was able to get the MCC selector working, but the script is iterating over each account and erasing the previous accounts data. Does it have to do with the report.exportToSheet(sheet); part? How would I get each iteration to not overwrite the previous data?

+1, encountered the same issue. Maybe an extra function for checking if rows are already populated before exporting new data would come in handy. I think that this chunk of code can help:

function writeToSpreadsheet(rows) { var access = new SpreadsheetAccess(SPREADSHEET_URL, 'Report'); var emptyRow = access.findEmptyRow(6, 2); if (emptyRow < 0) { access.addRows(rows.length); emptyRow = access.findEmptyRow(6, 2); } access.writeRows(rows, emptyRow, 2); sortReportRows(); }

Source: https://developers.google.com/google-ads/scripts/docs/solutions/adsmanagerapp-account-summary

@harperplants
Copy link

harperplants commented Jun 13, 2020

Like acgorecki, I'm seeing the script overwrite rows. In the Google Ads Scripts Forum, I see a similar thread- https://groups.google.com/forum/#!topic/adwords-scripts/TQP_dNjLUZA --"Your use case which is to output the reports for all child accounts under an MCC into one spreadsheet is possible. ... In the MCC level, you cannot use the exportToSheet() method of the report since the goal is just to append the report data into a sheet for all child accounts. If you use exportToSheet(), it will always overwrite the data of the sheet. That said, you need to manually add the report data as a row in the sheet per account using appendRow() function. It is recommended that we execute the child accounts in series, not in parallel, as running in parallel would result to overlapping results in the sheet." Here is an MCC script that is not overwriting rows.

function main() {
var accountIterator = MccApp.accounts().withCondition("Clicks > 1 during THIS_MONTH").get();
var spreadsheet = SpreadsheetApp.openByUrl("PASTE_YOUR_SPREADSHEET_URL_HERE");
var mccAccount = AdWordsApp.currentAccount();
var sheet = spreadsheet.getActiveSheet();
sheet.appendRow(['Query', 'CostPerConversion','AccountDescriptiveName','CampaignName','AdGroupName', 'Conversions', 'Cost', 'Clicks']);
while (accountIterator.hasNext()) {

    var account = accountIterator.next();
    // Switch to the account you want to process.

 MccApp.select(account);
    //Query
    var report = AdWordsApp.report(
      "SELECT Query, CostPerConversion, AccountDescriptiveName, CampaignName, AdGroupName, Conversions, Cost, Clicks " +
      "FROM SEARCH_QUERY_PERFORMANCE_REPORT " +
      "WHERE Clicks > 1 " +
      "DURING LAST_7_DAYS");

  var rows = report.rows();
    while (rows.hasNext()) {
        var row = rows.next();            
        sheet.appendRow([row['Query'], row['CostPerConversion'], row['AccountDescriptiveName'], row['CampaignName'], row['AdGroupName'], row['Conversions'], row['Cost'], row['Clicks']]);
    }
}

}

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment