Excel to CRM

We recently started a project with a client who had some very complex spreadsheets. They wanted the data from a complex spreadsheet to reside in CRM but the algorithms and formatting frequently changed. Essentially they wanted us to build a tool that was able to keep this data in CRM but was flexible enough to handle significant changes.

We got the idea to create an excel add-in that took in the appropriate data from the sheet and to write that data to CRM. Our initial thought was to create a CRM entity that contained the schema for a particular sheet. One particular fields' data was held in Cell X123 and so on. However, when I started the project it became clear that this might become a bit of a nightmare to maintain so instead we came up with the idea to use user defined functions (UDF's).

We wrote a series of UDF's that would store each piece of information for an entity and when triggered, write them all to CRM. The greatest part is that its completely dynamic. You only need to enter the name of the field, the entity and the value and the data is written into a temporary data structure. For example, the main UDF takes the value as its first argument, the field name as its second and the entity as its third. The function uses the Metadata service to verify that the field is a valid field and then stores the value. When the user presses the Write to CRM button, all of the data stored in the data structure is written directly to CRM. Since many users are familiar with excel and excel functions, it makes it an easy way for an end-user to import complex data into CRM as well as gives them the ability to create their own customizable interface.