Invalid Spreadsheet URL?

[Charlie Busby]

Hi, I've entered a script to track spend over the month and the only error I get is 'invalid spreadsheet URL'

I've linked the script to a google doc, and tried using the sharable link (with edit access), or making the spreadsheet public, nothing has worked.

Is there a problem with the formatting on the page or is there an issue with the script?

Thanks!!!

function main() {

 // Locate the Spreadsheet and the sheet name

 var SURL = 'https://docs.google.com/spreadsheets/d/1BBPwgu1-fGuM2SYcznavmSJDqidYIbtJFqzPTqCJiV0/edit?usp=sharing';
 var spreadsheet = getSpreadsheet(SURL);
 var sheet = spreadsheet.getSheetByName('Spending');

 // Initialize dates

 var startdate = new Date(sheet.getRange(3,2).getValue());
 var enddate = new Date(sheet.getRange(4,2).getValue());
 var tempdate = new Date(sheet.getRange(3,2).getValue());
 startdate.setTime(startdate.getTime() + (3*60*60*1000));
 enddate.setTime(enddate.getTime() + (3*60*60*1000));
 tempdate.setTime(tempdate.getTime() + (3*60*60*1000));

 var startdateminusone = new Date();
 startdateminusone.setTime(startdate.getTime() - (24 * 60 * 60 * 1000));

 var today = new Date();

 // Set main formulas in the Spreadsheet

 sheet.getRange(2,4).setValue(Utilities.formatDate(startdateminusone, "PST", "yyyy-MM-dd"));
 sheet.getRange(2,5,1,3).setValues([[0,0,0]]);
 sheet.getRange(11,2).setFormula("=sum(E:E)");
 sheet.getRange(6,2).setFormula("=B4-B3+1");
 sheet.getRange(14,2).setFormula("=B4-ROUNDDOWN(today(),0)");
 sheet.getRange(12,2).setFormula("=B8-B11");
 sheet.getRange(16,2).setFormula("=B12/B14");
 sheet.getRange(9,2).setFormula("=B8/B6");

 // Loop through every date of the campaign and retrieve the total spent at that date

 var i = 0;
 while (enddate >= tempdate) {

 var campaignsIterator = AdWordsApp.campaigns().get();
 var spendingTotal = 0;
 while (campaignsIterator.hasNext()) {
 var campaign = campaignsIterator.next();
 var stats = campaign.getStatsFor(Utilities.formatDate(startdate, "PST", "yyyyMMdd"),
 Utilities.formatDate((tempdate), "PST", "yyyyMMdd"));
 spendingTotal = spendingTotal + stats.getCost();
 }

 // Write date, spent-this-day, spent-to-date and ideal pacing in the spreadsheet

 sheet.getRange(3+i,4).setValue(Utilities.formatDate(tempdate, "PST", "yyyy-MM-dd"));

 if (today >= tempdate){
 sheet.getRange(3+i,5).setFormula("R[0]C[1]-R[-1]C[1]");
 sheet.getRange(3+i,6).setValue(spendingTotal);
 }

 sheet.getRange(3+i,7).setFormula("($B$8/$B$6)*(row()-2)");

 tempdate.setTime(tempdate.getTime() + (24 * 60 * 60 * 1000));
 i = i + 1;
 }
 }

// This function retrieves the Spreadsheet from the URL

function getSpreadsheet(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);
}

[Tyler Sidell (AdWords Scripts Team)]

Hi Charlie,

You are experiencing that error from the getSpreadsheet() function:

function getSpreadsheet(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);
}

You can avoid this method all together and instead use only the SpreadsheetApp.openByUrl() method which will do the same functionality for you:

var SURL = 'https://docs.google.com/spreadsheets/d/1BBPwgu1-fGuM2SYcznavmSJDqidYIbtJFqzPTqCJiV0/edit?usp=sharing';

var spreadsheet = SpreadsheetApp.openByUrl(SURL);

Thanks,
Tyler Sidell
AdWords Scripts Team