top of page

Integrate Google Sheets with Salesforce Marketing Cloud

Updated: Jan 26, 2022

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'


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!


2,068 views7 comments

7 Comments


Luis Panta
Luis Panta
May 05, 2023

Es genial, sabes si puedo enviar datos de una ED de Salesforce Marketing Cloud a Google Sheets?.. tengo unas ED que se alimentan de inscripciones que hacen mediante una landing, y necesito pasarlo de forma automatizada a un GSheets a otro equipo.

Like

Vinoth Kumar
Vinoth Kumar
May 17, 2022

Hello,

This is super cool!

With the current implementation, I see that I can pull only the Client Id, External key of the Journey, Name of the journey, and send stats.

Is it possible that we can also store the subscriber's information in the google sheet who has clicked the CTA button?


Like
Naveen VM
Naveen VM
May 17, 2022
Replying to

Thanks for your feedback Vinoth!

With this way you can only able to track at aggregated level but not at the individual / subscriber level.

Like

Là De Masi
Là De Masi
Sep 23, 2021

does anyone know if this is possible not only with triggered sends but with normal email send flow? I need to import monthly report in G Sheet via API.

Like
Naveen VM
Naveen VM
Sep 23, 2021
Replying to

Hi , Currently its only possible with Triggered sends.

Like

Daniel Gonzalez
Daniel Gonzalez
Mar 15, 2021

What about the other way round, from GSheet to Data Extension.

Like
Naveen VM
Naveen VM
May 17, 2022
Replying to

Hi Daniel, you can try using App script in Google sheet by passing parameters in Cloud pages URL and in the cloud page you can write the logic using Ampscript or SSJS where you can able to insert a record into data extension.

Like
bottom of page