Sunday, October 20, 2013

Use GDrive To Load A Single AdWords Script Into Multiple Accounts

One of the downsides to using AdWords Scripts is that you need to log into each account and set up the script. For most people, that isn't a problem the first few times. But as you start seeing the value of some of these scripts, there might be a set of them that you want to put in all your accounts. Setting them up is fine, until you find a bug in your code and have to go in and update all 20 copies of the script in each account.

Well in this post, I put together a simple way to keep a single copy of your script and load it into any number of AdWords accounts. Then, if you ever have a change to make, you can update a single version of the script and all the accounts will instantly start using the new code.

The code for this consists of two parts. The first snippet of code is the generic code that you need to place in each one of your accounts. This code references a single Google Spreadsheet (here is a sample one for you to copy: http://goo.gl/y6hPfy) that it uses to know what scripts it is supposed to run. The spreadsheet has only 3 columns: A description which is just used for logging, the location of the script in Google Drive, and the Object Name in the script. Don't worry about that right now, I will describe it better in the next section. Finally, it loads the script file and executes the main function.

/************************************
 * Generic Script Runner
 * Version 1.0
 * Created By: Russ Savage
 * FreeAdWordsScripts.com
 ***********************************/
function main() {
  //See http://goo.gl/KvINmD for an example spreadsheet.
  var scriptConfigId = 'Your Spreadsheet Id Goes Here';
  var sheet = SpreadsheetApp.openById(scriptConfigId).getActiveSheet();
  var data = sheet.getRange('A:C').getValues();
  for(var i in data) {
    if(i == 0) { continue; }
    var [description, location, classname] = data[i];
    if(!location) { continue; }
    Logger.log('Running "'+description+'" from location: '+location);
    var scriptFile = getFile(location);
    var scriptText = scriptFile.getBlob().getDataAsString();
    eval(scriptText);
    var script = eval('new '+classname+'();');
    script.main();
  }
}
 
//This function gets the file from GDrive
function getFile(loc) {
  var locArray = loc.split('/');
  var folder = getFolder(loc);
  if(folder.getFilesByName(locArray[locArray.length-1]).hasNext()) {
    return folder.getFilesByName(locArray[locArray.length-1]).next();
  } else {
    return null;
  }
}
 
//This function finds the folder for the file and creates folders if needed
function getFolder(folderPath) {
  var folder = DriveApp.getRootFolder();
  if(folderPath) {
    var pathArray = folderPath.split('/');
    for(var i in pathArray) {
      if(i == pathArray.length - 1) { break; }
      var folderName = pathArray[i];
      if(folder.getFoldersByName(folderName).hasNext()) {
        folder = folder.getFoldersByName(folderName).next();
      }
    }
  }
  return folder;
}

Now that we have a generic piece of code that reads the description, location, and object name from a spreadsheet and executes code, we need to make some slight modifications to some of our existing scripts to get it to work.

One of my favorite scripts is the one about Finding Anomalies in Your Account. In order for the script to be run from another script, we need to convert it into an object with a single public function. This same technique should work on almost all of the scripts from FreeAdWordsScripts.com.

First, surround the entire script in a function call and give it whatever name you like.
function Anomalies() {
 // Copy and Paste the code from:
 // http://goo.gl/IT1UcV
};
Next, you need to update the main function to be a public method so that we can call it from our generic script.
this.main = function() {
 // Don't make any changes to the body of the main method
}

A full version of the updated code can be found here: Find Anomalies in Your Account Object Version.

Now you can save this new script somewhere in your GDrive and update the Location and Object Name (Anomalies in this case) in your config spreadsheet.

Now you should be good to go. You can add as many scripts as you like to the config spreadsheet but keep in mind that the 30 minute limit still applies.

Thanks,
Russ