Script Adding Rows instead of updating

[Ryan Gallup]

I am converting this script from creating a new spreadsheet every time when ran to updating a specific sheet each time. So far it is referencing the correct sheet and doing things as planned in that manner, the only problem is that it adds new rows on the bottom instead of updating the rows that are already there, this throws off all of the totals and calculation cells. The code is below and I will highlight the section I believe needs tweaked. Please let me know if you need any more information.

Thank you in advance for your help!




var SPREADSHEET_URL = 'GoogledocURL'

function main() {
  var eMail = "noemail";
  var monthlyBudget = "10000.00";
  var accountName = AdWordsApp.currentAccount().getName();
  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = spreadsheet.getActiveSheet();
  function daysInMonth(month, year) {
    return new Date(year, month, 0).getDate();
  }
 
  var cell = sheet.getRange("A:K");
  cell.setFontFamily("Lato");
  cell.setFontSize("10");
  var cell = sheet.getRange("B:GK");
  cell.setHorizontalAlignment("center");
  sheet.getRange("A2").setValue(
    "Monthly Budget Projections");
  sheet.getRange("A2:K2").setBackground("#34495E");
  sheet.getRange("A2:K2").setFontColor("White");
  sheet.getRange("A2:K2").setFontSize("14");

  var today = new Date();
  var thisMonth = today.getMonth();
  var thisYear = today.getFullYear();
  var totalDays = daysInMonth(thisMonth + 1, thisYear);
  var daysSoFar = today.getDate() - 1;
  today.getDate();
  Utilities.formatDate(today, "PST", "yyyyMMdd");
  var range = sheet.getRange("A1:A1").setValue(today);
  range.setFontStyle("italic");
 
  var range = sheet.getRange("E2:E2").setValue(accountName);
  range.setFontColor("White");
  range.setFontSize("14");
 
  sheet.getRange("A3").setValue("Budget");
  sheet.getRange("A4").setValue("Today's Date:");
  sheet.getRange("B4").setValue(today);
  sheet.getRange("A5").setValue("End of Month:");
  var endDate = new Date(thisYear, thisMonth, totalDays);
  sheet.getRange("B5").setValue(endDate);
  sheet.getRange("A6").setValue("Days Left:");
  var daysLeft = (totalDays - daysSoFar);
  sheet.getRange("B6").setValue(daysLeft);
  sheet.getRange("A7").setValue("7 Day Cost:");// 7 Day Cost
  sheet.getRange(7, 2).setFormula("=sum(F:F)");
 
  sheet.getRange("A8").setValue("MTD Cost:"); // Month to Date Cost
  sheet.getRange(8, 2).setFormula("=sum(G:G)");
  sheet.getRange("B10").setValue("Projected");
  sheet.getRange("B10:C10").setBackground("#34495E");
  sheet.getRange("B10:C10").setFontColor("White");
  sheet.getRange("B10:C10").setFontSize("10");
 
  sheet.getRange("A11").setValue("Cost:"); //Projected Cost
  sheet.getRange(11, 2).setFormula("=sum(H:H)"); //Projected Cost
 
  sheet.getRange("A12").setValue("Conversions:"); //Projected Conversions
  sheet.getRange(12, 2).setFormula("=sum(J:J)"); //Projected Conversions
  sheet.getRange("A13").setValue("Cost Per Lead:"); //Projected cost per lead
  sheet.getRange(13, 2).setFormula("=ROUND(B11/B12)"); // Projected Conv. / Projected Cost
  sheet.getRange("A15").setValue("% of Budget Spent:");
  sheet.getRange(15, 2).setFormula("=sum((B8/B3)*100)"); // (MDT Cost / Budget)*100
  sheet.getRange("A16").setValue("Amount Over Budget:");
  sheet.getRange(16, 2).setFormula("B11-B3"); // Projected Cost - Budget
  sheet.getRange("A17").setValue("Daily Cut Needed:");
  sheet.getRange(17, 2).setFormula("=ROUND(B16/B6)"); // Amount Over Budget / Days Left
 
  sheet.getRange("C10").setValue("Last Month");
  sheet.getRange("C11").setValue("=sum(I:I)");
  sheet.getRange("C12").setValue("=sum(K:K)");
  sheet.getRange("C13").setValue("=Round(C11/C12)");
   
 
  // Add spreadsheet column categories
  sheet.getRange("A19").setValue("Campaign");
  sheet.getRange("B19").setValue("Projected Clicks");
  sheet.getRange("C19").setValue("Clicks Last Month");
  sheet.getRange("D19").setValue("Projected Impr.");
  sheet.getRange("E19").setValue("Impr. Last Month");
  sheet.getRange("F19").setValue("Seven Day Cost");
  sheet.getRange("G19").setValue("Month To Date Cost");
  sheet.getRange("H19").setValue("Projected Cost");
  sheet.getRange("I19").setValue("Cost Last Month");
  sheet.getRange("J19").setValue("Projected Conv.");
  sheet.getRange("K19").setValue("Conv. Last Month");
  // Set formatting for column categories
  var range = sheet.getRange("A19:K19");
  range.setFontColor("White");
  range.setBackground("#7492af");
  range.setFontSize("10");
  sheet.setColumnWidth(1, 160); // Account
  sheet.setColumnWidth(2, 110); // Projected Clicks
  sheet.setColumnWidth(3, 110); // Clicks Last Month
  sheet.setColumnWidth(4, 110); // Projected Impressions
  sheet.setColumnWidth(5, 110); // Impressions Last Month
  sheet.setColumnWidth(6, 110); // Seven Day Cost
  sheet.setColumnWidth(7, 110); // Month To Date Cost
  sheet.setColumnWidth(8, 110); // Projected Cost
  sheet.setColumnWidth(9, 110); // Cost Last Month
  sheet.setColumnWidth(10, 110); // Conversions
  sheet.setColumnWidth(11, 110); // Conversions Last Month
  //format date
  var today = new Date();
  var date = new Date();
  var lastDay = new Date(date.getFullYear(), date.getMonth() + 1, 0);
  var daysLeft = days_between(lastDay, today);
  getAllCampaigns();
  var subject = "Monthly Account Budget Projections " + accountName;
  var body = "Your monthly projections have been posted to: " +
    spreadsheet.getUrl();
  MailApp.sendEmail(eMail, subject, body);
  function getAllCampaigns() {
    var campaignIterator = AdWordsApp.campaigns().get();
    while (campaignIterator.hasNext()) {
      var campaign = campaignIterator.next();
      var campaignName = campaign.getName() ? campaign.getName() :
        '--';
      var statsSevenDays = campaign.getStatsFor("LAST_7_DAYS");
      var statsMonth = campaign.getStatsFor("THIS_MONTH");
      var statsLastMonth = campaign.getStatsFor("LAST_MONTH");
            
     
      var lmtdTotal = {};
      lmtdTotal['last month clicks'] = 0;
      lmtdTotal['last month cost'] = 0;
      lmtdTotal['last month impressions'] = 0;
      lmtdTotal['last month conversions'] = 0;
     
      var mtdTotal = {};
      mtdTotal['clicks'] = 0;
      mtdTotal['cost'] = 0;
      mtdTotal['impressions'] = 0;
      mtdTotal['conversions'] = 0;
      var sevenDayTotal = {};
      sevenDayTotal['clicks'] = 0;
      sevenDayTotal['cost'] = 0;
      sevenDayTotal['impressions'] = 0;
      sevenDayTotal['conversions'] = 0;
      var projectedTotal = {};
      projectedTotal['clicks'] = 0;
      projectedTotal['cost'] = 0;
      projectedTotal['impressions'] = 0;
      projectedTotal['conversions'] = 0;
  lmtdTotal['last month clicks'] += statsLastMonth.getClicks();
      lmtdTotal['last month impressions'] += statsLastMonth.getImpressions();
      lmtdTotal['last month cost'] += statsLastMonth.getCost();
      lmtdTotal['last month conversions'] += statsLastMonth.getConversions();
      mtdTotal['clicks'] += statsMonth.getClicks();
      mtdTotal['impressions'] += statsMonth.getImpressions();
      mtdTotal['cost'] += statsMonth.getCost();
      mtdTotal['conversions'] += statsMonth.getConversions();
      sevenDayTotal['clicks'] += statsSevenDays.getClicks();
      sevenDayTotal['impressions'] += statsSevenDays.getImpressions();
      sevenDayTotal['cost'] += statsSevenDays.getCost();
      sevenDayTotal['conversions'] += statsSevenDays.getConversions();
      projectedTotal['clicks'] = Math.round(makeProjections(
        mtdTotal['clicks'],
        sevenDayTotal['clicks'], daysLeft));
      projectedTotal['impressions'] = Math.round(makeProjections(
        mtdTotal['impressions'],
        sevenDayTotal['impressions'], daysLeft));
      projectedTotal['cost'] = makeProjections(mtdTotal['cost'],
        sevenDayTotal['cost'],
        daysLeft).toFixed(2);
      projectedTotal['conversions'] = Math.round(makeProjections(
        mtdTotal['conversions'],
        sevenDayTotal['conversions'], daysLeft));

      var row = [
        campaignName,
        projectedTotal['clicks'],
        lmtdTotal['last month clicks'],
        projectedTotal['impressions'],
        lmtdTotal['last month impressions'],
        sevenDayTotal['cost'],
        mtdTotal['cost'],
        projectedTotal['cost'],
        lmtdTotal['last month cost'],
        projectedTotal['conversions'],
        lmtdTotal['last month conversions']
      ];
      sheet.appendRow(row);
    }
  }
  function days_between(date1, date2) { 
    var ONE_DAY = 1000 * 60 * 60 * 24;
    var date1_ms = date1.getTime();
    var date2_ms = date2.getTime();
    var difference_ms = Math.abs(date1_ms - date2_ms);
    return Math.round(difference_ms / ONE_DAY);
  }
  function makeProjections(month, lastWeek, time) {
    var total = month + ((lastWeek / 7) * time);
    return total;
  }
}

[Anash Oommen]

Hi Ryan,

You need to calculate the row you want to overwrite. Then retrieve the cell range using the getRange method and then use the setValues() method to overwrite the existing values. Something like this:

var range = sheet.getRange(row, col, 11, 1);
range.setValues[row];

Cheers,
Anash P. Oommen,
AdWords Scripts Team.

[Ryan Gallup]

Thanks Anash,

I believe I am on the right track with what you are saying however am getting an error. Right now I have the range below and am getting the error of: "values" is not defined. It is coming up with this error no matter what I am putting in the section of range.setValues["XXX"];

As I believe it, I am calling from cell A20 to cell K40 with the (20,1,20,11) which would be enough for 20 campaigns.


      var range = sheet.getRange(20, 1, 20, 11);
range.setValues[values];

        campaignName,
        projectedTotal['clicks'],
        lmtdTotal['last month clicks'],
        projectedTotal['impressions'],
        lmtdTotal['last month impressions'],
        sevenDayTotal['cost'],
        mtdTotal['cost'],
        projectedTotal['cost'],
        lmtdTotal['last month cost'],
        projectedTotal['conversions'],
        lmtdTotal['last month conversions']
      ;
      sheet.appendRow(row);
    }

Thank you again for your help!

[Ryan Gallup]

I ended up simply using a clear contents of the selected range earlier on the script and it seems to be working well. Now I will be working on making this into a MCC script that will have a different spreadsheet (within one workbook) for each account selected and essentially do the same thing on each page daily. If you have any tips on the adding a sheet or anything like that I'd love to hear them, if not I'll continue on this sometime next week probably.