Salesforce and Quickbooks Integration
I recently started a business and the premise is that we can integrate everything and automate everything. This is in fact the goal and the driving force behind the business. So to start off, why not take on a project that requires integrating an application with the two monoliths, Quickbooks and Salesforce.
The client wants a customer portal that will show quickbooks data and salesforce data based on the customer that is signed in.
I played around with two potential ideas
1. Download all the client data and seed a database that we will control and maintain. A daemon (or similar) will be used to poll Quickbooks and Salesforce for updates to the database.
2. Make an api call to quickbooks and salesforce with the user data that we store in a database when the user logs in. The results of the query will then be stored in a database as JSON with the account name as the key and a timestamp, sort of like an extended cache system. Then, after a certain amount of time passes, the past query is determined expired and a new query will be made when the user logs in.
We investigated both of these options and spoke more with the client about their expectations and what we found was that
quickbooks api cannot work without getting an application certified and registered with them
there will not be a lot of traffic or a lot of updates to the data in Salesforce or Quickbooks and
The client has the expectation of a tight turn around (under 2 weeks)
In the end, the decision was made to go with version 2.
There were a lot of hurdles, here is a rundown on how we made it work with the following technologies:
Google App Engine (more on this here)
Python
Flask
Firebase (more on this here)
AWS S3
Excel Spreadsheets
Salesforce API + SOQL
Python and Flask are used to manage the backend of the service. My previous post discusses the use of AJAX and jQuery to interact with the backend. After a user is successfully authenticated using firebase (blog post here), the backend has information about the user’s email, name and their token. This is stored in a claim object, but the claim object does not have information about the user’s salesforce id, quickbooks id, region etc. because this is information the user does not know and will not sign up with. In this case we had to make the connection between the two.
So the first hurdle is managing the users data needed for quickbooks and salesforce. In this case we created a google datastore model of the user’s information and will use the user’s email to query the information.
# [START User]
class User_ext(ndb.Model):
# NDB model class for a user, which contains their information
sf_id = ndb.StringProperty()
qb_company_name = ndb.StringProperty()
qb_emails = ndb.StringProperty()
account_owner = ndb.StringProperty()
account_email = ndb.StringProperty()
region = ndb.StringProperty()
# [END User]
now when a user logs in we can get their sf_id and the qb_company_name which we will use for getting their information. This information can be easily added or updated using an admin backend like flask-admin. or programmatically added by seeding the database.
Now that we have the user information we will make our queries to salesforce and quickbooks.
Salesforce ended up being the easier of the two. Using a python module called simple-salesforce I could easily access the salesforce instance and make salesforce queries. There are some particulars with SOQL that make it more difficult to use than a vanilla SQL. I am not an expert here, but with the developer tools from salesforce and some nice walkthroughs online, it is fairly easy to get what you want back. I ended up querying for the opportunity line items for a particular client using their salesforce id. Here is a very straightforward walkthrough of getting started with simple-salesforce from Salesforce themselves.
Once the data was pulled from salesforce it was stored into an object that would be sent back to the front end with the other user information.
here is an example code snippet of a query:
sf = Salesforce(username='username', password='password', security_token='this-is-my-token')
#first query for opportunity line items by account id
op_id_payload = sf.query_all("SELECT id, account.name from opportunity where account.id = "+ "'" + customer + "'")['records']
# get list of opportunity ids
op_ids = []
for op_id in op_id_payload:
op_ids.append(op_id['Id'])
# another query for the product information based on opId
data = []
for opId in op_ids:
op_line_item = sf.query_all("SELECT Id, Name, (Select Id, Quantity, TotalPrice, PricebookEntry.Product2.ProductCode, PricebookEntry.Product2.Name From OpportunityLineItems) From Opportunity Where Id = " + "'" +opId+"'")
Quickbooks quickly became another story... After playing around with a few different python modules and understanding how to query from quickbooks, it became apparent that we couldn’t actually go this route because releasing a production app for quickbooks would take too long and too many resources. We had to find another way.
The solution - have the client upload 2 reports generated from quickbooks every week or so. The reports would be parsed based on the customer information and it would essentially act like a datastore. the question became, what was the easiest and fastest way to allow our users to upload the required documents and how should they be parsed because there was a lot of mismatching information between inventory on hand, company name and products on invoices. A very robust method was needed to match this information.
S3 to the rescue. using s3 and flask admin, we could create an admin page that allowed the users to upload the documents to an s3 bucket. This is easy to set up with
Based on the workflow we decided upon, when a user logs in, the request will then be made first to the database, and if nothing exists or the record is too old, it will get the information from the latest uploaded files (transactions and inventory) with the correct name in the s3 bucket.
To do this, our flask backend requests the s3 resource and then will read the excel document . Below is an example of reading an excel file with xlrd and urllib from an s3 bucket:
def read_excel(filename, header_row = 0, start_row = 1, start_col = 0):
opener = urllib.URLopener()
myurl = "https://s3-<region>.amazonaws.com/<bucket-name>/" + filename
myfile = opener.open(myurl).read() # Open the workbook
xl_workbook = xlrd.open_workbook(file_contents = myfile)
# List sheet names, and pull a sheet by name
sheet_names = xl_workbook.sheet_names() sheet = xl_workbook.sheet_by_name(sheet_names[0])
# Pull the headers using the provided inputs by index
headers = [sheet.cell(header_row, col_index).value for col_index in xrange(sheet.ncols)]
# make a dictionary with the headers
for row_index in xrange(start_row, sheet.nrows):
d = {headers[col_index]: sheet.cell(row_index, col_index).value
for col_index in xrange(sheet.ncols)}
dict_list.append(d) return(dict_list)
Pandas, numpy and google app engine. I usually use pandas and numpy to manipulate data in csv or excel formats. Its quick and easy and saves a lot of time. Unfortunately google app engine only allows numpy==1.6.1, which does not play well with pandas, which requires numpy=>1.7.1. So this meant I had to do it the long way, but what is probably the better method with less dependencies. Part of this is in the example listed above.
The next hurdle was matching the names. There would be one variation of the company name from our database of customers and we had many variations of the name in the transactions spreadsheet as well as the inventory spreadsheet. I used a combination of regex, fuzzy string matching (thank you
), tuning and unambiguous name portion matching to create a ranking algorithm of whether names were a “match” or not. The matches are then returned as objects which are stored in the database of records with a timestamp and then returned to the front end with other user information.
After working through the various hurdles, the conclusion is that the quickbooks API is a forest and very difficult to find your way through and their method of sandboxing the API does not allow for very easy integrations for small businesses. Although there are some integrations available on Zapier etc. they do not have a search functionality and are very limited. Salesforce’s API is much easier to work with and their developer tools allows for a much easier integration.