Consolidate AdWords Script

Question

I have the below code in a script I am using for Google AdWords and it is working well however was hoping that it could be shortened or consolidated in some way easily. It is currently working however has nearly double the script run time and is not very easy for changes and expansion in the future (if needed). Please let me know how you would do this or if you need any other information!

//projections by Campaign row 22
sheet.getRange("E10").setValue("=A22");
sheet.getRange("E11").setValue("=H22"); 
sheet.getRange("E12").setValue("=J22"); 
sheet.getRange("E13").setValue("=Round(E11/E12)"); 
sheet.getRange("E14").setValue("=B22");
sheet.getRange("E15").setValue("=D22");
sheet.getRange("E17").setValue("=sum((G22/E3)*100)");
sheet.getRange("E18").setValue("=E11-E3");
sheet.getRange("E19").setValue("=ROUND(E18/B6)");

//projections by Campaign row 23
sheet.getRange("F10").setValue("=A23");
sheet.getRange("F11").setValue("=H23"); 
sheet.getRange("F12").setValue("=J23"); 
sheet.getRange("F13").setValue("=Round(F11/F12)"); 
sheet.getRange("F14").setValue("=B23");
sheet.getRange("F15").setValue("=D23");
sheet.getRange("F17").setValue("=sum((G23/F3)*100)");
sheet.getRange("F18").setValue("=F11-F3");
sheet.getRange("F19").setValue("=ROUND(F18/B6)");

//projections by Campaign row 24
sheet.getRange("G10").setValue("=A24");
sheet.getRange("G11").setValue("=H24"); 
sheet.getRange("G12").setValue("=J24"); 
sheet.getRange("G13").setValue("=Round(G11/G12)"); 
sheet.getRange("G14").setValue("=B24");
sheet.getRange("G15").setValue("=D24");
sheet.getRange("G17").setValue("=sum((G24/G3)*100)");
sheet.getRange("G18").setValue("=G11-G3");
sheet.getRange("G19").setValue("=ROUND(G18/B6)");

//projections by Campaign row 25
sheet.getRange("H10").setValue("=A25");
sheet.getRange("H11").setValue("=H25"); 
sheet.getRange("H12").setValue("=J25"); 
sheet.getRange("H13").setValue("=Round(H11/H12)"); 
sheet.getRange("H14").setValue("=B25");
sheet.getRange("H15").setValue("=D25");
sheet.getRange("H17").setValue("=sum((G25/H3)*100)");
sheet.getRange("H18").setValue("=H11-H3");
sheet.getRange("H19").setValue("=ROUND(H18/B6)");

//projections by Campaign row 26
sheet.getRange("I10").setValue("=A26");
sheet.getRange("I11").setValue("=H26"); 
sheet.getRange("I12").setValue("=J26"); 
sheet.getRange("I13").setValue("=Round(I11/I12)"); 
sheet.getRange("I14").setValue("=B26");
sheet.getRange("I15").setValue("=D26");
sheet.getRange("I17").setValue("=sum((G26/I3)*100)");
sheet.getRange("I18").setValue("=I11-I3");
sheet.getRange("I19").setValue("=ROUND(I18/B6)");

//projections by Campaign row 27
sheet.getRange("J10").setValue("=A27");
sheet.getRange("J11").setValue("=H27"); 
sheet.getRange("J12").setValue("=J27"); 
sheet.getRange("J13").setValue("=Round(J11/J12)"); 
sheet.getRange("J14").setValue("=B27");
sheet.getRange("J15").setValue("=D27");
sheet.getRange("J17").setValue("=sum((G27/J3)*100)");
sheet.getRange("J18").setValue("=J11-J3");
sheet.getRange("J19").setValue("=ROUND(J18/B6)");

//projections by Campaign row 28
sheet.getRange("K10").setValue("=A28");
sheet.getRange("K11").setValue("=H28"); 
sheet.getRange("K12").setValue("=J28"); 
sheet.getRange("K13").setValue("=Round(K11/K12)"); 
sheet.getRange("K14").setValue("=B28");
sheet.getRange("K15").setValue("=D28");
sheet.getRange("K17").setValue("=sum((G28/K3)*100)");
sheet.getRange("K18").setValue("=K11-K3");
sheet.getRange("K19").setValue("=ROUND(K18/B6)");

//projections by Campaign row 29
sheet.getRange("L10").setValue("=A29");
sheet.getRange("L11").setValue("=H29"); 
sheet.getRange("L12").setValue("=J29"); 
sheet.getRange("L13").setValue("=Round(L11/L12)"); 
sheet.getRange("L14").setValue("=B29");
sheet.getRange("L15").setValue("=D29");
sheet.getRange("L17").setValue("=sum((G29/L3)*100)");
sheet.getRange("L18").setValue("=L11-L3");
sheet.getRange("L19").setValue("=ROUND(L18/B6)");

//projections by Campaign row 30
sheet.getRange("M10").setValue("=A30");
sheet.getRange("M11").setValue("=H30"); 
sheet.getRange("M12").setValue("=J30"); 
sheet.getRange("M13").setValue("=Round(M11/M12)"); 
sheet.getRange("M14").setValue("=B30");
sheet.getRange("M15").setValue("=D30");
sheet.getRange("M17").setValue("=sum((G30/M3)*100)");
sheet.getRange("M18").setValue("=M11-M3");
sheet.getRange("M19").setValue("=ROUND(M18/B6)");

//projections by Campaign row 30
sheet.getRange("N10").setValue("=A31");
sheet.getRange("N11").setValue("=H31"); 
sheet.getRange("N12").setValue("=J31"); 
sheet.getRange("N13").setValue("=Round(N11/N12)"); 
sheet.getRange("N14").setValue("=B31");
sheet.getRange("N15").setValue("=D31");
sheet.getRange("N17").setValue("=sum((G31/N3)*100)");
sheet.getRange("N18").setValue("=N11-N3");
sheet.getRange("N19").setValue("=ROUND(N18/B6)");

I (extremely inexperienced in coding) have gone through and tried to have it place this in the first two columns as it does already and then drag to expand the formula patterns across the remaining columns desired however have not figured out how to do that. I've considered trying to set the ranges at the same time (E10:N10) equals to (A22:A31) however struggled with that as well. I'm really just not sure what way would even be best to approach it with (if either of those) — Ryan, Jan 20 '15 at 17:40

score 0 · Answer 1 · answered Feb 06 '15 at 10:02

You can use the following code to reduce the lines of code.

      var range = "E";
        while(range < "N"){
    var num =22;
        for(var i=10;i<20;i++){
            if(i==10){
        sheet.getRange(range+i).setValue("=A"+num);
        }
else if(i==11){
 sheet.getRange(range+i).setValue("=H"+num);
}
else if(i==12){
 sheet.getRange(range+i).setValue("=J"+num);
}
else if(i==13){
sheet.getRange(range+i).setValue("=Round(range+11/range+12)"); 
}
else if(i==14){
sheet.getRange(range+i).setValue("=B"+num);
}
else if(i==15){
sheet.getRange(range+i).setValue("=D"+num);
}
else if(i==17){
sheet.getRange(range+i).setValue("=sum(("+range+""+num+"/"+range+"3)*100)");
}
else if(i==18){
sheet.getRange(range+i).setValue("="+range+"11-"+range+"3");
}
else if(i==19){
sheet.getRange(range+i).setValue("=ROUND("+range+"18/"+range+"6)");
}
else{
continue;
}

        }
range = nextChar(range);
    }

    function nextChar(c){
      return String.fromCharCode(c.charCodeAt()+1);
    }

This should solve your problem. Alternatively, you can use switch().

score 0 · Accepted Answer · edited Mar 24 '15 at 03:27

It seems that you are using the wrong notation: you want to insert FORMULA and not VALUE so far. Check the Spreadsheet Class and Range Class docs.

Here is what you are looking for:

setFormula(formula): Updates the formula for this range.
setFormulaR1C1(formula): Updates the formula for this range.
setFormulas(formulas): Sets a rectangular grid of formulas (must match dimensions of this range).
setFormulasR1C1(formulas): Sets a rectangular grid of formulas (must match dimensions of this range).

So for example

sheet.getRange("E10").setValue("=A22");
sheet.getRange("E11").setValue("=H22"); 
sheet.getRange("E12").setValue("=J22"); 
sheet.getRange("E13").setValue("=Round(E11/E12)"); 
sheet.getRange("E14").setValue("=B22");
sheet.getRange("E15").setValue("=D22");
sheet.getRange("E17").setValue("=sum((G22/E3)*100)");
sheet.getRange("E18").setValue("=E11-E3");
sheet.getRange("E19").setValue("=ROUND(E18/B6)");

could become

var Formulas = [
    "=A22","=H22","=J22"
    "=Round(E11/E12)",
    "=B22","=D22",
    "=Sum((G22/E3)*100)",
    "=E11-E3",
    "=ROUND(E18/B6)"
    ]
sheet.getRange("E10:E19").setFormulasR1C1(Formulas);
//not sure about the A1 notation if that would accept this parameters else
//replace by getRange(10,5,19,5) (

Consolidate AdWords Script

2 Answers2