How to turn a spreadsheet into a Salesforce App

Tell me if any of these statements apply to you and your company.
“Oh we just have soooo many spreadsheets.”
“We have Salesforce but our sales management loves their spreadsheets.”
“Management told me its just easier to update a spreadsheet, so I feel stuck”

Ok, I could go on and on but you get the point. Here you are as a Salesforce Admin trying to make your company successful but getting your team off spreadsheets is posing a problem. It’s time to target those spreadsheets and turn them into Salesforce apps! And rather than waxing poetic, let’s actually get our hands dirty and build an app.

Let’s start with an Excel sheet

For me a good app to start with is one that builds on the functionality of Sales Cloud. One common practice in sales is to do strategic account planning, which is figuring out from a sales perspective which big new customers or existing ones you would like to sell your goods or services to. In this post I will take an excel spreadsheet- that I have seen companies use for Strategic Planning and turn it into a Salesforce app. And to help you follow along I’m going to give you a couple options. You can  download the sample Excel file here.

Let me first give you an overview of what the company wanted. They needed to do Strategic Account Planning. So they tracked Accounts, Contacts- as they related to the accounts, Strategic Plans- which had to be associated to an Account, and Action Plans- which needed to be associated to a Strategic Plan. At the heart of this app was an Account, with an Account created the user should be able to create a Strategic plan, that plan should have action items- which were different than the native activity object in Salesforce for visibility reasons. Before we dive into the spreadsheet, let me touch on why I used a custom object for Action Plans instead of Activities. In this case, the business really needed automation attached to the Action Items- reminders, email alerts, etc. Since Workflow Rules won’t fire on Activities this was the easiest way to go. In addition, while doing the requirements gathering it became apparent that Action Plans are way more involved than just a simple Activity.

This is an example of the spreadsheet they sent me. So let’s take a moment and breakdown the spreadsheet so we can understand how it can become an app. If you choose to download the example spreadsheet I’ve made line 1 the name of the column and line 2 a description of what the eventual Salesforce field type.

The Red Arrow

Let’s start with the red arrow at the bottom. As you know those are the Worksheets in an Excel Workbook. Conveniently, they also can be Objects in Salesforce. Remember that objects store data records that are alike. So we can see that we need the following objects: Accounts, Contacts, Strategic Plans, and Action Plans. Now we don’t yet know the relationship to each other- but that doesn’t matter yet. From using Salesforce we know that two objects are already built for us- Accounts and Contacts. Which is great because we can leverage these native objects to integrate them into our application.

The Blue Arrow

These are simple- each line on the Excel sheet is a data record. For the Accounts tab we see a list of Account Records, Contacts tab we see a list of Contact Records, and so on. I won’t spend too much time here. But I will point out a couple things. First, be sure to question the data that is in each field. Look especially for fields containing vast amounts of data. What you want to be careful is that you don’t create a 255 character field when they really need a 1,000 character text field. Oh, sure they may tell you “We only put in a little information in that field”. But don’t be lulled into complacency- they really like to dump stuff in that field- so be sure to give them the space they need.

The Green Arrow

Last on our list are the columns. Columns can most easily be thought of as ‘Fields’ so for every column you know you will need to create a field. The type of field will become apparent as you work through the Excel sheet. The key question to ask is “Can the subsequent records on each Worksheet exist without the prior?” For example- can an “Action Plan”- which is what we need to do to execute the “Strategic Plan” exist without a strategic plan? The answer would be no. So we know that Action Plans will be Master-detail to Strategic plans. And then you should ask “Can a Strategic Plan exist without an Account?” That answer, in this case, would be no as well. So we know that Strategic Plans will be Master-detail to Accounts. If they answered yes, then you know the relationship could be a lookup relationship.

Let’s build the app!

We know from the Excel sheet we have 2 custom objects to build because Accounts and Contacts already exist. Because Strategic Plans controls Actions plans, let’s start there. This is our first Custom Object and they are at the mercy of an Account. Now we get to create a Master-Detail field which looks to Accounts for its master. This way, when an Account is deleted- which could happen- the subsequent Strategic Plans are deleted as well. Because Action Plans follow the same mantra- if a Strategic Plan is deleted so much an Action Plan go to the way side. We will have a Master-Detail to Strategic Plans.

Here is a basic look at what your SchemaBuilder for this app would look like:

Now I skipped over the details of creating every object and subsequent fields, but the idea here was to help you make sense of a spreadsheet and turn it into a Salesforce App. Subsequent questions you will want to ask stakeholders would include visibility around these custom objects not only in terms of page layouts but in terms of reporting as well. And let’s not forget reporting- what do they want to see?

So there it is, in a few thousand characters we have the makings for a nice Strategic Planning app. Again, I didn’t walk you through everything but wanted to lay the foundation to get you started when another department hands you a spreadsheet and says “Make this.” My app looks a little like this-

Now, if you really want to get a head start on a strategic planning application here is a link to a few on the AppExchange that will turbo boost your process.

• Project and Issue Management – Force.com Labs
• TaskRay Project Management – Bracket Labs
• Project Portfolio Management – Force.com Labs
• Milestones PM – Force.com Labs