Integrate Google Sheets with Salesforce Marketing Cloud
Updated: Oct 15, 2019
SSJS are hidden gem of marketing cloud. In our last blog, we have see how to check the automation status via SSJS. In this blog we will how we can integrate Salesforce Marketing cloud with google sheet.
The idea here is use to display Salesforce Marketing Cloud Journey email tracking directly in Google Sheets in real time!
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 web page.
Now we will import the JSON into Google sheets
Create a google sheet.
Go to Tools --> 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")"
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 questions or comments.