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