TypeError: Cannot read property "length" from undefined.

[Mark Trapani]

Hi guys,

Got the following script which is returning the following error:

TypeError: Cannot read property "length" from undefined. (file Code.gs, line 80)

Any clue why this is happening?

var DECIMALS = 4; 

var DATE_RANGE = 'YESTERDAY'; 

var LAST_N_DAYS = 0;

var CSV_FILE_PREFIX = ""; 

var SPREADSHEET_URL = "https://docs.google.com/spreadsheets/d/1FxUwvGw1RbGz_-QHUkHa-Mn8lP0uWAD50xYzLHfBAMo/edit#gid=0";

var SPREADSHEET_NAME = "";

  

function main() {

  var isCSV = (CSV_FILE_PREFIX !== "");

  var allData = getKeywordsReport();

  var tabs = ['Account','Campaign','AdGroup','Keyword', "ImpsWeightsQS", "Ctr", "CpcBid", "AverageCpc", "SearchRankLostImpressionShare"];

  for(var i in tabs) {

    var tab = tabs[i];

    var dataToWrite = [];

    var cols = getCols(tab);

    var rowKeys = getRowKeys(tab,Object.keys(allData));

    for(var x in rowKeys) {

      var rowArray = [];

      var key = rowKeys[x];

      var row = allData[key];

      for(var y in cols) {

        rowArray.push(row[cols[y]]);

      }

      dataToWrite.push(rowArray);

    }

    if(isCSV) {

      writeDataToCSV(tab,dataToWrite);

    } else {

      writeDataToSpreadsheet(tab,dataToWrite);

    }

  }

}

function getRowKeys(tab,allKeys) {

  return allKeys.filter(function(e) { return (e.indexOf(tab) >= 0); }); 

}

function getCols(tab) {

  return {

    'Account' : ['Date','Account','ImpsWeightedQS'],

    'Campaign': ['Date','Account','Campaign','ImpsWeightedQS'],

    'AdGroup' : ['Date','Account','Campaign','AdGroup','ImpsWeightedQS'],

    'Keyword' : ['Date','Account','Campaign','AdGroup','Keyword','QS','ImpsWeightedQS','Ctr','CpcBid','AverageCpc','SearchRankLostImpressionShare']

  }[tab];

}

 

// Super fast spreadsheet insertion

function writeDataToSpreadsheet(tab,toWrite) {

  //This is where i am going to store all my data

  var spreadsheet;

  if(SPREADSHEET_NAME) {

    var fileIter = DriveApp.getFilesByName(SPREADSHEET_NAME);

    if(fileIter.hasNext()) {

      var file = fileIter.next();

      spreadsheet = SpreadsheetApp.openById(file.getId());

    } else {

      spreadsheet = SpreadsheetApp.create(SPREADSHEET_NAME);

    }

  } else if(SPREADSHEET_URL) {

    spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);

  } else {

    throw 'You need to set at least one of the SPREADSHEET_URL or SPREADSHEET_NAME variables.';

  }

  var sheet = spreadsheet.getSheetByName(tab);

  if(!sheet) {

    sheet = spreadsheet.insertSheet(tab);

    sheet.appendRow(getCols(tab));

  }

  

  var lastRow = sheet.getLastRow();

  var numRows = sheet.getMaxRows();

  if((numRows-lastRow) < toWrite.length) {

    sheet.insertRowsAfter((lastRow == 0) ? 1 : lastRow,toWrite.length-numRows+lastRow);

  }

  var range = sheet.getRange(lastRow+1,1,toWrite.length,toWrite[0].length);

  range.setValues(toWrite);

}

function writeDataToCSV(tab,toWrite) {

  if(!toWrite) { return; }

  var fileName = CSV_FILE_PREFIX + '_' + tab + '.csv';

  var file;

  var fileIter = DriveApp.getFilesByName(fileName);

  if(fileIter.hasNext()) {

    file = fileIter.next();

  } else {

    file = DriveApp.createFile(fileName, formatCsvRow(getCols(tab)));

  }

  var fileData = file.getBlob().getDataAsString();

  for(var i in toWrite) {

    fileData +=  formatCsvRow(toWrite[i]);

  }

  file.setContent(fileData);

  return file.getUrl();

}

function formatCsvRow(row) {

  for(var i in row) {

    if(row[i].toString().indexOf('"') == 0) {

      row[i] = '""'+row[i]+'""';

    }

    if(row[i].toString().indexOf('+') == 0) {

      row[i] = "'"+row[i];

    }

    if(row[i].toString().indexOf(',') >= 0 && 

       row[i].toString().indexOf('"""') != 0) 

    {

      row[i] = ('"'+row[i]+'"');

    }

  }

  return row.join(',')+'\n';

}

 

function getKeywordsReport() {

   var theDate = DATE_RANGE

  if(LAST_N_DAYS != 0) {

    theDate = getDateDaysAgo(LAST_N_DAYS)+','+getDateDaysAgo(1);

  }

  Logger.log('Using date range: '+theDate);

  var OPTIONS = { includeZeroImpressions : false };

  var cols = ['ExternalCustomerId',

              'CampaignId','CampaignName',

              'AdGroupId','AdGroupName',

              'Id','Criteria','KeywordMatchType',

              'IsNegative','Impressions', 'QualityScore', 

              'Ctr', 'CpcBid', 'AverageCpc','SearchRankLostImpressionShare'];

  var report = 'KEYWORDS_PERFORMANCE_REPORT';

  var query = ['select',cols.join(','),'from',report,

               'where AdNetworkType1 = SEARCH',

               'and CampaignStatus = ENABLED',

               'and AdGroupStatus = ENABLED',

               'and Status = ENABLED',

               'and QualityScore >= 1',

               'during',theDate].join(' ');

  var results = {};

  var reportIter = AdWordsApp.report(query, OPTIONS).rows();

  while(reportIter.hasNext()) {

    var row = reportIter.next();

    if(row.IsNegative == true || row.IsNegative === 'true') { continue; }

    loadHashEntry('Account:'+row.ExternalCustomerId,row,results);

    loadHashEntry('Campaign:'+row.CampaignId,row,results);

    loadHashEntry('AdGroup:'+[row.CampaignId,row.AdGroupId].join('-'),row,results);

    loadHashEntry('Keyword:'+[row.CampaignId,row.AdGroupId,row.Id].join('-'),row,results);

  }

  var dateStr = Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd');

  for(var i in results) {

    results[i]['Date'] = dateStr;

    results[i]['ImpsWeightedQS'] = (results[i]['totalImps'] === 0) ? 0 : round(results[i]['ImpsWeightedQS']/results[i]['totalImps']);

  }

  return results;

}

function loadHashEntry(key,row,results) {

  if(!results[key]) {

    results[key] = {

      SearchRankLostImpressionShare : 0,

      Ctr : 0,

      CpcBid : 0,

      AverageCpc : 0,

      QS : 0,

      ImpsWeightedQS : 0,

      totalImps : 0,

      Account : null,

      Campaign : null,

      AdGroup : null,

      Keyword : null

    };

  }

  results[key].SearchRankLostImpressionShare = parseFloat(row.SearchRankLostImpressionShare);

  results[key].Ctr = parseFloat(row.Ctr);

  results[key].CpcBid = parseFloat(row.CpcBid);

  results[key].AverageCpc = parseFloat(row.AverageCpc);

  results[key].QS = parseFloat(row.QualityScore);

  results[key].ImpsWeightedQS += (parseFloat(row.QualityScore)*parseFloat(row.Impressions));

  results[key].totalImps += parseFloat(row.Impressions);

  results[key].Account = row.ExternalCustomerId;

  results[key].Campaign = row.CampaignName;

  results[key].AdGroup = row.AdGroupName;

  results[key].Keyword = (row.KeywordMatchType === 'Exact') ? '['+row.Criteria+']' :

                         (row.KeywordMatchType === 'Phrase') ? '"'+row.Criteria+'"' : row.Criteria;

}

//A helper function to return the number of days ago.

function getDateDaysAgo(days) {

  var thePast = new Date();

  thePast.setDate(thePast.getDate() - days);

  return Utilities.formatDate(thePast, AdWordsApp.currentAccount().getTimeZone(), 'yyyyMMdd');

}

function round(val) {

  var divisor = Math.pow(10,DECIMALS);

  return Math.round(val*divisor)/divisor;

}

[Sravani Yelamarthi (AdWords Scripts Team)]

Hi Mark,

It appears to be one the references to the length property inside of the function, writeDataToCSV, that is causing the error. I do not have spreadsheet access, so I cannot run the script, but can you try testing which of the four references to the length property in that function are throwing this error? For example, I would start by isolating each use of length and seeing if there is one in particular that is causing the error. If you can isolate it, that will tell you which array is not defined.

Thanks,

Sravani Yelamarthi

AdWords Scripts Team