Question

Send Google Sheets Data To Mixpanel Using App Script

  • 14 November 2020
  • 2 replies
  • 64 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());
  }
  
}


2 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.

Reply