Google Ads Script for Exporting Year-to-Date Data to Sheets

PPC

Google Ads Script for Exporting Year-to-Date Data to Sheets

Posted by seohelper on August 11, 2020 at 3:09 am

Hi everyone — I’m back again with another nifty variation on [the Google Ads script](https://www.optmyzr.com/blog/a-script-to-put-any-adwords-data-in-a-spreadsheet/) for exporting data into Sheets, from [Optimyzr.com](https://Optimyzr.com).

If you’re using this, you may have noticed that while there are a variety of options to export data, there’s really no one option to export data for the year to date (ex. January 1, 2020 to today). You’ll need to have to type in manually the start date and the end date, which isn’t really practical if you have to update the script everyday for those dates.

So what I did was to create a little JavaScript variable to pull the date today inside the query automatically. The updated code is below:

/**
> Export YTD data dynamically using AWQL
> Based on the AWQL to Spreadsheet script by Frederick Vallaeys of Optimyzr.com
> Original post: https://www.optmyzr.com/blog/a-script-to-put-any-adwords-data-in-a-spreadsheet/
> The start date is static, the end date is equal to the date today

IMPORTANT:
> Make sure that you fill in the spreadsheet URL and tab name in lines 24 and 25 for the export

**/

function main() {

var d = new Date();
var ISOdate = d.toISOString();
var fulldate = ISOdate.replace(/T(.*)/i, “”);
var nodashdate = fulldate.replace(/-/g, “”);
Logger.log(nodashdate);

var QUERIES = [ {‘query’ : ‘SELECT Date, AccountDescriptiveName, CampaignName, Impressions, SearchImpressionShare ‘ +
‘FROM CAMPAIGN_PERFORMANCE_REPORT ‘+
‘WHERE Impressions > 0 ‘+
‘DURING 20200101,’ + nodashdate,
‘spreadsheetUrl’ : ‘[INSERT SPREADSHEET URL HERE]’,
‘tabName’ : ‘[INSERT TAB NAME HERE]’,
‘reportVersion’ : ‘v201809’
}
];

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

}

Hope this helps! You can also use the script for specific time periods–you’ll just need to modify either the start or end dates. Feel free to play around with the date variable as well to pull dates other than today–maybe you want yesterday? Or if your client has some specific way to determine the week–let’s say their week starts on Friday; Google Ads doesn’t have a default setting for that.

[Obligatory link to my blog post about this as well, which has more detail about the script.](https://claracommutes.com/ytd-data-export-google-ads-scripts-sheets/)

seohelper replied 3 years, 8 months ago 1 Member · 0 Replies
0 Replies

Sorry, there were no replies found.