Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
/* 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);
}
}
@ghost

This comment has been minimized.

Copy link

@ghost ghost commented May 8, 2018

Hello! Is it possibile to use AdWordsApp.report and add some other custom columns/data inside also?

@siliconvallaeys

This comment has been minimized.

Copy link
Owner Author

@siliconvallaeys siliconvallaeys commented Aug 10, 2018

Hi, the script already uses AdWordsApp.report so you can update the AWQL to include any metrics, attributes, and segments you'd like.

@acgorecki

This comment has been minimized.

Copy link

@acgorecki acgorecki commented Mar 13, 2020

I see a lot of potential in this script. Is it possible to update it to the latest API version and deploy it at the MCC level? What changes would need to be made?

@siliconvallaeys

This comment has been minimized.

Copy link
Owner Author

@siliconvallaeys siliconvallaeys commented Mar 13, 2020

The script is currently on the most recent AdWords API version (v201809)

Making it work at the MCC level should be straightforward. More or less this:

`
function main() {
var accountSelector = AdsManagerApp.accounts()
.withLimit(50)
.withCondition("Impressions > 100")
.forDateRange("LAST_MONTH")
.orderBy("Clicks DESC");

accountSelector.executeInParallel("generateReports");
}

function generateReports() {
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);
}
}
`

@acgorecki

This comment has been minimized.

Copy link

@acgorecki acgorecki commented Mar 15, 2020

The script is currently on the most recent AdWords API version (v201809)

I guess I was referring to the new Google Ads API but I suppose you're probably waiting until it is out of beta.

Anyways, thank you for the quick response and for the help! Keep up the great work!

@siliconvallaeys

This comment has been minimized.

Copy link
Owner Author

@siliconvallaeys siliconvallaeys commented Mar 15, 2020

Good point! Once the new API is out of beta and v201809 is shut down I'll be happy to adjust this script. Of course all the ones we host on Optmyzr.com will automatically get updated for our customers. Thanks for following my work!

@acgorecki

This comment has been minimized.

Copy link

@acgorecki acgorecki commented Mar 16, 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?

@monkevlar

This comment has been minimized.

Copy link

@monkevlar 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

This comment has been minimized.

Copy link

@harperplants 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
You can’t perform that action at this time.