Question

Send Google Sheets Data To Mixpanel Using App Script

  • 14 November 2020
  • 5 replies
  • 174 views

Badge +3

I am trying to get events and users data in to Mixpanel using API:

https://developer.mixpanel.com/reference/events#track-event

 

I have created a Google App Script that is supposed to be sending data to Mixpanel although it shows 200 Status but it is not sending data to Mixpanel.

 

@cherise Could you help me connecting with someone from Mixpanel as this seems a bit tech and could be related to issues with the API.

 

Here is my Script in a more generalised format so this could help other users as well who are trying to get events data from Google Sheets to Mixpanel
 

function sendDataToMixpanel() {
  
  var SheetId = "Add Sheet Id Here";
  var SheetName = "Add Sheet Name Here";
  var FiletName = "Add File Name Here";
  var devToken = "Add Dev Token Here";
  
  var sheet = SpreadsheetApp.openById(SheetId).getSheetByName(SheetName);
  
  var range = sheet.getDataRange();
  var values = range.getValues();
  
  var date = new Date();
  var nonce = Math.floor((date.getTime()/1000)).toString();

  for (var i = 1; i < values.length+1 ; i++) {

    var userId = "Add User ID Here";
    var FirstNameValue = "Add FirstNameValue Here";
    var LastNameValue = "Add LastNameValue Here";
    var PhoneValue = "Add PhoneValue Here";
    var EmailValue = "Add EmailValue Here";
    
    var headers = {
    "Accept": "application/json",
    "Content-Type": "application/json"
    }
    
    var ident = {
      "data": {
        "event": "$identify", //$identify for identify or eventName for others
        
        "properties":           {
          "token": devToken,
          "$identified_id": userId,
          "$anon_id": userId,
        }
      }
    }
    
    var set = {
      "data": {
        "$token": devToken,
        "$distinct_id": userId,
        "$set": {
          "$first_name": FirstNameValue,
          "$last_name": LastNameValue,
          "$phone": PhoneValue,
          "Import Sheet Name": SheetName,
          "Import File Name": FileName,
        }
      }
    }
    
    var set_once = {
      "data": {
        "$token": devToken,
        "$distinct_id": userId,
        "$set_once": {
          "First Import Date": nounce
        }
      }
    }
    
    var add = {
      "data": {
        "$token": devToken,
        "$distinct_id": userId,
        "$add": {
          "Imported Properties": 1
        }
      }
    }
    
    
    var event = {
      "data":{
        "event": "Test",
        "properties":           {
          "token": devToken,
          "time": nonce,          
          "distinct_id": userId,
          "$first_name": FirstNameValue,
          "$last_name": LastNameValue,
          "$phone": PhoneValue,
          "$email": EmailValue,
          "Import Date": nounce,
          "Import Sheet Name": SheetName,
          "Import File Name": FileName,
        }
      }
    }

    var identOptions = {
      method: "POST",
      headers: headers,
      body: ident
    };
    
    var setOptions = {
      method: "POST",
      headers: headers,
      body: set
    };
    
    
    var setOnceOptions = {
      method: "POST",
      headers: headers,
      body: set_once
    }
    
    
    var addOptions = {
      method: "POST",
      headers: headers,
      body: add
    }
    
    var eventOptions = {
      method: "POST",
      headers: headers,
      body: event
    }
    
    var identResponse = UrlFetchApp.fetch("https://api.mixpanel.com/track#create-identity",identOptions);
    Logger.log(identResponse.getResponseCode());
    

    var setResponse = UrlFetchApp.fetch("https://api.mixpanel.com/engage#profile-set",setOptions);
    Logger.log(setResponse.getResponseCode());
    
    var setOnceResponse = UrlFetchApp.fetch("https://api.mixpanel.com/engage#profile-set-once",setOnceOptions);
    Logger.log(setOnceResponse.getResponseCode());
    
    var addResponse = UrlFetchApp.fetch("https://api.mixpanel.com/engage#profile-numerical-add",addOptions);
    Logger.log(addResponse.getResponseCode());

    var eventResponse = UrlFetchApp.fetch("https://api.mixpanel.com/track#live-event",eventOptions);
    Logger.log(eventResponse.getResponseCode());
  }
  
}


5 replies

Badge +3

@cherise Could you find some help for me here :)

 

Thanks in advance!

Userlevel 6
Badge +3

@Affan —

I haven’t seen events sent in this way, but I reached out to our support team to see if they can chime in with their thoughts. 

Can you double check that your security credentials are correct? I believe that for security reasons we will return a 200, even if the security credentials are incorrect because we do not want to expose credentials. 

My advice here would be to break down your script to the most simple request first. For example, track just a single event first without worrying about the ID. Once you finish that, you can build on it with confidence — such as adding ID management, or making a user profile.

Userlevel 6
Badge +3

@Affan — This is still waiting some eyes from Support, but I wanted to check in here to see if my suggestions above helped narrow down the issue? Let me know!

Badge +3

@cherise I am still working on the integration and any help from the team would be really helpful.

 

What I am doing here is really helpful for a lot of Mixpanel users as most mixpanel user would like to send events from a spreadsheet to Mixpanel or they would like to tag users import with an event for users journey or for any other reason and this is the solution that they could use.

 

Any tech support from Mixpanel might be able to assist me in resolving this issue!

Userlevel 3
Badge +3

Hello @Affan ,

To be honest it is always hard to debug a code like this without going in the details.

Did you try to launch it on postman to be sure about your formatting?

To debug I would delete everything and try to send one event to see it that works. Then, add the rest of the code. 

Another solution, less scalable would be to use Zapier with Google Sheet X Mixpanel but that can consume all zapier quota.

Reply


Mixpanel