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!
