Script to manage budget pacing

[Akshay Godiya]

Hi, 

I have seen one of the scripts for the budget pacing in this group.

I tried to apply that script to my MCC.

I have changed the master_id and added my spreadsheet id here, so when i am running this script i am facing this error:

The number of rows in the range must be at least 1. (file Code.gs, line 25)

Can you please help me with this script correction.

Here is the Script:

var master_id = '1xy_7wxWNr5PCytjISVYXj-OjLdUyerlWwFEIF0gjoGw';

var too_much = "#ffa0a0";

var too_less = "#ffe4c9";

var just_right = "#d6fcd6";

var header_bg = "#e5e5e5";

  

/* === STOP EDITING === */

var now = new Date();

var now = new Date(now.getUTCFullYear(), now.getUTCMonth(), now.getUTCDate(),  0, 0, 0);

var yesterday = new Date(now.getFullYear(), now.getMonth(), now.getDate() - 1,  now.getHours(), now.getMinutes(), now.getSeconds());

function main() {

  

    var spreadsheet_keys = []

    

    var master = SpreadsheetApp.openById(master_id);

    var idSheet = getSheet( "ids", master ); //what sheet do i want to open

    var ss_ids = idSheet.getRange( 2, 1, idSheet.getLastRow()-1, 1 ).getValues();

  

      

    for ( j=0; j < ss_ids.length; j++ ) {

   

       spreadsheet_keys.push(ss_ids[j][0]);

   

    }

  

for ( i=0; i<spreadsheet_keys.length; i++ ) {

      

        var ss_id = spreadsheet_keys[i];

     

var ss = SpreadsheetApp.openById(ss_id);

var sheet = getSheet(  Utilities.formatDate( now, "CET", "yy" ) + "-" + getMonthName( now.getMonth() ), ss );

write_spendings_in_sheet( sheet )

if ( now.getDate() == 1 ) {

    // if first day of month, also write in the sheet of last month

var sheet_last_month = getSheet(  Utilities.formatDate( yesterday, "CET", "yy" ) + "-" + getMonthName( yesterday.getMonth() ), ss );

write_spendings_in_sheet( sheet_last_month );

}

}

}

function write_spendings_in_sheet( sheet ) {

  var headings = [ [ "ID","Planbudget","Start","End","Name","Label/Mitarbeiter","Daily Run Rate", "Spent yesterday", "Difference", "Daily spent L7D","Spent",  "Budget left","Spent-%", "Days Gone-%" ] ];

  sheet.getRange( 1, 1, 1, headings[0].length ).setValues( headings ).setFontWeight("bold").setBackground(header_bg);

  

  var ids = sheet.getRange("A2:A150").getValues();

  var row_last_id = ids.filter(String).length; // get the number of ids in the sheet

for ( row = 2; row <= row_last_id+1; row++ ) {

        // get the values from the sheet

var id = sheet.getRange( row, 1).getValue();

      //Logger.log(id);

      

var budget = sheet.getRange( row, 2).getValue();

      

      if ( sheet.getRange( row, 3).getValue() == "" ) {

        // when start date is empty, set to yesterday

        

        var start = yesterday;

        sheet.getRange( row, 3).setValue( yesterday ).setBackground(header_bg);

        

      } else {        

        

        var start = sheet.getRange( row, 3).getValue();

      

      }

      

var startDateApi = Utilities.formatDate( start, "CET", "yyyyMMdd" ); // for use as daterange to get the stats

      if ( sheet.getRange( row, 4).getValue() == "" ) {

         // when end date is empty, set to yesterday

        

        var end = yesterday;

        sheet.getRange( row, 4).setValue( yesterday ).setBackground(header_bg);

        

      } else {

        

        var end = sheet.getRange( row, 4).getValue();

      

      }

      

var yesterdayApi =  Utilities.formatDate( yesterday, "UTC", "yyyyMMdd" ); // for use as daterange to get the stats

var accountIterator = MccApp.accounts().withIds( [id] ).get();

while (accountIterator.hasNext()) {

var account  = accountIterator.next();

          

// Write name of the account in the sheet as a check

sheet.getRange( row, 5 ).setValue( account.getName() );

          

          // Get the names of manager if applicable. Labels have to start with 'MA_'

            var accountLabelSelector = account.labels().withCondition("Name STARTS_WITH_IGNORE_CASE 'MA_'");

var accountLabelIterator = accountLabelSelector.get();

while (accountLabelIterator.hasNext()) {

var accountLabel = accountLabelIterator.next();

sheet.getRange(row,6).setValue( accountLabel.getName().substr(3) );

break;

}

          

          

            // Calculating the metrics

          

var accountStatsYes = account.getStatsFor( "YESTERDAY" );

var accountStatsL7D = account.getStatsFor( "LAST_7_DAYS" );

if ( Utilities.formatDate( now, "CET", "yyyyMMdd" ) == startDateApi ) {

var spendThisMonth = 0;

} else {

var accountStats = account.getStatsFor( startDateApi, yesterdayApi );

var spendThisMonth = accountStats.getCost(); 

}

var spentPct = spendThisMonth/budget;

var toSpent = budget - spendThisMonth;

var daysInTimeFrame =  Math.round((end - start)/(3600000*24)) + 1;      

          

          

var daysLeft = Math.round((end - yesterday)/(3600000*24));

                    

            var daysGonePct = 1 - daysLeft/daysInTimeFrame

var runRate = toSpent / daysLeft;

var spendYesterday = accountStatsYes.getCost();          

          

var difference = spendYesterday - runRate;

            

            var spendL7D = accountStatsL7D.getCost() / 7;

var metrics = [ [ runRate, spendYesterday, difference, spendL7D, spendThisMonth, toSpent, spentPct, daysGonePct ] ];

          

sheet.getRange( row, 7, 1, metrics[0].length ).setValues( metrics );

          

var bgSpentPct = just_right;

          

if ( spentPct > daysGonePct*1.05  ) { bgSpentPct = too_much; }

if ( spentPct < daysGonePct*0.95  ) { bgSpentPct = too_less; }

          

sheet.getRange( row, 13 ).setBackground( bgSpentPct );

          

var bgspendYesterday = just_right;          

          

if ( spendYesterday*0.95 > runRate && runRate > 0 ) { bgspendYesterday = too_much; }

if ( spendYesterday*1.05 < runRate && runRate > 0 ) { bgspendYesterday = too_less; }

if ( runRate <= 0 && spendYesterday > 0 ) { bgspendYesterday = too_much; }

          

sheet.getRange( row, 9 ).setBackground( bgspendYesterday );

          

}

}  

}

function getSheet( sheetName, spreadsheet ) {

if ( spreadsheet.getSheetByName( sheetName ) == null ) {

//if sheet doesnt exist yet, create a new one

var sheet = spreadsheet.insertSheet( sheetName );

} else {

var sheet = spreadsheet.getSheetByName( sheetName );

}

return sheet;

}

function getDaysInMonth ( month, year ) {

var d31 = new Date(year,month,31);

var d30 = new Date(year,month,30);

var d29 = new Date(year,month,29);

if ( d31.getMonth() == month ) {

var days = 31;

} else if ( d30.getMonth() == month ) { 

var days = 30;

} else if ( d29.getMonth() == month ) { 

var days = 29;

} else {

var days = 28;

}

return days;

}

function getMonthName ( monthIndex ) {

var months = ["Jan", "Feb", "Mrz", "Apr", "Mai", "Jun", "Jul", "Aug", "Sep", "Okt", "Nov", "Dez"];

return months[monthIndex];

}

Thanks & Regards

Akshay

[Akshay Godiya]

Hi, 

I am new to the script, so i don't have much knowledge about the script.

So Please Help me out in that way which can be easier for me.

Thank You So much In Advance.

Thanks 

Akshay

[Google Ads Scripts Forum Advisor]

Hi Akshay,

Thanks for reaching out. I am Harry from the Google Ads Scripts Team.

Kindly provide the following so that I can check this on our end and assist you further:

• CID
• Script's name
• Shareable link to the spreadsheet you are using

Looking forward to your reply.

Thanks,

Harry Cliford Rivera Google Ads Scripts Team

ref:_00D1U1174p._5004Q2IyL4F:ref

[Akshay Godiya]

Hi Harry,

Can you please provide your email address, so that I can share everything through email rather than going with a group?

Thanks & Regards

Akshay

[Google Ads Scripts Forum Advisor]

Hi Akshay,

Thanks for coming back. You may send them here or privately via the reply to author option. Note that you may need to join the Google Group for you to use this option. If this option is not available at your end still, you may send it through our email (googleadsscr...@google.com) instead.

[Akshay Godiya]

Hi Harry,

I have shared everything on email, can you please check and let me know.

Thanks & Regards

Akshay

[Google Ads Scripts Forum Advisor]

Hi Akshay,

Thanks for providing the requested details. However, since this is a third party script that I am unfamiliar with, can you kindly clarify the following points to further my investigation? To let you know, the error is with regard to the script not able to find the supposed range.

• Does the script have a provided template spreadsheet? Can you kindly provide that to me as well?
• I would also appreciate it if you can provide context to what does the script's functions is with regard to budget pacing.
• Does it takes values from the spreadsheet and log results into the sheet accordingly?
• Any other details is highly appreciated.

[Akshay Kumar Godiya]

Hi Harry,

 

This script does not have any template, I have picked up this code from here:

https://groups.google.com/g/adwords-scripts/c/Kh3bYTs7ftc

 

In this you can also find the screenshot how the script actually looks like.

 

I need the same to find out how my campaigns are spending on daily basis, what is the difference between planned budget and spent budget, Percentage of days gone, Percentage of budget spent everything you can see in the snap shot attached.

 

It fetched directly from the google ads all the data, Please check the snap shot what much data this script provides.

 

Let me know if you have any question.

 

Thanks & Regards

Akshay

 

 

From: Google Ads Scripts Forum on behalf of adsscripts <adwords...@googlegroups.com>
Sent: Friday, June 25, 2021 3:28 PM
To: adwords...@googlegroups.com
Subject: Re: Script to manage budget pacing

 

Hi Akshay,

Thanks for providing the requested details. However, since this is a third party script that I am unfamiliar with, can you kindly clarify the following points to further my investigation? To let you know, the error is with regard to the script not able to find the supposed range.

• Does the script have a provided template spreadsheet? Can you kindly provide that to me as well?
• I would also appreciate it if you can provide context to what does the script's functions is with regard to budget pacing.
• Does it takes values from the spreadsheet and log results into the sheet accordingly?
• Any other details is highly appreciated.

Looking forward to your reply.

Thanks,

Harry Cliford Rivera Google Ads Scripts Team

ref:_00D1U1174p._5004Q2IyL4F:ref

--
-- You received this message because you are subscribed to the Google Groups AdWords Scripts Forum group. Please do not reply to this email. To post to this group or unsubscribe please visit https://developers.google.com/adwords/scripts/community.
---
You received this message because you are subscribed to a topic in the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/adwords-scripts/-q7_7vPIAW0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to adwords-scrip...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/FfDv7000000000000000000000000000000000000000000000QV951E00Bt1XtQOdReaSfVsqd8XBQA%40sfdc.net.

------------------------------------------------------------------------
Disclaimer The information in this email and any attachments may contain proprietary and confidential information that is intended for the addressee(s) only. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, retention or use of the contents of this information is prohibited. When addressed to our clients or vendors, any information contained in this e-mail or any attachments is subject to the terms and conditions in any governing contract. If you have received this e-mail in error, please immediately contact the sender and delete the e-mail.

[Google Ads Scripts Forum Advisor]

Hi Akshay,

I work along with Harry. Allow me to assist you in this.

With regard to your concern, I've checked the link that you provided and it appears that the script does need the spreadsheet template in order to work. Usually, our solution script comes with the spreadsheet template which contains configurations. Since this is a script created by a third-party, we recommend the reach out to the creator of the script instead.

Regards,

Teejay Wennie Pimentel Google Ads Scripts Team

ref:_00D1U1174p._5004Q2IyL4F:ref

[Akshay Kumar Godiya]

Hey Teejay,

 

As I don’t have the spreadsheet template and actually I am not aware that to which third party I should go with, because I am not aware about that third party who have made it.

The link which I have given you, there you might have got the spreadsheet from their end, if possible can you please help me out with that, because other than this I don’t think that I would be having another source to find out that third party.

 

Let me know if you can help me in that.

 

Thanks & Regards

Akshay

 

From: Google Ads Scripts Forum on behalf of adsscripts <adwords...@googlegroups.com>

Sent: Monday, June 28, 2021 11:30 AM
To: adwords...@googlegroups.com
Subject: RE: Script to manage budget pacing

 

Hi Akshay,

I work along with Harry. Allow me to assist you in this.

With regard to your concern, I've checked the link that you provided and it appears that the script does need the spreadsheet template in order to work. Usually, our solution script comes with the spreadsheet template which contains configurations. Since this is a script created by a third-party, we recommend the reach out to the creator of the script instead.

Regards,

Teejay Wennie Pimentel Google Ads Scripts Team

ref:_00D1U1174p._5004Q2IyL4F:ref

--
-- You received this message because you are subscribed to the Google Groups AdWords Scripts Forum group. Please do not reply to this email. To post to this group or unsubscribe please visit https://developers.google.com/adwords/scripts/community.
---
You received this message because you are subscribed to a topic in the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/adwords-scripts/-q7_7vPIAW0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to adwords-scrip...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/rRyTn000000000000000000000000000000000000000000000QVEDZS001ioQCdMUS3-m1zPFg_tlKA%40sfdc.net.

[Google Ads Scripts Forum Advisor]

Hi Akshay,

Thank you for getting back to us.

I've checked the thread that you provided, and can see that the owner of the thread had a discussion with my colleague regarding the script. My colleague requested for an access to the spreadsheet template that the script is using, unfortunately, the user there didn't response back. I'm afraid that we weren't able to proceed assisting you further unless we have the spreadsheet template being use by the script. With that said, we would recommend to reach out directly to the user who posted that script, and ask them to share the spreadsheet template to you.

Regards,

Teejay Wennie Pimentel Google Ads Scripts Team

ref:_00D1U1174p._5004Q2IyL4F:ref

[Akshay Kumar Godiya]

Hi Teejay,

 

No Problem, is there any script which can help me out in this?

The script which can work in the same way and provide me every glimpse where I need to pace up the budget and where I can under pace the budget, I would be needing this script for MCC.

 

Thanks & Regards

Akshay

 

From: Google Ads Scripts Forum on behalf of adsscripts <adwords...@googlegroups.com>
Sent: Monday, June 28, 2021 3:26 PM
To: adwords...@googlegroups.com
Subject: RE: Script to manage budget pacing

 

Hi Akshay,

Thank you for getting back to us.

I've checked the thread that you provided, and can see that the owner of the thread had a discussion with my colleague regarding the script. My colleague requested for an access to the spreadsheet template that the script is using, unfortunately, the user there didn't response back. I'm afraid that we weren't able to proceed assisting you further unless we have the spreadsheet template being use by the script. With that said, we would recommend to reach out directly to the user who posted that script, and ask them to share the spreadsheet template to you.

Regards,

Teejay Wennie Pimentel Google Ads Scripts Team

ref:_00D1U1174p._5004Q2IyL4F:ref

--
-- You received this message because you are subscribed to the Google Groups AdWords Scripts Forum group. Please do not reply to this email. To post to this group or unsubscribe please visit https://developers.google.com/adwords/scripts/community.
---
You received this message because you are subscribed to a topic in the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/adwords-scripts/-q7_7vPIAW0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to adwords-scrip...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/UKVNl000000000000000000000000000000000000000000000QVEOXY00q5GQlg_jSH-SZgD8rW-ZQQ%40sfdc.net.

[Akshay Kumar Godiya]

Hi Teejay,

 

Also, If you can let me know the name of the third party, so that I can contact him for the template?

 

Thanks & Regards

Akshay

 

From: Google Ads Scripts Forum <adwords...@googlegroups.com>
Sent: Tuesday, June 29, 2021 10:38 AM
To: Google Ads Scripts Forum on behalf of adsscripts <adwords...@googlegroups.com>
Subject: RE: Script to manage budget pacing

 

This email has been sent from a source external to Publicis Groupe. Please use caution when clicking links or opening attachments.
Cet email a été envoyé depuis une source externe à Publicis Groupe. Veuillez faire preuve de prudence lorsque vous cliquez sur des liens ou lorsque vous ouvrez des pièces jointes.

 

 

To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/SJ0PR02MB71687650078236725D8ED70C80029%40SJ0PR02MB7168.namprd02.prod.outlook.com.

[Google Ads Scripts Forum Advisor]

Hi Akshay,

Harry here. The script seems to have been created by the user who posted it in this forum link so maybe you can reach out to them to help you out further. On the other hand, you can take a look at the following available solution scripts that might be of use to you:

• Flexible Budgets - Manager Account
• Export Budget Recommendations
• Export Move Unused Budget Recommendations

Thanks,

Harry Cliford Rivera Google Ads Scripts Team

ref:_00D1U1174p._5004Q2IyL4F:ref