jasp1994/SQR report script.js Secret

## SQR report script.js
//** Script Developed by PMJ Digital **//
//** Visit pmjdigital.co.uk for more free Google Ads Scripts **//

var config = {
    accountIDs: ['CID 1, CID 2'],
    EMAIL_ADDRESS: '',
    TIME_FRAME: "LAST_30_DAYS"
}
}

function main() {

    var now = new Date();
    var today = JSON.stringify(new Date(now.getTime()));

    //creates spreadsheet
    var spreadsheet = SpreadsheetApp.create("MCC Level SQR " + today.substr(1, 10));

    //Gets all the accounts from MCC with selected account IDs
    var accountIterator = AdsManagerApp.accounts()
        .withIds(config.accountIDs)
        .get();

    //Iterates through accounts
    while (accountIterator.hasNext()) {
        var account = accountIterator.next();
        AdsManagerApp.select(account);
        var accountName = account.getName();
        //Logs account name
        Logger.log('Running Search Query Report For ' + accountName)

        var report = AdsApp.report("SELECT CampaignName, AdGroupName, KeywordTextMatchingQuery, Query, QueryMatchTypeWithVariant, Impressions, Clicks, Ctr, Cost, Conversions" +
            " FROM SEARCH_QUERY_PERFORMANCE_REPORT" +
            ' DURING ' + config.TIME_FRAME);
        spreadsheet.insertSheet(accountName)
        report.exportToSheet(spreadsheet.getActiveSheet());

    }
    var sheet = spreadsheet.getSheetByName('Sheet1');
    spreadsheet.deleteSheet(sheet);

    spreadsheet.addEditors([config.EMAIL_ADDRESS])


    //Builds email
    var emailHeader = [];
    var emailFooter = [];
    var emailBody = [];
    emailHeader.push('<html>',
        '<head></head>',
        '<body>');
    emailFooter.push('</body>',
        '</html>');
    emailBody.push("SQR Report available at " + spreadsheet.getUrl())

    //sends email

    MailApp.sendEmail({
        to: config.EMAIL_ADDRESS,
        name: 'AdwordsScript',
        subject: "Mcc SQR report ready for the " + config.TIME_FRAME,
        htmlBody: emailHeader.join('\n') + emailBody.join('\n') + emailFooter.join('\n')
    });

    Logger.log("Email sent with export")
}
	// Script Developed by PMJ Digital //
	// Visit pmjdigital.co.uk for more free Google Ads Scripts //

	var config = {
	accountIDs: ['CID 1, CID 2'],
	EMAIL_ADDRESS: '',
	TIME_FRAME: "LAST_30_DAYS"
	}
	}

	function main() {

	var now = new Date();
	var today = JSON.stringify(new Date(now.getTime()));

	//creates spreadsheet
	var spreadsheet = SpreadsheetApp.create("MCC Level SQR " + today.substr(1, 10));

	//Gets all the accounts from MCC with selected account IDs
	var accountIterator = AdsManagerApp.accounts()
	.withIds(config.accountIDs)
	.get();

	//Iterates through accounts
	while (accountIterator.hasNext()) {
	var account = accountIterator.next();
	AdsManagerApp.select(account);
	var accountName = account.getName();
	//Logs account name
	Logger.log('Running Search Query Report For ' + accountName)

	var report = AdsApp.report("SELECT CampaignName, AdGroupName, KeywordTextMatchingQuery, Query, QueryMatchTypeWithVariant, Impressions, Clicks, Ctr, Cost, Conversions" +
	" FROM SEARCH_QUERY_PERFORMANCE_REPORT" +
	' DURING ' + config.TIME_FRAME);
	spreadsheet.insertSheet(accountName)
	report.exportToSheet(spreadsheet.getActiveSheet());

	}
	var sheet = spreadsheet.getSheetByName('Sheet1');
	spreadsheet.deleteSheet(sheet);

	spreadsheet.addEditors([config.EMAIL_ADDRESS])


	//Builds email
	var emailHeader = [];
	var emailFooter = [];
	var emailBody = [];
	emailHeader.push('<html>',
	'<head></head>',
	'<body>');
	emailFooter.push('</body>',
	'</html>');
	emailBody.push("SQR Report available at " + spreadsheet.getUrl())

	//sends email

	MailApp.sendEmail({
	to: config.EMAIL_ADDRESS,
	name: 'AdwordsScript',
	subject: "Mcc SQR report ready for the " + config.TIME_FRAME,
	htmlBody: emailHeader.join('\n') + emailBody.join('\n') + emailFooter.join('\n')
	});

	Logger.log("Email sent with export")
	}