Tuesday, September 30, 2014

Use Google Feed API to Convert RSS to JSON

Here is a quick tip for anyone sick of working with the xml in RSS feeds. You can use the Google Feed API in your scripts to automatically convert the XML in the RSS feed to JSON which makes working with them much easier. For example, there is an RSS Feed from the Consumer Product Safety Commission and it has all the government recalls in the United States. Using this simple trick, you could easily pull the fresh feed on a regular basis and parse out information about recalls to use in your account. You can also configure Google Alerts to generate RSS feeds based on specific keywords you are interested in. For example, here is an RSS feed for the keyword "smx".

Thanks,
Russ

/******************************************
* Use Google Feed API to convert RSS to json
* Version 1.0 
* Created By: Russ Savage
* FreeAdWordsScripts.com
******************************************/
// Usage: var jsonData = convertRssToJson('http://www.cpsc.gov/en/Newsroom/CPSC-RSS-Feed/Recalls-RSS/');
function convertRssToJson(rssUrl) {
  var FEED_API_URL = "https://ajax.googleapis.com/ajax/services/feed/load?v=1.0&q="
  var url = FEED_API_URL+encodeURIComponent(rssUrl);
  var resp = UrlFetchApp.fetch(url);
  if(resp.getResponseCode() == 200) {
    return JSON.parse(resp.getContentText());
  } else {
    throw "An error occured while trying to parse: "+rssUrl;
  }
}

Monday, September 29, 2014

Pull Salesforce Data into AdWords Using Scripts

After my post about importing Zoho CRM data into AdWords, I received a lot of comments about doing something similar for Salesforce. I finally had some time to build a simple class that allows you to query data and objects from your Salesforce instance and use it in your scripts.

First, we will need to set you up with some OAuth credentials, and for that, you need to set up a new connected app in Salesforce. It is in slightly different places in each version, but using the developer version, I was able to find it under Setup > Build > Create > Apps. From there, all the way at the bottom, you can see a section for Connected Apps.
Creating a new connected app

If for some reason you can't find it in your Salesforce instance, your admin may not have given you access to it. Hopefully, they can help you.

After clicking the new button, you will need to fill out a few required fields and then select the option to "Enable Oauth Settings." You will need to enter a callback url but we won't be using it so you can enter any url that starts with https. For "Scopes", I just said "Full Access" but you may have to talk to your Salesforce Admin about that one. We will only be reading from Salesforce so it shouldn't be an issue.
Enabling the OAuth Settings

That's all you need to fill out and you should have a new app created. The important thing here is the "Consumer Key" and the "Consumer Secret" that you will need for the script to connect to your Salesforce instance.
Salesforce Consumer and Secret Keys

The last thing you will need from your Salesforce instance is a security token. You may already have one in which case, you can skip this. But if not, you can reset it under My Settings > Personal > Reset my Security Token. It will email you a new token.
Reset your Security Token

Ok, now we are finally ready to get to the AdWords Scripts code. The following code will set up a new SalesforceAPI object and query the most recent Opportunities that were Close Won so that you can use that revenue in your AdWords account.

/******************************************
* Get Won Opportunity Revenue Amounts
* Version 1.0 
* Created By: Russ Savage
* FreeAdWordsScripts.com
******************************************/
function main() {
  var CONSUMER_KEY = "YOUR APP CONSUMER KEY";
  var CONSUMER_SECRET = "YOUR APP SECRET KEY";
  var USERNAME = "YOUR SALESFORCE USERNAME";
  var PASSWORD = "YOUR SALESFORCE PASSWORD";
  var SECURITY_TOKEN = "YOUR SECURITY TOKEN";
  
  // Create a new SalesforceAPI object
  var sf = new SalesforceAPI({
    client_id : CONSUMER_KEY,
    client_secret : CONSUMER_SECRET,
    username : USERNAME,
    password : PASSWORD,
    token: SECURITY_TOKEN
  });
  
  // Query the results
  var results = sf.query("SELECT Name \
                         , StageName \
                         , ExpectedRevenue \
                         FROM Opportunity \
                         WHERE IsWon = True \
                         AND CloseDate < THIS_WEEK");
  // Log the results
  for(var i in results) {
    Logger.log(['Name:',results[i].Name,
                'Stage Name:',results[i].StageName,
                'Expected Revenue:',results[i].ExpectedRevenue,
                'Url:',sf.getFullUrl(results[i].attributes.url)].join(' '));
  }
}

It's that simple. If you want to get all the information about a particular object after you query for it, you can use the function getObjectByUrl() and send it the url from the query results. To learn more about the query syntax, check out the Salesforce SOQL documentation.

There are a few caveats for this code. Every Salesforce installation is unique so there really is no way for me to really troubleshoot issues with your specific install. This code was tested on a fresh Salesforce for Developers account so your results may vary. You will probably have more luck contacting your Salesforce Admin than leaving a comment here. Also, you may notice that the code it using the least secure option to log into Salesforce. This code with your username and password will be accessible to all users of your AdWords account, so be careful. It might be better to create a special Salesforce user with very limited permissions for something like this.

If you think this is useful, come let me know at SMX East this week.

Thanks,
Russ

/******************************************
* Salesforce.com CRM API Class
* Use it to query data out of Salesforce CRM
* Version 1.0 
* Created By: Russ Savage
* FreeAdWordsScripts.com
******************************************/
function SalesforceAPI(configVars) {
  this.login = function(configVars) {
    var LOGIN_ENDPOINT = "https://login.salesforce.com/services/oauth2/token";
    var options = {
      muteHttpExceptions : false,
      method: "POST",
      payload: {
        grant_type : "password",
        client_id : configVars.client_id,
        client_secret : configVars.client_secret,
        username : configVars.username,
        password : configVars.password+configVars.token
      }
    };
    var resp = UrlFetchApp.fetch(LOGIN_ENDPOINT, options);
    if(resp.getResponseCode() == 200) {
      var jsonResp = JSON.parse(resp.getContentText());
      this.id = jsonResp.id;
      this.instanceUrl = jsonResp.instance_url;
      this.signature = jsonResp.signature;
      this.accessToken = jsonResp.access_token;
      Logger.log('Successfully logged in user with id: '+this.id);
    }
  }
  
  this.getServices = function() {
    if(this.serviceUrls) { return this.serviceUrls };
    var ENDPOINT_URL = this.instanceUrl+"/services/data/v26.0/.json";
    var options = getBasicOptions(this.accessToken);
    var resp = UrlFetchApp.fetch(ENDPOINT_URL, options);
    if(resp.getResponseCode() == 200) {
      var jsonResp = JSON.parse(resp.getContentText());
      this.serviceUrls = jsonResp;
      return this.serviceUrls;
    }
  }
  
  this.query = function(queryStr) {
    if(!this.serviceUrls.query) { throw "Query service is not enabled in this SF instance."; }
    var ENDPOINT_URL = this.instanceUrl+this.serviceUrls.query+'.json';
    var url = ENDPOINT_URL + '?q=' + encodeURIComponent(queryStr);
    var options = getBasicOptions(this.accessToken);
    var resp = UrlFetchApp.fetch(url, options);
    if(resp.getResponseCode() == 200) {
      var jsonResp = JSON.parse(resp.getContentText());
      if(jsonResp.done) {
        return jsonResp.records;
      } else {
        var retVal = jsonResp.records;
        while(!jsonResp.done) {
          resp = UrlFetchApp.fetch(jsonResp.nextRecordsUrl, options);
          if(resp.getResponseCode() == 200) {
            jsonResp = JSON.parse(resp.getContentText());
            retVal = retVal.concat(jsonResp.records);
          }
        }
        return retVal;
      }
    }
  }
  
  this.getObjectByUrl = function(url) {
    var url = this.instanceUrl + url + '.json';
    var options = getBasicOptions(this.accessToken);
    var resp = UrlFetchApp.fetch(url, options);
    Logger.log(resp.getContentText());
    if(resp.getResponseCode() == 200) {
      return JSON.parse(resp.getContentText());
    }
  }
  
  this.getFullUrl = function(url) {
    return this.instanceUrl + url;
  }
  
  this.login(configVars);
  this.getServices();
  
  
  function getBasicOptions(token) {
    return {
      muteHttpExceptions : false,
      method: 'GET',
      headers: {
        Authorization : "Bearer " + token
      }
    };
  }
}

Wednesday, September 17, 2014

Come Join Me at Marketing Festival 2014

I just wanted to take a second to mention that I will be speaking at Marketing Festival 2014 this year as well as running a workshop on more advanced techniques for scripting.  If you are able to attend, it should be a very productive conference and workshop.  I look forward to meeting you in person!

Thanks,
Russ

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;
}