Budget Pacing Report

[Bharath mass]

Hi,

When I run the below script, received an error 'Missing ; before statement. (line 5)'.

Could anyone check help me on this?

function main() {

 // Locate the Spreadsheet and the sheet name

 var spreadsheet URL = 'https://docs.google.com/spreadsheets/d/1lrGtpOVnVwdVkb1vgTUFUubixNVXe4Y-363ljlhZZf0/edit#gid=1105847628';

 var spreadsheet = getSpreadsheet(spreadsheet URL);

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

}

[Sven Reinhardt]

Hi,

remove the empty space in your variable - it should be  spreadsheetURL rather than spreadsheet URL


Hth,
Sven

[Tyler Sidell (AdWords Scripts Team)]

Hi,

Thank you Sven for providing that solution. Bharath, please let us know if you have any further questions.

Thanks,

Tyler Sidell
AdWords Scripts Team

[Tyler Sidell (AdWords Scripts Team)]

Hi Bharath,

In the future, try not to post your spreadsheet URL on the forum.  I would suggest in your code to remove the gid part of the url.  For instance only use https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit.  Let me know if that works.  If not, please send your CID (reply privately to the author) and I'll take a look.

Thanks,

Tyler Sidell

AdWords Scripts Team

On Wednesday, January 11, 2017 at 1:18:32 AM UTC-5, Bharath mass wrote:

Also, I have removed the space between 'spreadsheet URL' in the line 69 - received the same error.

On Wednesday, January 11, 2017 at 11:44:57 AM UTC+5:30, Bharath mass wrote:

Hi Tyler,

I have tried removing that space, but now i see this error - "Invalid spreadsheet URL: https://docs.google.com/spreadsheets/d/1lrGtpOVnVwdVkb1vgTUFUubixNVXe4Y-363ljlhZZf0/edit#gid=1105847628 (line 69)"

@Sven - thanks for your reply, please have a look if you could fix this error.

Regards,

Bharath. U

[Lisa Leysen]

Hi Bharath,

I'd like to use this script as well but then I'd need to be able to copy your spreadsheet.

I also have the 'Invalid spreadsheet URL' error and I think that's why. Anyone reading this, please correct me if I'm wrong.

I requested access to it, can you please give me permission? Or let me know if you can send me a copy?

It would be really handy for me.

Feel free to contact me on LinkedIn as well.

Kind regards,

Lisa

[Adrian Catambay (AdWords Scripts Team)]

Hello Lisa,

As what my colleague Tyler have suggested, you could try to remove the gid part of the URL. Nonetheless, you would still have to request permission from the owner of the spreadsheet to view or edit it.

Thanks,

Adrian

AdWords Scripts Team

[Lisa Leysen]

Hi Adrian,

It doesn't seem to work, my URL is: https://docs.google.com/spreadsheets/d/spreadsheetID/edit#gid=0

Is there anything else I need to adjust?

It's was in line 5 where the URL is posted, but there's still an error in line 69.

Kind regards,

Lisa

[Adrian Catambay (AdWords Scripts Team)]

Hello Lisa,

Could you please Reply privately to author with your CID, script name, and access to the spreadsheet your script uses, so that I could properly investigate the cause of the error?