In my previous post I wrote a non-threatening introduction to customizing AdWords Scripts for those interested in editing scripts without having to rely on a programmer.
Here at Hanapin get loads of requests to edit and tweaks scripts. In this post I’ll show you how to make the 3 most requested scripts edits.
Formatting a spreadsheet is by far the easiest way to improve readability and convey information the way you want instead of being dictated to by the script writer.
1) Script Formatting
Using the getRange() allows you to set the starting point at which your script populates its data. By moving the starting point down, you can place metric totals or other information at the top of the spreadsheet so you don’t have to scroll thousands of rows down every time you want see the total for a metric.
getRange determines where you want your script to start populating data.
sheet.getRange(row, column, numRows, numColumns)
This is what it will look like in your script.
sheet.getRange(11, 1, 1, 11).setValues([header]);
The “11” tells the scripts you wants to start data population at row 11 or cell A11 in the script. Column and numRows can almost certainly remain “1” in most scripts. As for numColumns, this depends on how many metrics you have in the script. In this case we have 11 or range A11:K11. Changing the starting row from 1 to 11 provides us with enough room to show our columns totals at the top of the sheet.
You can also set sheet.getRange using commom spreadsheet ranges instead of numbers.
However, if you use this method you’ll have to set the range for every single cell within the spreadsheet.
There are other spreadsheet formatting options as well:
- Color: range.setFontColor(“White”);
- Font: cell.setFontFamily(“Lato”);
- Font Size: range.setFontSize(“14”);
- Cell Alignment: cell.setHorizontalAlignment(“center”);
2) Totaling Your Metrics
This tells the script you want to sum the H column and place it in B11. You can do this with any metric column.
sheet.getRange(11, 2).setFormula(“=sum(H:H)”); //Cost
Tip: If you don’t want to mess around with moving the starting point of your data population you can always choose a cell to the right of your data population.
3) Using Formulas In Scripts
sheet.getRange(15, 2).setFormula(“=sum((B8/B3)*100)”); // (MTD Cost / Budget)*100
Using setFormula() in the Monthly Budget Projections script allows me to easily find my percentage of budget spent.
Disclaimer: This is by no means an exhaustive list of things you can change within AdWords Scripts or uses exact coding terminology, it’s simply meant to be an introduction for those interested in tweaking scripts without having to rely on a programmer.