Adwords Script .getCells Gives Error

81 Views Asked by At

I've been creating a custom report using Adwords scripts. The report simply populates a google sheet with desired data points for various keywords. I have used the .getRange() and .setValues() methods to populate some of the table cells in the google sheet however, when it comes to inserting the actual data, I encounter the following error in the log:

The coordinates or dimensions of the range are invalid. (line 73)

Line 73 looks like this:

sheet.getRange(4, 1, rows.length, 8).setValues(rows);

At first I thought the rows.length might be the culprit, but when I change that to a number (for testing purposes) I get a different error:

Incorrect range height, was 0 but should be 20 (line 73)

Elsewhere in the code I am using .getRange twice and those cells populate with no problem. They are the account name and date, which I dynamically insert. What am I doing wrong? I can't understand the errors that it's giving me or how to fix it.

Here is a copy of the whole script:

var ROOT_FOLDER_NAME = 'Keyword Performance';
var SPREADSHEET_URL = 'exampleURL.com';
var ACCT_REPEATER = MccApp.accounts().get();
var ACCOUNTS = [];
var rows=[];


function main() {
    var folder = DriveApp.getFoldersByName(ROOT_FOLDER_NAME).next();
    var formattedDate = Utilities.formatDate(new Date(), 'GMT', 'MMM d, YYYY');

    while (ACCT_REPEATER.hasNext()) {
        processAccount(ACCT_REPEATER.next(), folder);
    }
}



function processAccount(account, rootFolder) {
    MccApp.select(account);
    var spreadsheet = copySpreadsheet(SPREADSHEET_URL, rootFolder, account.getName());
    var sheet = spreadsheet.getSheetByName('Template');
    var acctName = account.getName().split("- ").pop();
    sheet.getRange(2, 2, 1, 1).setValue(new Date());
    sheet.getRange(2, 6, 1, 1).setValue(acctName);
    outputData(account, sheet);
}



function copySpreadsheet(spreadsheetUrl, rootFolder) {
    var fileName = "Keyword Quality Score Report";
    var copy = SpreadsheetApp.openByUrl(spreadsheetUrl).copy(fileName);

    while (ACCT_REPEATER.hasNext()) {
        var sheet = copy.getSheetByName('Template');
        var account = ACCT_REPEATER.next().getName().split("- ").pop();
        copy.insertSheet(account, 1, {template: sheet});
        copy.getSheetByName(account).getRange(2, 2, 1, 1).setValue(new Date());
        copy.getSheetByName(account).getRange(2, 6, 1, 1).setValue(account);
    }
    var newFile = DriveApp.getFileById(copy.getId());
    var oldParent = newFile.getParents().next();
    oldParent.removeFile(newFile);
    rootFolder.addFile(newFile);
    return SpreadsheetApp.openByUrl(newFile.getUrl());
}



function outputData(account, sheet) {
    MccApp.select(account);
    var keywordIterator = AdWordsApp.keywords().get();

    if (keywordIterator.hasNext()) {
        while (keywordIterator.hasNext()) {
          var keyword = keywordIterator.next();
          var stats = keyword.getStatsFor('TODAY');
          var row = [
                    keyword.getCampaign().getName(),
                    keyword.getAdGroup().getName(),
                    keyword.getText(),
                    keyword.getQualityScore(),
                    stats.getImpressions(),
                    stats.getClicks(),
                    stats.getCost(),
                    stats.getAveragePosition()
                ];

            if (keyword.getCampaign().isEnabled()){
                rows.push(row);
            }
            sheet.getRange(4, 1, 20, 8).setValues(rows);
          //Logger.log(row[1]);
        }
    }
}
0

There are 0 best solutions below