Monday, July 28, 2014

Save a File or Spreadsheet in a Specific Folder of GDrive

Here is a quick code snippet to help you understand how to save a spreadsheet into a specific folder on Google drive.

In order to create a file in a folder, you will need to find the parent folder and pass it to the DriveApp when you create the file. Normally, file paths are in the form of "/path/to/file/file.js", so it makes sense to keep that same construct when saving files to GDrive. The following code accepts the full file path and creates a new file in that folder.

The next obvious question is how to create a spreadsheet. Unfortunately, you can't create it using the DriveApp, you need to use the SpreadsheetApp. Also, since the spreadsheet app cannot create files in a folder, you will need to create the spreadsheet, then move it to the correct folder. I usually create a separate function with similar logic to find the correct folder, then update the create logic to use SpreadsheetApp instead of DriveApp. See the second function for an example.

Thanks,
Russ

/******************************************
* Create File in a Specific Folder Path
* Version 1.0 
* Created By: Russ Savage
* FreeAdWordsScripts.com
******************************************/
function createFile(filePath) {
  var pathArray = filePath.trim().split('/');
  var folder;
  for(var i in pathArray) {
    var parentName = pathArray[i];
    if(!parentName || parentName === '') { continue; } // in the root folder
    // source: http://goo.gl/P0LQ86
    if(filePath.indexOf(parentName, filePath.length - parentName.length) !== -1) {
      // This is creating the actual file
      if(folder) {
        if(folder.getFilesByName(parentName).hasNext()) {
          Logger.log('Using existing file: '+parentName);
          return folder.getFilesByName(parentName).next();
        }
        return folder.createFile(parentName,'');
      } else {
        if(DriveApp.getRootFolder().getFilesByName(parentName).hasNext()) {
          Logger.log('Using existing file: '+parentName);
          return DriveApp.getRootFolder().getFilesByName(parentName).next();
        }
        return DriveApp.getRootFolder().createFile(parentName,'');
      }
    }
    if(!folder && !DriveApp.getRootFolder().getFoldersByName(parentName).hasNext()) {
      Logger.log('Creating folder: '+parentName);
      folder = DriveApp.getRootFolder().createFolder(parentName);
    } else if(folder && !folder.getFoldersByName(parentName).hasNext()) {
      Logger.log('Creating folder: '+parentName);
      folder = folder.createFolder(parentName);
    } else {
      Logger.log('Using existing folder: '+parentName);
      folder = (!folder) ? DriveApp.getRootFolder().getFoldersByName(parentName).next() 
                         : folder.getFoldersByName(parentName).next();
    }
  }
  // Should never get here
  throw "Invalid file path: "+filePath;
}
And to create a spreadsheet:
/******************************************
* Create Spreadsheet in a Specific Folder Path
* Version 1.0 
* Created By: Russ Savage
* FreeAdWordsScripts.com
******************************************/
function createSpreadsheet(filePath) {
  var pathArray = filePath.trim().split('/');
  var folder;
  for(var i in pathArray) {
    var parentName = pathArray[i];
    if(!parentName || parentName === '') { continue; } // in the root folder
    // source: http://goo.gl/P0LQ86
    if(filePath.indexOf(parentName, filePath.length - parentName.length) !== -1) {
      // This is creating the actual file
      if(folder) {
        if(folder.getFilesByName(parentName).hasNext()) {
          Logger.log('Using existing file: '+parentName);
          var file = folder.getFilesByName(parentName).next();
          return SpreadsheetApp.openById(file.getId());
        }
        // creates in folder
        var spreadsheet = SpreadsheetApp.create(parentName);
        var file = DriveApp.getRootFolder().getFilesByName(parentName).next();
        folder.addFile(file);
        DriveApp.getRootFolder().removeFile(file);
        return spreadsheet;
      } else {
        if(DriveApp.getRootFolder().getFilesByName(parentName).hasNext()) {
          Logger.log('Using existing file: '+parentName);
          var file = DriveApp.getRootFolder().getFilesByName(parentName).next();
          return SpreadsheetApp.openById(file.getId());
        }
        // creates in root folder
        return SpreadsheetApp.create(parentName);
      }
    }
    if(!folder && !DriveApp.getRootFolder().getFoldersByName(parentName).hasNext()) {
      Logger.log('Creating folder: '+parentName);
      folder = DriveApp.getRootFolder().createFolder(parentName);
    } else if(folder && !folder.getFoldersByName(parentName).hasNext()) {
      Logger.log('Creating folder: '+parentName);
      folder = folder.createFolder(parentName);
    } else {
      Logger.log('Using existing folder: '+parentName);
      folder = (!folder) ? DriveApp.getRootFolder().getFoldersByName(parentName).next() 
                         : folder.getFoldersByName(parentName).next();
    }
  }
  // Should never get here
  throw "Invalid file path: "+filePath;
}

Sunday, July 27, 2014

Exception Invalid reporting query INVALID_PREDICATE_ENUM_VALUE ACTIVE

UPDATE: Here is a note from the AdWords Scripts team on the topic.

A few days ago, I started getting a bunch of errors from my scripts that looked like this:
Exception: Invalid reporting query: INVALID_PREDICATE_ENUM_VALUE: ACTIVE

If anyone else is seeing this issue, it has to do with an update to the reporting API. Previously, ACTIVE used to be a valid enum value for CampaignStatus, which was always sort of an anomaly since everything else used ENABLED. It has been updated to match the other Status values but in the process, broke any script that was using the ACTIVE status. Here is an example:
    ...
    var query = ['select',cols.join(','),'from',report,
                 'where CampaignStatus = ACTIVE',
                 'and AdGroupStatus = ENABLED',
                 'and Status = ACTIVE',
                 'during','TODAY'].join(' ');
    ...
This should be changed to:
    ...
    var query = ['select',cols.join(','),'from',report,
                 'where CampaignStatus = ENABLED',
                 'and AdGroupStatus = ENABLED',
                 'and Status = ENABLED',
                 'during','TODAY'].join(' ');
    ...

I am trying to go back and update my previous scripts but it might take some time.
Thanks,
Russ

Thursday, June 19, 2014

Calling All AdWords Scripts Developers

I was thinking of putting together a directory for any developers or companies out there that are currently writing Adwords Scripts for clients and would be interested in being contacted. I figured a picture or logo, name, short bio, location, and website would be a good start for this. If you are interested in being listed, please fill out this form and I will add you to the directory that is linked in the sidebar.

Thanks,
Russ

Monday, May 5, 2014

Connect Zoho CRM Data with AdWords Using Scripts

For anyone working in B2B Pay Per Click, one of the biggest headaches is trying to report on the entire sales flow within a single report. The biggest issue is that marketing PPC data lives in AdWords (clicks, impressions, MQLs) and the sales data lives in your CRM (Leads, Contacts, Opportunities, Etc.). So I started looking at ways to connect the two sources of data. I don't use a CRM, so I signed up for a free trial of Zoho CRM and started fiddling with their API. It turns out, they have a REST(ish) API that returns JSON objects, which is perfect for AdWords scripts.

I built the Class below to pull data out of Zoho. It has the ability to pull Leads, Contacts, Potentials and just about any other Zoho object you can think of directly from your CRM. I stopped at just being able to get data out since updating or deleting records seemed less useful for AdWords scripts.

Here is a quick reference guide to the Class. Define a new object with var zoho = new ZohoApi(YOUR_API_TOKEN); If you need help setting up your account for API access or generating a token, check out the Zoho Developer Docs. I generated some simple getters for each Zoho object. ZohoApi.get[Objects]() will pull in all of the particular object. So you can say zoho.getLeads() or zoho.getPotentials(). You can also get only those objects belonging to you with zoho.getMyLeads().

If you have any additional url parameters you want to send over with the request, you can add them as a parameter to the function. For example, if you wanted to return the first 100 records (instead of the default 20), you would say zoho.getLeads({fromIndex:1,toIndex:100});

You can also search for records using zoho.search[Objects](). So to search for Potentials that have been won, you would say zoho.searchPotentials({'searchCondition':'(Stage|=|Closed Won)'}); You can read more about Zoho's searchCondition syntax in their API Docs. As part of that, you can put the columns you want to see or if you don't put anything in there, I pull the full set of columns to display for you using the get[Objects]Fields() method.

As for the response from the class, you will get an array of objects. Each key in the object has been lowercased with spaces replaced by underscores. For example, retVal[0].first_name or retVal[0].annual_revenue.

So give it a shot and let me know what you think in the comments. I put together a simple example script at the very bottom of this post to store Impressions, Clicks, Conversions, and Closed Won Potentials in a Google Doc on a daily basis to give you an idea of what you can do. Let me know what you would like to see next.

Thanks,
Russ

/******************************************
* Zoho CRM Get API Class
* Use it to pull data out of Zoho CRM
* Version 1.0 
* Created By: Russ Savage
* FreeAdWordsScripts.com
******************************************/
//For more info about the Zoho CRM API, see here:
// https://www.zoho.com/crm/help/api/
function ZohoApi(authToken) {
  var ZOHO_AUTH_TOKEN = authToken;
  var ZOHO_BASE_URL = 'https://crm.zoho.com/crm/private/json/';
  var METHODS = ['getMyRecords','getRecords','getRecordById','getCVRecords',
                 'getSearchRecords','getSearchRecordsByPDC','getRelatedRecords',
                 'getFields','getUsers','downloadFile','downloadPhoto'];
  var OBJECTS = ['Leads','Accounts','Contacts','Potentials',
                 'Campaigns','Cases','Soultions','Products',
                 'PriceBooks','Quotes','Invoices','SalesOrders',
                 'Vendors','PurchaseOrders','Events','Tasks','Calls'];
  
  for(var i in OBJECTS) {
    // Creating getPotentials() functions
    this['get'+OBJECTS[i]] = new Function('additionalParams',
      'return this.get("'+OBJECTS[i]+'","getRecords",additionalParams);');
    // Creating getMyPotentials() functions
    this['getMy'+OBJECTS[i]] = new Function('additionalParams',
      'return this.get("'+OBJECTS[i]+'","getMyRecords",additionalParams);');
    // Creating getPotentialsById(id) functions
    this['get'+OBJECTS[i]+'ById'] = new Function('id', 
      'return this.get("'+OBJECTS[i]+'","getRecordById",{ id : id });');
    // Creating searchPotentials(searchCondition) functions
    this['search'+OBJECTS[i]] = new Function('criteria', 
      'return this.get("'+OBJECTS[i]+'","getSearchRecords",criteria);');
    // Creating getPotentialsFields() functions
    this['get'+OBJECTS[i]+'Fields'] = new Function('return this.get("'+OBJECTS[i]+'","getFields",{});');
  }
  
  // You can use any Zoho OBJECT and METHOD and 
  // put any additional parameters as a map {param : val, param2: val2}
  this.get = function(zohoObj,zohoMethod,additionalParams) {
    validateParams(zohoObj,zohoMethod);
    additionalParams = addColumnsIfNeeded(this,zohoObj,zohoMethod,additionalParams);
    var url = buildUrl(zohoObj,zohoMethod,additionalParams);
    Logger.log(url);
    var resp = UrlFetchApp.fetch(url).getContentText();
    try {
      var jsonObj = JSON.parse(resp);
      if(jsonObj['response'] && jsonObj['response']['nodata']) {
        Logger.log('Code: '+jsonObj['response']['nodata']['code']+ 
               ' Message: '+ERRORS[jsonObj['response']['nodata']['code']]);
        return [];
      }
      if(jsonObj['response'] && jsonObj['response']['error']) {
        throw 'Code: '+jsonObj['response']['error']['code']+ 
          ' Message: '+jsonObj['response']['error']['message'];
      }
      
      if(jsonObj['response'] && jsonObj['response']['result']) {
        return parseResponseObject(zohoObj,jsonObj);
      }
      if(jsonObj[zohoObj] && jsonObj[zohoObj]['section']) {
        return parseFieldsObject(zohoObj,jsonObj);
      }
      return jsonObj;
    }catch(e){
      throw 'There was an issue parsing the response. '+e;
    }
  };
    
  function parseResponseObject(zohoObj,jsonObj) {
    if(jsonObj['response'] && jsonObj['response']['result']) {
      var rows = jsonObj['response']['result'][zohoObj]['row'];
      if(typeof rows[0] === 'undefined') {
        return [mapValToContent(rows)];
      } else {
        var retVal = [];
        for(var i in rows) {
          retVal.push(mapValToContent(rows[i]));
        }
        return retVal;
      }
    }
    return [];
  }
  
  function parseFieldsObject(zohoObj,jsonObj) {
    if(jsonObj[zohoObj] && jsonObj[zohoObj]['section']) {
      var fields = [];
      for(var i in jsonObj[zohoObj]['section']) {
        var elem = jsonObj[zohoObj]['section'][i];
        if(elem['FL'] && elem['FL'][0]) {
          for(var x in elem['FL']) {
            var field = elem['FL'][x];
            if(field['dv']) {
              fields.push(field['dv']);
            }
          }
        } else if(elem['FL'] && elem['FL']['dv']) {
          fields.push(elem['FL']['dv']);
        }
      }
      return fields;
    }
    return [];
  }
  
  function validateParams(zohoObj,zohoMethod) {
    if(!zohoObj || OBJECTS.indexOf(zohoObj) == -1) {
      throw 'Get must be called with a proper ZOHO object. Object given: "'+
        zohoObj+'" Available Objects:'+OBJECTS.join(',');
    }
    if(!zohoMethod || METHODS.indexOf(zohoMethod) == -1) {
      throw 'Get must be called with a proper ZOHO method. Method given: "'+
        zohoObj+'" Available Methods:'+METHODS.join(',');
    }
  }
  
  function addColumnsIfNeeded(self,zohoObj,zohoMethod,additionalParams) {
    var searchConditionRequired = ['getSearchRecords','getSearchRecordsByPDC'];
    if(searchConditionRequired.indexOf(zohoMethod) >= 0) {
      if(!additionalParams['selectColumns']) {
        additionalParams['selectColumns'] = zohoObj+'('+self['get'+zohoObj+'Fields']().join(',')+')';
      }
    }
    return additionalParams;
  }
  
  function buildUrl(zohoObj,zohoMethod,additionalParams) {
    var url = ZOHO_BASE_URL+zohoObj+'/'+zohoMethod+
      '?authtoken='+ZOHO_AUTH_TOKEN+'&scope=crmapi&newFormat=1';
    for(var key in additionalParams) {
      url += '&' + key + '=' + encodeURIComponent(additionalParams[key]);
    }
    return url;
  }
  
  function mapValToContent(obj) {
    var retVal = {};
    if(obj.FL) {
      for(var i in obj.FL) {
        var elem = obj.FL[i];
        var key = elem.val;
        var cleanKey = key.toLowerCase().replace(/ /g,'_');
        retVal[cleanKey] = elem.content;
      }
    }
    return retVal;
  }
  
  var ERRORS = {'4000':'Please use Authtoken, instead of API ticket and APIkey.',
                '4500':'Internal server error while processing this request',
                '4501':'API Key is inactive',
                '4502':'This module is not supported in your edition',
                '4401':'Mandatory field missing',
                '4600':'Incorrect API parameter or API parameter value. Also check the method '+
                       'name and/or spelling errors in the API url.',
                '4820':'API call cannot be completed as you have exceeded the "rate limit".',
                '4831':'Missing parameters error',
                '4832':'Text value given for an Integer field',
                '4834':'Invalid ticket. Also check if ticket has expired.',
                '4835':'XML parsing error',
                '4890':'Wrong API Key',
                '4487':'No permission to convert lead.',
                '4001':'No API permission',
                '401':'No module permission',
                '401.1':'No permission to create a record',
                '401.2':'No permission to edit a record',
                '401.3':'No permission to delete a record',
                '4101':'Zoho CRM disabled',
                '4102':'No CRM account',
                '4103':'No record available with the specified record ID.',
                '4422':'No records available in the module',
                '4420':'Wrong value for search parameter and/or search parameter value.',
                '4421':'Number of API calls exceeded',
                '4423':'Exceeded record search limit',
                '4807':'Exceeded file size limit',
                '4424':'Invalid File Type',
                '4809':'Exceeded storage space limit'};
  
}


And here is a really simple example of how you could combine conversion data from multiple sources into a single Google Spreadsheet report.
/******************************************
* Combine Conversion Data from AdWords and Zoho CRM
* Version 1.0 
* Created By: Russ Savage
* FreeAdWordsScripts.com
******************************************/
var ZOHO_AUTH_TOKEN = 'YOUR ZOHO AUTH TOKEN';
var SPREADSHEET_URL = 'THE URL OF AN EMPTY SPREADSHEET';

function main() {
  var acctStats = AdWordsApp.currentAccount().getStatsFor('YESTERDAY');
  var adWordsImps = parseFloat(acctStats.getImpressions());
  var adWordsClicks = parseFloat(acctStats.getClicks());
  var adWordsCtr = parseFloat(acctStats.getCtr());
  var adWordsConv = parseFloat(acctStats.getConversions());
  var crmConv = getCRMConversions().count;
  var totalConv = (crmConv + adWordsConv);
  var sheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL).getActiveSheet();
  if(!sheet.getActiveRange().getValue()) {
    sheet.appendRow(['Date','Account Name',
                     'Impressions','Clicks','Ctr',
                     'AdWords Conversions','Conv Rate',
                     'CRM Wins','Win %']);
  }
  var toAppend = [ 
    Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd'),
    AdWordsApp.currentAccount().getName(),
    adWordsImps,
    adWordsClicks,
    adWordsCtr,
    adWordsConv,
    (adWordsClicks === 0) ? 0 : Math.round((adWordsConv/adWordsClicks)*100)/100,
    crmConv,
    (adWordsConv === 0) ? 0 : Math.round((crmConv/adWordsConv)*100)/100];
  sheet.appendRow(toAppend);
}

function getCRMConversions(dateRange) {
  var zoho = new ZohoApi(ZOHO_AUTH_TOKEN);
  var closedWonCount = 0;
  var closedWonRevenue = 0;
  var yesterday = new Date();
  yesterday.setDate(yesterday.getDate()-1);
  var yesterdayStr = Utilities.formatDate(yesterday, AdWordsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd');
  var potentials = zoho.searchPotentials({'searchCondition':'(Closing Date|=|'+yesterdayStr+')'});
  for(var i in potentials) {
    var potential = potentials[i];
    if(potential.stage === 'Closed Won' && potential.campaign_source === 'AdWords') {
      closedWonCount++;
      closedWonRevenue += parseFloat(potential.amount);
    } 
  }
  return { count: closedWonCount, revenue: closedWonRevenue };
}

function ZohoApi(authToken) { throw 'Fill this code in from the blog post!'; }