Invalid spreadsheet URL for YTD Keyword Performance Script

[Nate Arcane]

I have worked on and modified a script over time that will tell me some simple data about some keyword performance metrics in Adwords. It's a great and simple tool that I can show clients that tends to get their attention. It shows updated stats for the year-to-date. The issue is that I have had lately is nothing new. When I run the script now (vs. when I ran it 18 months ago) I get this error from the logs Invalid spreadsheet URL. 

Below is the script. I could use some help with making it work with whatever Google Spreadsheet address I need it to. (*hint: you have to have the tab named "yearly" at the bottom of the page in order to get this script to work):

HELP!

-------------

// Comma-separated list of recipients.

var RECIPIENT_EMAIL = 'YOUR EMAIL GOES HERE';

var date = new Date();

// Spreadsheet template.

var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/1yXhM2zo0UXmn6D9N4n56blQSJ0jHRHKfswOdTRwka28/edit#gid=0&vpid=A1';

/**

 * This script computes a keyword performance report

 * and outputs it to a Google spreadsheet. 

 */

function main() {

  var spreadsheet = copySpreadsheet(SPREADSHEET_URL);

  var sheet = spreadsheet.getSheetByName('Yearly');

  outputQualityScoreData(sheet);

  outputPositionData(sheet);

  Logger.log('yearly Keyword performance report - ' + spreadsheet.getUrl());

  MailApp.sendEmail(

    RECIPIENT_EMAIL, 'New yearly Report is ready.', spreadsheet.getUrl());

}

/**

 * Retrieves the spreadsheet identified by the URL.

 * @param {string} spreadsheetUrl The URL of the spreadsheet.

 * @return {SpreadSheet} The spreadsheet.

 */

function copySpreadsheet(spreadsheetUrl) {

  var matches = new RegExp('key=([^&#]*)').exec(spreadsheetUrl);

  if (!matches || !matches[1]) {

    throw 'Invalid spreadsheet URL: ' + spreadsheetUrl;

  }

  var spreadsheetId = matches[1];

  return SpreadsheetApp.openById(spreadsheetId);

}

/**

 * 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 (%)',

    'Conversions',

    'Cost'

  ];

  sheet.getRange(1, 1, 1, 7).setValues([header]);

  // Initialize

  var qualityScoreMap = [];

  for (i = 1; i <= 10; i++) {

    qualityScoreMap[i] = {

      numKeywords: 0,

      totalImpressions: 0,

      totalClicks: 0,

      totalConversions: 0,

      totalCost: 0.0

    };

  }

  // Compute data

  var keywordIterator = AdWordsApp.keywords()

      .forDateRange(date.getFullYear() + '0101',date.getFullYear() + ('0' + (date.getMonth()+1)).slice(-2) + ('0' + date.getDate()).slice(-2))

      .withCondition('Impressions > 0')

      .get();

  while (keywordIterator.hasNext()) {

    var keyword = keywordIterator.next();

    var stats = keyword.getStatsFor(date.getFullYear() + '0101',date.getFullYear() + ('0' + (date.getMonth()+1)).slice(-2) + ('0' + date.getDate()).slice(-2));

    var data = qualityScoreMap[keyword.getQualityScore()];

    if (data) {

      data.numKeywords++;

      data.totalImpressions += stats.getImpressions();

      data.totalClicks += stats.getClicks();

      data.totalConversions += stats.getConversions();

      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].totalConversions,

      qualityScoreMap[key].totalCost

    ];

    rows.push(row);

  }

  sheet.getRange(2, 1, rows.length, 7).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 (%)',

    'Conversions',

    'Cost'

  ];

  headerRow.push(header);

  sheet.getRange(14, 1, 1, 7).setValues(headerRow);

  // Initialize

  var positionMap = [];

  for (i = 1; i <= 12; i++) {

    positionMap[i] = {

      numKeywords: 0,

      totalImpressions: 0,

      totalClicks: 0,

      totalConversions: 0,

      totalCost: 0.0

    };

  }

  // Compute data

  var keywordIterator = AdWordsApp.keywords()

      .forDateRange(date.getFullYear() + '0101',date.getFullYear() + ('0' + (date.getMonth()+1)).slice(-2) + ('0' + date.getDate()).slice(-2))

      .withCondition('Impressions > 0')

      .get();

  while (keywordIterator.hasNext()) {

    var keyword = keywordIterator.next();

    var stats = keyword.getStatsFor(date.getFullYear() + '0101',date.getFullYear() + ('0' + (date.getMonth()+1)).slice(-2) + ('0' + date.getDate()).slice(-2));

    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.totalConversions += stats.getConversions();

    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].totalConversions,

      positionMap[key].totalCost

    ];

    rows.push(row);

  }

  sheet.getRange(15, 1, rows.length, 7).setValues(rows);

}

[Tyler Sidell (AdWords Scripts Team)]

Hi Nate,

You are experiencing this error due to the error catching in the copySpreadsheet function.  Try modifying that function to the following below to see if that helps the situation.

function copySpreadsheet(spreadsheetUrl) {
return SpreadsheetApp.openByUrl(spreadsheetUrl);
}

Thanks,

Tyler Sidell

AdWords Scripts Team

[yu...@breadcrumbdigital.com.au]

I have the same problem -Invalid argument: url

I am using the Keyword Performance Report - Manager Account which is published in Solutions. 

I didn't make any changes to the code except SPREADSHEET_URL, ACCOUNTS, ROOT_FOLDER_NAME.

I tried to change the function as Tyler Sidell recommended but it still gives the same error.

Please help, I am very new to Adwords Scripts.

[Tyler Sidell (AdWords Scripts Team)]

Hi,

Can you provide us with your CID (reply privately to the author) along with the name of the script so that we can take a look further?

Thanks,
Tyler Sidell
AdWords Scripts Team