Naveen VM

  • Naveen VM

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!

Create a Cloud Page with code resource as JavaScript and add the below SSJS code:

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.

Happy learning!

#sfmc #googlesheet #ssjs #cloudpage