Flexible Budgets reworked

[Mindaugas Kalnius]

Hello,

I am attempting to modify Flexible budgets script, which I've done in previous experience succesfully, to use campaign id instead of campaign name. I've ran into some issues from Oct 31 and I do not trust the previous script because I've already found some mistakes in the login, or at least I think I do but those mistakes are on my end. I thought it would be great time to update the script even though it was still running.

The script is as follows:

/**
 * Configuration to be used for the Flexible Budgets script.
 */
CONFIG = {
  // URL of the default spreadsheet template. This should be a copy of
  // https://docs.google.com/spreadsheets/d/17wocOgrLeRWF1Qi_BjEigCG0qVMebFHrbUS-Vk_kpLg/copy
  // Make sure the sheet is owned by or shared with same Google user executing the script
  'spreadsheet_url': 'https://docs.google.com/spreadsheets/d/1QCECU1fx1N2CddbR96h5h0s6jkOu8NF7jTFazGvCVz0/edit#gid=0',

  'advanced_options': {
    // Please fix the following variables if you need to reformat the
    // spreadsheet
    // column numbers of each config column. Column A in your spreadsheet has
    // column number of 1, B has number of 2, etc.
    'column': {
      'accountId': 2,
      'campaignId': 3,
      'startDate': 4,
      'endDate': 5,
      'totalBudget': 6,
      'results': 7
    },

    // Actual config (without header and margin) starts from this row
    'config_start_row': 3
  }
};

const SPREADSHEET_URL = CONFIG.spreadsheet_url;
const COLUMN = CONFIG.advanced_options.column;
const CONFIG_START_ROW = CONFIG.advanced_options.config_start_row;

function main() {
  // Uncomment the following function to test your budget strategy function
  testBudgetStrategy(calculateBudgetEvenly, 10, 500);
  //setNewBudget(calculateBudgetWeighted);
}

// Core logic for calculating and setting campaign daily budget
function setNewBudget(budgetFunc) {
  console.log(`Using spreadsheet - ${SPREADSHEET_URL}.`);
  const spreadsheet = validateAndGetSpreadsheet(SPREADSHEET_URL);
  spreadsheet.setSpreadsheetTimeZone(AdsApp.currentAccount().getTimeZone());
  const sheet = spreadsheet.getSheets()[0];

  const endRow = sheet.getLastRow();

  const mccAccount = AdsApp.currentAccount();
  //sheet.getRange(2, 6, 1, 2).setValue(mccAccount.getCustomerId());

  const today = new Date();

  for (let i = CONFIG_START_ROW; i <= endRow; i++) {
    console.log(`Processing row ${i}`);

    const accountId = sheet.getRange(i, COLUMN.accountId).getValue();
    const campaignId = sheet.getRange(i, COLUMN.campaignId).getValue();
    const startDate = new Date(sheet.getRange(i, COLUMN.startDate).getValue());
    const endDate = new Date(sheet.getRange(i, COLUMN.endDate).getValue());
    const totalBudget = sheet.getRange(i, COLUMN.totalBudget).getValue();
    const resultCell = sheet.getRange(i, COLUMN.results);

    const accountIter = AdsManagerApp.accounts().withIds([accountId]).get();
    if (!accountIter.hasNext()) {
      resultCell.setValue('Unknown account');
      continue;
    }
    const account = accountIter.next();
    AdsManagerApp.select(account);

    const campaign = getCampaign(campaignId);
    if (!campaign) {
      resultCell.setValue('Unknown campaign');
      continue;
    }

    if (today < startDate) {
      resultCell.setValue('Budget not started yet');
      continue;
    }
    if (today > endDate) {
      resultCell.setValue('Budget already finished');
      continue;
    }

    const costSoFar = campaign
                          .getStatsFor(
                              getDateStringInTimeZone('yyyyMMdd', startDate),
                              getDateStringInTimeZone('yyyyMMdd', endDate))
                          .getCost();
    const daysSoFar = datediff(startDate, today);
    const totalDays = datediff(startDate, endDate);
    const newBudget = budgetFunc(costSoFar, totalBudget, daysSoFar, totalDays);
    campaign.getBudget().setAmount(newBudget);
    console.log(
        `AccountId=${accountId}, CampaignId=${campaignId}, ` +
        `StartDate=${startDate}, EndDate=${endDate}, ` +
        `CostSoFar=${costSoFar}, DaysSoFar=${daysSoFar}, ` +
        `TotalDays=${totalDays}, NewBudget=${newBudget}'`);
    resultCell.setValue(`Set today's budget to ${newBudget}`);
  }

  // update "Last execution" timestamp
  //sheet.getRange(1, 3).setValue(today);
  //AdsManagerApp.select(mccAccount);
}

// One calculation logic that distributes remaining budget evenly
function calculateBudgetEvenly(costSoFar, totalBudget, daysSoFar, totalDays) {
  const daysRemaining = totalDays - daysSoFar;
  const budgetRemaining = totalBudget - costSoFar;
  if (daysRemaining <= 0) {
    return budgetRemaining;
  } else {
    return budgetRemaining / daysRemaining;
  }
}

// One calculation logic that distributes remaining budget in a weighted manner
function calculateBudgetWeighted(costSoFar, totalBudget, daysSoFar, totalDays) {
  const daysRemaining = totalDays - daysSoFar;
  const budgetRemaining = totalBudget - costSoFar;
  if (daysRemaining <= 0) {
    return budgetRemaining;
  } else {
    return budgetRemaining / (2 * daysRemaining - 1);
  }
}

// Test function to verify budget calculation logic
function testBudgetStrategy(budgetFunc, totalDays, totalBudget) {
  let daysSoFar = 0;
  let costSoFar = 0;
  while (daysSoFar <= totalDays + 2) {
    const newBudget = budgetFunc(costSoFar, totalBudget, daysSoFar, totalDays);
//    console.log(
//        `Day ${daysSoFar + 1} of ${totalDays}, ` +
//        `new budget ${newBudget}, cost so far ${costSoFar}`);
    costSoFar += newBudget;
    daysSoFar += 1;
  }
}

// Return number of days between two dates, rounded up to nearest whole day.
function datediff(from, to) {
  const millisPerDay = 1000 * 60 * 60 * 24;
  return Math.ceil((to - from) / millisPerDay);
}

// Produces a formatted string representing a given date in a given time zone.
function getDateStringInTimeZone(format, date, timeZone) {
  date = date || new Date();
  timeZone = timeZone || AdsApp.currentAccount().getTimeZone();
  return Utilities.formatDate(date, timeZone, format);
}

/**
 * Finds a campaign by name, whether it is a regular, video, or shopping
 * campaign, by trying all in sequence until it finds one.
 *
 * @param {string} campaignName The campaign name to find.
 * @return {Object} The campaign found, or null if none was found.
 */
function getCampaign(campaignId) {
  const selectors =
      [AdsApp.campaigns(), AdsApp.videoCampaigns(), AdsApp.shoppingCampaigns()];
  for (const selector of selectors) {
    const campaignIter =
        selector.withCondition(`CampaignId = "${campaignId}"`).get();
    if (campaignIter.hasNext()) {
      return campaignIter.next();
    }
  }
  return null;
}

/**
 * Validates the provided spreadsheet URL to make sure that it's set up
 * properly. Throws a descriptive error message if validation fails.
 *
 * @param {string} spreadsheeturl The URL of the spreadsheet to open.
 * @return {Spreadsheet} The spreadsheet object itself, fetched from the URL.
 * @throws {Error} If the spreadsheet URL hasn't been set
 */
function validateAndGetSpreadsheet(spreadsheeturl) {
  if (spreadsheeturl == 'YOUR_SPREADSHEET_URL') {
    throw new Error(
        'Please specify a valid Spreadsheet URL. You can find' +
        ' a link to a template in the associated guide for this script.');
  }
  return SpreadsheetApp.openByUrl(spreadsheeturl);
}

I have replaced all campaignName values with campaignId, which, previously did the trick, however, now it's simply not working, logger does not return any issues and I am lost here. Can anyone help me with this?

What I need:

Update the script code part, replace the campaignName with campaignId. 

As you may already have noticed, I did it on this script, however, it does not result to what I want. Thank you in advance.

[Mindaugas Kalnius]

I solved this myself.... again... I had commented out the main function setNewBudget....

[Google Ads Scripts Forum]

Reposting the last inquiry (https://groups.google.com/g/adwords-scripts/c/LMshA99tKEw) from the forum as it wasn't routed to our support queue. 

Cheers, 

James

Google Ads Scripts Team

[Google Ads Scripts Forum]

Reposting the last inquiry (https://groups.google.com/g/adwords-scripts/c/LMshA99tKEw) from the forum as it wasn't routed to our support queue.

Regards,
Yasmin
Google Ads Scripts Team

[Google Ads Scripts Forum Advisor]

Hi,

 

Thank you for reaching out to us. This is Yasmin from the Google Ads scripts team. Please excuse us for only getting back now as your message failed to be routed to our support queue.

 

I'm glad that you've got it working on your end and solved the issue. The solution scripts are quite complex to modify as changing one part of the code may cause the entire script to break. In the event that you'd need further assistance, please let us know and we'd be happy to lend a hand so we're able to investigate the nature of your use-case.

 

Regards,

 

Yasmin Gabrielle Google Ads Scripts Team

ref:_00D1U1174p._5004Q2gOsbk:ref