Schedule Google Ads Reports to the same Google Sheets spreadsheet file

[Kevin Montel]

I am trying to achieve a pretty simple task, which unfortunately seems to be actually complex in nature.

I would like to create an automation (any kind of automation) that will send daily Google Ads data to Google Data Studio. 

I can't use the Ads connector in Data Studio because I need data from all accounts (the limit is 50). I thought that scheduling the creation of a Google Sheets spreadsheet through the Google Ads Report tool would be enough, since Data Studio accepts Google Sheets as source as well.

The problem is that the scheduled task produces a new Google Sheets spreadsheet (with the same name) everyday, without overwriting the previous file.

I am currently exploring time consuming workarounds that involve Google Ads scripts that query data via AWQL and feed it directly to targeted Google Sheets, but I'm still far from the solution.

I was wondering if there's a way to "trick" the Google Drive file management with a simple overwrite script, in order to keep only the most recent file. I found this solution o Github, but the "unique ID" of the newly created file will prevent me anyway to tell Data Studio which file to point to.

Thank you for your attention.

Best regards,

Kevin

[Google Ads Scripts Forum Advisor]

Hi Kevin,

While this channel does not support Data Studio, the exportToSheet method can clear all data on a sheet, and write a new report on that spreadsheet. Please see the sample code below:
 

function main() {
  
  var spreadsheetUrl = 'YOUR_SPREADSHEET_URL';
  var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl)
  var sheet = spreadsheet.getActiveSheet();
  
  var report = AdsApp.report(
    "SELECT CampaignName, AdGroupName, KeywordTextMatchingQuery, Query, Clicks, Ctr, Conversions, CostPerConversion " + 
    "FROM CAMPAIGN_PERFORMANCE_REPORT " + 
    "DURING THIS_MONTH "
  )
  
  report.exportToSheet(sheet);
  Logger.log("Your repot can be found here: " + spreadsheet.getUrl());
                 
}

Please let us know if you have any other questions on this.

Regards,
Matt
Google Ads Scripts Team

ref:_00D1U1174p._5004Q1zKDhN:ref

[Kevin Montel]

Hi Matt,

thank you kindly for your answer. I already tried to implement the exportToSheet method with the following script (source): 

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);
}
}

But since I need to run it on MCC level, the sheet gets cleaned after every iteration of the accountSelector.

I'm pretty sure that this is the best solution, but I only have beginner skills in javascript and writing (or fixing) scripts is somewhat hard for me.

Thanks a lot!

[Google Ads Scripts Forum Advisor]

Hi Kevin,

I work with Matt and let me provide further support to your concern.

In order to store all the data into the spreadsheet, I would suggest creating sheet for each client account and named it with the customer ID (use this format xxx-xxx-xxxx). Then, you can use the modified script below. Also, please use different spreadsheets for Keywords Performance Report and Campaign Performance Report.

function generateReports() {
  for(var i in QUERIES) {
    var queryObject = QUERIES[i];
    var query = queryObject.query;
    var spreadsheetUrl = queryObject.spreadsheetUrl;

    var reportVersion = queryObject.reportVersion;
    //Logger.log(spreadsheetUrl + " " + query);
    var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);

    var sheet = spreadsheet.getSheetByName(AdsApp.currentAccount().getCustomerId());

    var report = AdWordsApp.report(query, {apiVersion: reportVersion});
    report.exportToSheet(sheet);
  }
}

Let me know if you have further questions.

Regards,
Ejay

[Kevin Montel]

Hi Ejay,

thank you for your answer. Storing the data in different sheets could be a solution, but my use case requires a final output with all the data presented in one single sheet. Besides that, I also need to figure out a way to overcome the 50 accounts limitation, since I manage more than that.

Let me try to explain my ideal workflow, so maybe we can figure out the best solution:

Google Ads (MCC) > Filter out desired campaigns from all the accounts > Get total weekly impressions and clicks for each campaign > Store this particular query in one single sheet

I am able to reproduce this workflow through the Google Ads Report tool with a scheduled download.

Hope this explanation helps to better understand my problem.

Thank you again,

Kevin

[Google Ads Scripts Forum Advisor]

Hi Kevin,

The workflow that you have mentioned is not possible with the current setup of the script as appending the returned data is currently not possible for exportToSheet(sheet), it is only overwriting the data instead. With this, you may try doing post-processing on your end to consolidate the data of client accounts in one sheet with the suggestion that I previously provided.

Let me know if you have further question.