Salesforce factMap compiling

[David_Sola]

I don’t think there are many solutions in here about how to break down the factMap from the Salesforce analytics/reports API. In truth, this took me several weeks to figure out, but I figured I’d share it here anyway, in case anyone else comes across a similar problem or has a better solution.

Issue: The Salesforce connector within make (apparently) does not have the ability to extract entire reports from Salesforce. The report data comes out when using the “include details” parameter in the URL during the API call, but it arrives in a very strange (and deeply nested) combination of arrays and collections that is a bit difficult to extract. Here is how I was able to get it done:

Step 1: (assuming your salesforce connection is already active) Make a GET API call to get the report data. Place the report ID in the greyed out space (make sure to include “?includeDetails=true” after the report ID. Verify that you have the Content-Type set to application/json.

Step 2: Set Variable to flatten and map the factMap Rows to dataCells 3 levels deep.

Step 3: Set Variable (again) to map the dataCell labels

Step 4: Take the new “label” variable and transform to JSON

Step 5: Use the text parser to check for a pattern via REGEX and use the following pattern:
(DISCLAIMER: the pattern identified here via the REGEX will need to be modified to match the number of columns that you have. This pattern splits the input JSON string by searching for the 29th instance of double quotations, followed by a comma in order to create the new bundles required for the parsing to properly execute. I used a sample from my data (2 bundles worth of the string) to test the separation on https://regex101.com.
The regex pattern is as such: ((?:[^““){29}[^”]”)([^,]*),

Step 6: Create a Table from the bundles using the table aggregator with the settings in the screenshot below.

Step 7: Use the text aggregator to prepare the string for input into the Google Sheets API.
(Pay attention to the additional comma after the text string from the previous module)

Step 8: Use the google sheets API to input the data from the previous module. Be sure to make a PUT API call and set the header key to: Content-Type and value to: application/json and Query String key to: valueInputOption and the value to: USER_ENTERED. You also must make sure that your body is formatted as seen in the screenshot: (values in first brackets should reflect your column headers followed by a comma (on the outside of the first bracket) and the mapped text string from the previous module enclosed within the second closed bracket.

Sorry if this seems really complex, it just took a long time for me to figure it out and I hadn’t seen a tutorial of it anywhere. I hope this helps someone else out!

Cheers!

[Michaela]

Hello @David_Sola

Thanks so much for jumping into the community and sharing this detailed guide with us! I genuinely appreciate the time you took to put this into words. Thanks for keeping the community in mind and teaching us what you learned. This is incredibly valuable

[David_Sola]

Hi @Michaela ! I’m glad to contribute (when I can). Truth be told, this community has so much potential to help each other through these problems and the solutions that I’ve read so far are very encouraging when I try to figure out my own! Hoping to show some more tricks and techniques I’ve learned the past few months. Had zero exposure to make, JSON, REGEX, or any of this stuff prior to subscribing to Make back in March!

[Michaela]

Heyyy @David_Sola

Thanks so much for your excitement to contribute! We all 100% appreciate the value you bring to the community through your unique perspective and experiences. It’s fantastic to have members like you who recognize the potential we have to support and assist one another.

By the way, your progress in areas like JSON, REGEX, etc. is truly inspirational! Keep up the fantastic work!

[Bill_Hodghead]

Thanks @David_Sola for the conversion! I followed these instructions and got it to work, but some things required changes or additions:

1. You must have permission to edit the Google sheet.

2. The URL for the Google Sheets API is
   spreadsheets/YOURSHEETID/values/Sheet1!A1:Z1000:append
   Your range and Sheet name may vary, of course

3. The Google Sheet method is now POST. PUT doesn’t work anymore

4. The Regular expression in step 5 is incorrect in the text but correct in the picture. It’s:
   ((?:[^““){39}[^”]”)([^,]*),
   You will have to modify the number for your columns as mentioned in the post.