Google Ads scripts to update budget, export daily report, pause campaign upon budget cap

[// Copyright 2019, Silver Mouse Sdn Bhd, All Rights Reserved.
/**
* @name Google Ads Script – Auto Update Daily Budget
*
* @overview This script updates the daily budget for this individual account.
* For more information, visit developers.google.com/ads/scripts
*
* @author Ong Hock Seng, Digital Marketing Manager at Silver Mouse [[email protected]]
*
* @version 1.2
*
* @changelog
* – version 1.0
* – Released initial version
* – version 1.1
* – Retrieve budget from sheet
* – version 1.2
* – Updated for 2019
*
* @usage Replace Google sheet ID in line 36 and specify campaign name in line 126
*/
function main() {

// set to select this account, shortened to make it easier to write fetches
var account = AdsApp.currentAccount();

// fetch date, in PST
var date = new Date();

// convert time to the account's time zone
var month = Utilities.formatDate(date, timeZoneName, 'MMMM');
// link to sheet url
var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/XXXXXXXXXXXXXXXXXXXXXXXX/edit';

// specify sheet name (Sheet1 is default)
var SHEET_NAME = 'Sheet1';
// open and select sheet
var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
var sheet = ss.getSheetByName(SHEET_NAME);

Logger.log('Spreadsheet URL : ' + SPREADSHEET_URL);
Logger.log('Spreadsheet name : ' + ss.getName());
Logger.log('Sheet name : ' + SHEET_NAME);

Logger.log('—');

// naming all the ranges
var rJANUARY = sheet.getRange(2, 1);
var rFEBRUARY = sheet.getRange(3, 1);
var rMARCH = sheet.getRange(4, 1);
var rAPRIL = sheet.getRange(5, 1);
var rMAY = sheet.getRange(6, 1);
var rJUNE = sheet.getRange(7, 1);
var rJULY = sheet.getRange(8, 1);
var rAUGUST = sheet.getRange(9, 1);
var rSEPTEMBER = sheet.getRange(10, 1);
var rOCTOBER = sheet.getRange(11, 1);
var rNOVEMBER = sheet.getRange(12, 1);
var rDECEMBER = sheet.getRange(13, 1);

var rbJANUARY = sheet.getRange(2, 2);
var rbFEBRUARY = sheet.getRange(3, 2);
var rbMARCH = sheet.getRange(4, 2);
var rbAPRIL = sheet.getRange(5, 2);
var rbMAY = sheet.getRange(6, 2);
var rbJUNE = sheet.getRange(7, 2);
var rbJULY = sheet.getRange(8, 2);
var rbAUGUST = sheet.getRange(9, 2);
var rbSEPTEMBER = sheet.getRange(10, 2);
var rbOCTOBER = sheet.getRange(11, 2);
var rbNOVEMBER = sheet.getRange(12, 2);
var rbDECEMBER = sheet.getRange(13, 2);

// retrieving value for ranges
var vJANUARY = rJANUARY.getValue();
var vFEBRUARY = rFEBRUARY.getValue();
var vMARCH = rMARCH.getValue();
var vAPRIL = rAPRIL.getValue();
var vMAY = rMAY.getValue();
var vJUNE = rJUNE.getValue();
var vJULY = rJULY.getValue();
var vAUGUST = rAUGUST.getValue();
var vSEPTEMBER = rSEPTEMBER.getValue();
var vOCTOBER = rOCTOBER.getValue();
var vNOVEMBER = rNOVEMBER.getValue();
var vDECEMBER = rDECEMBER.getValue();

var vbJANUARY = rbJANUARY.getValue();
var vbFEBRUARY = rbFEBRUARY.getValue();
var vbMARCH = rbMARCH.getValue();
var vbAPRIL = rbAPRIL.getValue();
var vbMAY = rbMAY.getValue();
var vbJUNE = rbJUNE.getValue();
var vbJULY = rbJULY.getValue();
var vbAUGUST = rbAUGUST.getValue();
var vbSEPTEMBER = rbSEPTEMBER.getValue();
var vbOCTOBER = rbOCTOBER.getValue();
var vbNOVEMBER = rbNOVEMBER.getValue();
var vbDECEMBER = rbDECEMBER.getValue();

// set value for monthly budget

if (month == vJANUARY) {Logger.log('Budget from month : ' + vJANUARY); var monthlyBudget = vbJANUARY;}
if (month == vFEBRUARY) {Logger.log('Budget from month : ' + vFEBRUARY); var monthlyBudget = vbFEBRUARY;}
if (month == vMARCH) {Logger.log('Budget from month : ' + vMARCH); var monthlyBudget = vbMARCH;}
if (month == vAPRIL) {Logger.log('Budget from month : ' + vAPRIL); var monthlyBudget = vbAPRIL;}
if (month == vMAY) {Logger.log('Budget from month : ' + vMAY); var monthlyBudget = vbMAY;}
if (month == vJUNE) {Logger.log('Budget from month : ' + vMAY); var monthlyBudget = vbJUNE;}
if (month == vJULY) {Logger.log('Budget from month : ' + vJULY); var monthlyBudget = vbJULY;}
if (month == vAUGUST) {Logger.log('Budget from month : ' + vAUGUST); var monthlyBudget = vbAUGUST;}
if (month == vSEPTEMBER) {Logger.log('Budget from month : ' + vSEPTEMBER); var monthlyBudget = vbSEPTEMBER;}
if (month == vOCTOBER) {Logger.log('Budget from month : ' + vOCTOBER); var monthlyBudget = vbOCTOBER;}
if (month == vNOVEMBER) {Logger.log('Budget from month : ' + vNOVEMBER); var monthlyBudget = vbNOVEMBER;}
if (month == vDECEMBER) {Logger.log('Budget from month : ' + vDECEMBER); var monthlyBudget = vbDECEMBER;}

Logger.log('—');

// select the specific campaign here
var campaignIterator = AdsApp.campaigns()
.withCondition('Name = "SEM"')
.get();

// set to select this account, shortened to make it easier to write fetches
// var account = AdsApp.currentAccount();

// this section will begin running if there is a campaign that matches the campaign name you keyed in
if (campaignIterator.hasNext()) {

// fetch campaign that we have selected
var campaign = campaignIterator.next();

// fetch campaign stats for this month
var stats = campaign.getStatsFor("THIS_MONTH");

// fetch date, in PST
var date = new Date();

// convert time to the account's time zone
var timeZoneName = AdsApp.currentAccount().getTimeZone();
var timeZoneNumber = Utilities.formatDate(date, timeZoneName, 'Z');
var month = Utilities.formatDate(date, timeZoneName, 'MMMM yyyy');

// more conversions, for my reference while making this script
var now = Utilities.formatDate(date, timeZoneName, 'dd MMMM yyyy HH:mm:ss');
var dateNow = Utilities.formatDate(date, timeZoneName, 'dd MMMM yyyy');
var timeNow = Utilities.formatDate(date, timeZoneName, 'HH:mm:ss');
var yearNow = Utilities.formatDate(date, timeZoneName, 'yyyy');
var monthNow = Utilities.formatDate(date, timeZoneName, 'MM');

// calculate the amount of days left

var d = new Date(Utilities.formatDate(new Date(), timeZoneName, "MMM dd,yyyy HH:mm:ss"));
var totalDays = new Date(d.getFullYear(), d.getMonth() + 1, 0).getDate();
var daysPassed = d.getDate() – 1;
var daysLeft = totalDays – daysPassed;

// calculate budget left

var costUsed = stats.getCost();
var costLeft = monthlyBudget – costUsed;

// and divided by days left

var costNew = costLeft / daysLeft;

// log for reference
Logger.log('Account name : ' + account.getName());
Logger.log('Campaign : ' + campaign.getName());
Logger.log('Currency : ' + account.getCurrencyCode());
Logger.log('—');
Logger.log('Current budget : ' + campaign.getBudget());
Logger.log('—');
Logger.log('Account date : ' + dateNow);
Logger.log('Account time : ' + timeNow);
Logger.log('Time Zone Name : ' + timeZoneName);
Logger.log('Time Zone Number : ' + timeZoneNumber);
// fetch cost
Logger.log('—');
Logger.log('Cost this month so far : ' + stats.getCost());
Logger.log('—');
// fetch date
Logger.log('This month : ' + month);
Logger.log('Month Number : ' + monthNow );
Logger.log('Year Number : ' + yearNow );
// fetch days left
Logger.log('—');
Logger.log('Days in this month : ' + totalDays);
Logger.log('Days passed : ' + daysPassed);
Logger.log('Days left : ' + daysLeft);
Logger.log('—');
// fetch budget
Logger.log('Monthly budget : ' + monthlyBudget);
Logger.log('Budget left : ' + costLeft);
Logger.log('Budget per day : ' + costNew );

// set new budget

campaign.getBudget().setAmount(costNew);
//var newBudget =
Logger.log('—');
Logger.log('New daily budget : ' + campaign.getBudget());
Logger.log('—');
} else {

Logger.log('—');
Logger.log('Hmm it seems like there are no campaigns selected..');
Logger.log('—');

}
}]

[// Copyright 2019, Silver Mouse Sdn. Bhd., All Rights Reserved.

/**
* @name Google Ads Script – Auto Report
*
* @overview This script exports a report for this individual account.
* For more information, visit developers.google.com/ads/scripts
*
* @author Ong Hock Seng, Digital Marketing Manager at Silver Mouse [[email protected]]
*
* @version 1.0.3a
*
* @changelog
* – version 1.0
* – Released initial version
* – version 1.0.1
* – Minor spring cleaning
* – version 1.0.2
* – Lean and clean
* – version 1.0.2a
* – updated for 2018
* – version 1.03a
* – updated for 2019
*
* @usage Insert spreadsheet URL in line 39 & campaign name in line 52.
* Remove line 46 to select all campaigns.
*
*/

function main() {

// set to select this account, shortened to make it easier to write fetches
var account = AdsApp.currentAccount();

// set to get campaign info
var campaignIterator = AdsApp.campaigns()
.get();

// link to sheet url
var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/INSERT SPREADSHEET URL HERE/edit';

// shorten
var sheetName = SpreadsheetApp.openByUrl(SPREADSHEET_URL);

// print log for reference
Logger.log('Account name : ' + account.getName());
Logger.log('Currency code : ' + account.getCurrencyCode());
Logger.log('—');

// select info
var report = AdsApp.report(
'SELECT Date, Cost ' +
'FROM CAMPAIGN_PERFORMANCE_REPORT ' +
'WHERE CampaignName CONTAINS "INSERT CAMPAIGN NAME HERE" ' +
'DURING 20180101,20181231');

// export to sheet
report.exportToSheet(sheetName.getActiveSheet());

// sort by first column, which is date
sheetName.sort(1);

// print log for confirmation
Logger.log("Report name : " + sheetName.getName());
Logger.log("Report URL : " + sheetName.getUrl());

}

The Script (ad groups)

// Copyright 2019, Silver Mouse Sdn. Bhd., All Rights Reserved.

/**
* @name Google Ads Script – Auto Report
*
* @overview This script exports a report for this individual account.
* For more information, visit developers.google.com/ads/scripts
*
* @author Ong Hock Seng, Digital Marketing Manager at Silver Mouse [[email protected]]
*
* @version 1.0.3b
*
* @changelog
* – version 1.0
* – Released initial version
* – version 1.0.1
* – Minor spring cleaning
* – version 1.0.2
* – Lean and clean
* – version 1.0.2a
* – updated for 2018
* – version 1.0.2b
* – adapted for ad group reporting, refer to https://developers.google.com/adwords/api/docs/appendix/reports/adgroup-performance-report
* – version 1.0.3b
* – updated for 2019
*
* @usage Insert spreadsheet URL in line 43 & campaign name in line 56.
* Remove line 56 to select all campaigns.
*
*/

function main() {

// set to select this account, shortened to make it easier to write fetches
var account = AdsApp.currentAccount();

// set to get campaign info
var campaignIterator = AdsApp.campaigns()
.get();

// link to sheet url
var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/INSERT SPREADSHEET URL HERE/edit';

// shorten
var sheetName = SpreadsheetApp.openByUrl(SPREADSHEET_URL);

// print log for reference
Logger.log('Account name : ' + account.getName());
Logger.log('Currency code : ' + account.getCurrencyCode());
Logger.log('—');

// select info
var report = AdsApp.report(
'SELECT CampaignName, AdGroupName, Date, Cost ' +
'FROM ADGROUP_PERFORMANCE_REPORT ' +
'WHERE CampaignName CONTAINS "INSERT CAMPAIGN NAME HERE" ' +
'DURING 20190101,20191231');

// export to sheet
report.exportToSheet(sheetName.getActiveSheet());

// sort by first column, which is date
sheetName.sort(1);

// print log for confirmation
Logger.log("Report name : " + sheetName.getName());
Logger.log("Report URL : " + sheetName.getUrl());

}]

[// Copyright 2019, Silver Mouse Sdn Bhd, All Rights Reserved.

/**
* @name Google Ads Script – Auto Stop Campaign
*
* @overview This script stops a campaign when the allocated budget has been exceeded
* For more information, visit developers.google.com/ads/scripts
*
* @author Ong Hock Seng, Digital Marketing Manager at Silver Mouse [[email protected]]
*
* @version 1.1
*
* @changelog
* – version 1.0
* – Released initial version
* – version 1.1
* – Updated for 2019
*
* @usage Select the campaign name in line 30 and insert campaign budget in line 46.
* set script schedule to run hourly.
*/

function main() {

// set to select this account, shortened to make it easier to write fetches
var account = AdsApp.currentAccount();

// select the specific campaign here
var campaignIterator = AdsApp.campaigns()
.withCondition('Name = "INSERT CAMPAIGN NAME HERE"')
.get();

// this section will begin running if there is a campaign that matches the campaign name you keyed in
if (campaignIterator.hasNext()) {

// fetch campaign that we have selected
var campaign = campaignIterator.next();

// fetch campaign stats for all time
var stats = campaign.getStatsFor("ALL_TIME");

// get cost
var campaignCost = stats.getCost();

// set your budget here
var totalBudget = 1234;

// pause campaign if cost is above set budget
var campaignDiff = totalBudget – campaignCost;
var campaignDiffCheck = campaignDiff < 0;
if (campaignDiff < 0) {campaign.pause()};

Logger.log('—');
Logger.log('Current cost : ' + campaignCost);
Logger.log('Total budget : ' + totalBudget);
Logger.log('Budget difference : ' + campaignDiff);
Logger.log('Difference is lower than 0 : ' + campaignDiffCheck);
Logger.log('Campaign status enabled : ' + campaign.isEnabled());
Logger.log('—');

} else {

Logger.log('—');
Logger.log('Hmm it seems like there are no campaigns selected..');
Logger.log('—');

}
}]