If you’re using a CRM like Pipedrive, Hubspot, or Copper, you’re certainly used to working with various types of strategies for your prospects and handling multiple tasks for different kinds of companies and people.
Then, you should also know the struggle of tasks being forgotten about, or simply losing track of all you’ve done throughout the week.
This gets even more complex if you are managing a team of SDRs. In each weekly meeting, you’d like to see what your SDR has gotten done in a given week, what they’ve missed, or you simply wanna have a look at all the notes and calls your team members have logged.
Putting all those metrics together can actually take quite a bit—some CRM systems don’t even provide you with this information as part of their UI.
Luckily, using dashdash, I found a way to get all this info into a spreadsheet! I’ll also show you some pretty handy functions that dashdash provides—UNIXTIME for example and more.
Let’s start building!
In this example, I will be working with Copper (formerly Prosperworks), the CRM system we are using.
However, you should be ready to do the same with pretty much any CRM after reading this post.
First of all, make sure to have a look at the API documentation of your CRM. You can find Copper’s API docs here.
For our use cases at CrossEngage, we are mainly using the
Copper provides a search endpoint for both activities and tasks which is very helpful for a variety of use cases.
Build the task dashboard.
In order to retrieve a list of tasks, we have to send a request to this URL:
As we can see, we have to send a
POST request which always consists of three elements:
First, the Action URL. That’s the one above.
Second, the Header (needed for Authorization).
Looking at Copper’s API docs, they say:
The Dev API uses a token based authentication. You have to include the token in the header of every request, along with the email address of the user who generated the token.
I’ve already explained how to build the Header for Copper in a separate post. Take a look here: Connecting Dashdash with Copper and Built With and Crunchbase - #2 by nadjabenes.
As a quick refresher, this is what the Header looks like:
I created the Header in
A4 using the
Third, the Body.
The Body is needed to specify which information (parameters) you want to retrieve/filter by when sending the API call.
Copper allows you to filter by various parameters.
I will use the task’s assignee, status, and due date here.
This is the first little hiccup you encounter when working with pretty much any CRM API:
The assignee has to be an ID, not an actual name.
The due date has to be submitted as a Unix timestamp, not as YYYY-MM-DD.
How can we deal with that so that you’re still able to search for tasks like in the example below:
You can retrieve a list of all users with name and ID by calling this endpoint:
Using this endpoint, you can create a list consisting of the user name and the corresponding ID.
I created a separate Help View in dashdash including the names and IDs.
Using the VLOOKUP function
=IFERROR(VLOOKUP(B5,'Help'!$G$5:$H$18,2.0,FALSE),"") in the cell next to the name field, I am fetching the ID for every name.
Copper needs you to send the timestamp as a Unix timestamp.
The Unix timestamp is a way to track time as a running total of seconds. This count starts at the Unix Epoch on January 1st, 1970 at UTC. Therefore, the Unix time stamp is merely the number of seconds between a particular date and the Unix Epoch.
Luckily, you don’t need to do any complicated calculations here!
Dashdash offers the UNIXTIME function to convert a given date into a Unix timestamp.
So I used
=UNIXTIME(B8) to convert the lower and upper boundary of the due date range into Unix time.
Once you have done this, building the actual body is pretty simple. It should look like so:
Make sure to use the square brackets for the
assignee_ids fields, as these are arrays (fields that can store information of multiple objects).
Followed so far? Great!
Your POST request might look like this:
=IF(A9=“Get Tasks”,POST(A1,‘Overview Activities’!$A$4,B1),“Click Get Tasks”)
I have a button in cell A9, the URL in A1, my header in a different view called Overview Activities, and the body in B1.
Now let’s build the actual activities view.
It the end, this is what you’ll get:
The Task ID, Name, and the Entity (lead, company, or opportunity) is information you get in the response from the POST request we performed above.
You can either retrieve this data by using the
OVERWRITE_DATA function as follows:
which would take every element of the “id” field and parse it to B11 and the cells below, or directly parse the data using the
PARSE function like so:
which would use an index created in column A to parse each item, in this case, the “type”.
Since only looking at the task name, id, and entity type aren’t very helpful, we also want to display the name and company name a task is related to.
The problem here is that Copper won’t give you this information in the response when listing the tasks. You will only get the entity type, like person or opportunity, and the corresponding ID.
In order to get the additional information we need, we have to call three different endpoints, depending on the entity type.
We have to combine multiple IF functions that will check for the entity type and send a GET request to the right endpoint using the specific ID in the URL.
I won’t display the entire formula here as it’s pretty long, but the general syntax should look like this:
This is actually a very handy way to combine a
GET (or any request) with a
Now, displaying information like the company name is super simple. Just parse the field you need:
If you just want to create an overview of all tasks, filtered by different criteria, you’re done already! However, I added one more feature to my app—allowing
users to add details to a task or change the due date. This is very valuable, especially during your weekly meetings.
You simply send the following request:
In the body of your PUT request, you specify which part of the task you want to update.
In my case, it’s the details and the due date.
Here’s my body:
As shown previously, we are using the UNIXTIME function to convert a YYYY-MM-DD format into a Unix timestamp.
The last step is to create a button and set up the API call:
=IF(J12="Update Task",PUT("https://api.prosperworks.com/developer_api/v1/tasks/"&B12&"",'Overview Activities'!$A$4,L12),"Klick Update Task")
You should be ready now to build your very own sales activities dashboard!
Want to try it out yourself?
You can find the app here. Just put in your Copper credentials and you’re ready to go.
Never lose track of you SDRs activities again