SSJS are hidden gems of marketing cloud. In our last blog, we have seen how to check the automation status via SSJS. In this blog we will see how we can integrate Salesforce Marketing cloud with google sheet.
The idea here is to display Salesforce Marketing Cloud Journey email tracking directly in Google Sheets in real time!
Create a Cloud Page with code resource as JavaScript and add the below SSJS code (view code snippet):
There are 2 parameters in URL:
tskey - Triggered send external key. For all the journey emails we create, SFMC internally creates a triggered email. Give the external key for journey triggered email.
metric - keep the value as 'all'
Sample URL - http://example.com/test?tskey=1234&metric=all
This will return a JSON in the web page.
Now we will import the JSON into Google sheets
Create a google sheet.
Go to Extensions--> Script editor
Delete the placeholder content and paste the code from this script.
Rename the script to ImportJSON.gs and click the save button
Back in the spreadsheet, in a cell, you can type as "=ImportJSON("http://example.com/test?tskey=1234&metric=all", "", "Headers")"
Or if you don't need header, you can type as "=ImportJSON("http://example.com/test?tskey=1234&metric=all","","noInherit,noTruncate,noHeaders")"
Below is the screenshot of how the google sheet looks like:
Please feel free to leave your feedback/ comment in case of any queries.
Happy learning!