Save AdWords reports to a specific folder in Google Drive

// Folder name
var FOLDER_NAME = 'My-Clients-AdWords-Reports';

    var spreadsheetName = spreadsheet.getName();

    var subFolder = DriveApp.getFoldersByName(FOLDER_NAME).next();
    var myFile = DriveApp.getFilesByName(spreadsheetName).next();
    var oldParent = myFile.getParents().next();

    oldParent.removeFile(myFile);
    subFolder.addFile(myFile);

• First we grab the spreadsheet name we are working on (note this relies on the ‘spreadsheet’ object which is created elsewhere in the script.)
• Then we set the value of subFolder to the folder where we want to save the file
• In the third line we grab the spreadsheet file we’ve created
• In the fourth line we determine the folder where the file currently resides. By default that’s the root folder of your Google docs account.
• In the last two lines we remove the file from it’s current folder (the root) and save it to the new folder.

// Comma-separated list of recipients. Comment out to not send any emails.
var RECIPIENT_EMAIL = '[email protected]';

// URL of the default spreadsheet template. This should be a copy of http://goo.gl/cULxUX
var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/1qb8ctGzyv5Wzx2YVrhITOYxs0rtJr5QEeuiGeQ6uxfM/edit#gid=0';

// Client name
var CLIENT_NAME = 'My-Client';

// Folder name
var FOLDER_NAME = 'KeywordPerformance-' + CLIENT_NAME;

// Schedule
var REPORT_SCHEDULE = 'LAST_WEEK';

/**
 * This script computes a keyword performance report
 * and outputs it to a Google spreadsheet. The spreadsheet
 * url is logged and emailed.
 */
function main() {
    var spreadsheet = copySpreadsheet(SPREADSHEET_URL);
    var sheet = spreadsheet.getSheetByName('Report');
    var spreadsheetName = spreadsheet.getName();

    sheet.getRange(1, 2, 1, 1).setValue('Date');
    sheet.getRange(1, 3, 1, 1).setValue(new Date());
    spreadsheet.getRangeByName('account_id').setValue(AdWordsApp.currentAccount().
        getCustomerId());
    outputQualityScoreData(sheet);
    outputPositionData(sheet);
    Logger.log('Keyword performance report available at\n' +
    spreadsheet.getUrl());
    if (RECIPIENT_EMAIL) {
        MailApp.sendEmail(RECIPIENT_EMAIL,
            'Keyword Performance Report is ready',
            spreadsheet.getUrl());
    }

    var subFolder = DriveApp.getFoldersByName(FOLDER_NAME).next();
    var myFile = DriveApp.getFilesByName(spreadsheetName).next();
    var oldParent = myFile.getParents().next();

    oldParent.removeFile(myFile);
    subFolder.addFile(myFile);
}

/**
 * Calculates local time based on UTC
 * @param {string} offset The URL of the spreadsheet.
 * @return {localTime} return time as a string.
 */

function calcTime(offset) {

    // create Date object for current location
    d = new Date();

    // convert to msec
    // add local time zone offset
    // get UTC time in msec
    utc = d.getTime() + (d.getTimezoneOffset() * 60000);

    // create new Date object for different city
    // using supplied offset
    nd = new Date(utc + (3600000*offset));

    var localTime = nd.toLocaleDateString('en-AU');

    // return time as a string
    return localTime;

}

/**
 * Retrieves the spreadsheet identified by the URL.
 * @param {string} spreadsheetUrl The URL of the spreadsheet.
 * @return {SpreadSheet} The spreadsheet.
 */
function copySpreadsheet(spreadsheetUrl) {

    return SpreadsheetApp.openByUrl(spreadsheetUrl).copy(
        'Keyword Performance Report - '+REPORT_SCHEDULE+' - '+CLIENT_NAME+' ' + calcTime('+10') );

}

/**
 * Outputs Quality score related data to the spreadsheet
 * @param {Sheet} sheet The sheet to output to.
 */
function outputQualityScoreData(sheet) {
    // Output header row
    var header = [
        'Quality Score',
        'Num Keywords',
        'Impressions',
        'Clicks',
        'CTR (%)',
        'Cost'
    ];
    sheet.getRange(3, 2, 1, 6).setValues([header]);

    // Initialize
    var qualityScoreMap = [];
    for (i = 1; i < = 10; i++) {
        qualityScoreMap[i] = {
            numKeywords: 0,
            totalImpressions: 0,
            totalClicks: 0,
            totalCost: 0.0
        };
    }

    // Compute data
    var keywordIterator = AdWordsApp.keywords()
        .forDateRange(REPORT_SCHEDULE)
        .withCondition('Impressions > 0')
        .get();
    while (keywordIterator.hasNext()) {
        var keyword = keywordIterator.next();
        var stats = keyword.getStatsFor(REPORT_SCHEDULE);
        var data = qualityScoreMap[keyword.getQualityScore()];
        if (data) {
            data.numKeywords++;
            data.totalImpressions += stats.getImpressions();
            data.totalClicks += stats.getClicks();
            data.totalCost += stats.getCost();
        }
    }

    // Output data to spreadsheet
    var rows = [];
    for (var key in qualityScoreMap) {
        var ctr = 0;
        var cost = 0.0;
        if (qualityScoreMap[key].numKeywords > 0) {
            ctr = (qualityScoreMap[key].totalClicks /
            qualityScoreMap[key].totalImpressions) * 100;
        }
        var row = [
            key,
            qualityScoreMap[key].numKeywords,
            qualityScoreMap[key].totalImpressions,
            qualityScoreMap[key].totalClicks,
            ctr.toFixed(2),
            qualityScoreMap[key].totalCost];
        rows.push(row);
    }
    sheet.getRange(4, 2, rows.length, 6).setValues(rows);
}

/**
 * Outputs average position related data to the spreadsheet.
 * @param {Sheet} sheet The sheet to output to.
 */
function outputPositionData(sheet) {
    // Output header row
    headerRow = [];
    var header = [
        'Avg Position',
        'Num Keywords',
        'Impressions',
        'Clicks',
        'CTR (%)',
        'Cost'
    ];
    headerRow.push(header);
    sheet.getRange(16, 2, 1, 6).setValues(headerRow);

    // Initialize
    var positionMap = [];
    for (i = 1; i < = 12; i++) {
        positionMap[i] = {
            numKeywords: 0,
            totalImpressions: 0,
            totalClicks: 0,
            totalCost: 0.0
        };
    }

    // Compute data
    var keywordIterator = AdWordsApp.keywords()
        .forDateRange(REPORT_SCHEDULE)
        .withCondition('Impressions > 0')
        .get();
    while (keywordIterator.hasNext()) {
        var keyword = keywordIterator.next();
        var stats = keyword.getStatsFor(REPORT_SCHEDULE);
        if (stats.getAveragePosition() < = 11) {
            var data = positionMap[Math.ceil(stats.getAveragePosition())];
        } else {
            // All positions greater than 11
            var data = positionMap[12];
        }
        data.numKeywords++;
        data.totalImpressions += stats.getImpressions();
        data.totalClicks += stats.getClicks();
        data.totalCost += stats.getCost();
    }

    // Output data to spreadsheet
    var rows = [];
    for (var key in positionMap) {
        var ctr = 0;
        var cost = 0.0;
        if (positionMap[key].numKeywords > 0) {
            ctr = (positionMap[key].totalClicks /
            positionMap[key].totalImpressions) * 100;
        }
        var row = [
            key < = 11 ? key - 1 + ' to ' + key : '>11',
            positionMap[key].numKeywords,
            positionMap[key].totalImpressions,
            positionMap[key].totalClicks,
            ctr.toFixed(2),
            positionMap[key].totalCost
        ];
        rows.push(row);
    }
    sheet.getRange(17, 2, rows.length, 6).setValues(rows);

}

Categorised in: Search Engine Marketing

This post was written by WillyNilly