Spreadsheet App to Track Users – Google Analytics

Step 1

Go To : https://spreadsheets.google.com

Step 2

Create a new spreadsheet and name it ‘ Event Registration ‘. (you can name anything)

Step 3

Go To Tools > Script Editor and paste the following code in code.gs:
In sendHit function, make sure to replace the value of propID (UA-XXXXXXXX-X) with your own property ID.
The current hit contains campaign parameters, you can comment it if you don’t want it.

/* Offline User Tracking - Spreadsheet App
 * Description: Send hits to analytics after verifying user.
 * Author:Ritwik B 
 * Digishuffle.com 
 */

function onOpen() {
   SpreadsheetApp.getUi().createMenu('Event Registered Users').addItem("Event ABC", 'showBar').addToUi()
        }


function showBar() {
  var html=HtmlService.createHtmlOutputFromFile('sidebar')
      .setTitle('Event ABC')
      .setWidth(300).setSandboxMode(HtmlService.SandboxMode.IFRAME);
  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
     .showSidebar(html);
}

function findCID(code)

{
var sheet=SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
var clientID=""


for (var i=1;i<sheet.getLastRow();i++)
{
if(code==sheet.getRange(i,3,1,1).getValue()&&sheet.getRange(i,4,1,1).getValue()=="")
{ 
clientID=sheet.getRange(i,2,1,1).getValue()
sendHit(clientID,code,i)
  
break;
}
  else if (code==sheet.getRange(i,3,1,1).getValue()&&sheet.getRange(i,4,1,1).getValue()=="Yes")
{
 clientID=sheet.getRange(i,2,1,1).getValue()
 SpreadsheetApp.getUi().alert("Unique Code already Verified!!").OK;
 break;
}
  
}
  
if(clientID=="") SpreadsheetApp.getUi().alert("Unique Code Not Found").OK
  
}


function sendHit(cid,code,rw)
{
  var propId='UA-XXXXXXXX-X'
  var sheet=SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var url = 'https://www.google-analytics.com/collect'
 
  // With Campaign Parameters
  var payload = { 'v' : '1' , 't' : 'event', 'tid' : propId, 'cid' : cid,
                 'ec' : 'Offline Event', 'ea' : 'Attended',
                 'cs': 'Event', 'cm' : 'Offline', 'cn' : 'Event ABC', 'ni' :'1' }

  /*
  // Without Campaign Parameters 
   var payload = { 'v' : '1' , 't' : 'event', 'tid' : propId, 'cid' : cid,
                  'ec' : 'Offline Event', 'ea' : 'Attended','ni' :'1' }
   
   */
   
  
  var url = UrlFetchApp.fetch(url, {'method':'POST','payload':payload})  
  sheet.getRange(rw,4,1,1).setValue("Yes")
  SpreadsheetApp.getUi().alert("Verification Success!! \n User code: "+code+"\n clientID: "+cid).OK;

  }

Now Create another file name ‘sidebar.html’ ( File > New > HTML File) and paste the following code:

<!DOCTYPE html>
<html>
<style>
#button
{margin-left:95px;}
#code
{margin-left:50px;}
#codeText
{margin-left:50px;
 font-size:20px }

</style>
  <head>
    <base target="_top">
 <link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
  <link rel="stylesheet" href="/resources/demos/style.css">
  <script src="https://code.jquery.com/jquery-1.12.4.js"></script>
  <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
  <script>
  $( function() {
    $( "#button" ).button();
    
  } );
  
  </script>
</head>
 
 <body>
<script>
function sendAnalyticsHit()
{
google.script.run.findCID(document.getElementById("code").value)
}

</script>

<h2> USER VERIFICATION</h2> <br />
 
 
 
  
<p id='codeText'>Enter Unique Code : </p><input type='text' id="code" > <br /><br />
 
  <input type='submit' id='button' value="Verify" onclick='sendAnalyticsHit()' > 
  <br />
  <div id='output'></div>
<br />
<br />
<br />
<p style='font-size:12px;text-align:center'> Digishuffle - User Tracking - Demo App </p>

 </body>
</html>

Step 4

Try refreshing the spreadsheet and the new drop down will appear named ‘Event Registered Users’ . Click on it and open Event ABC

Copy the Demo Excel file and paste it as shown in above image.

Step 5

Now enter the Unique Code and click verify. You can check the analytics real tie to see the event getting triggered.
You can verify the code only once to avoid sending duplicate hits to analytics.
Click Below gif to see the whole process

For more info, make sure you check : Tracking Offline Users in Real Time through Google Analytics

Ritwik B

Ritwik is a Web Analyst & Product Marketer. He loves to write technical & easy to understand blogs for Marketers & Entrepreneurs. Focused on Google Analytics, Facebook Analytics, Tag Management, Marketing & Automation Scripts & more. Google Certified Professional. A Firm Believer in Teaching -> Learning -> Growing. :)

Spreadsheet App To Track Offline Users in Google Analytics

Spreadsheet App to Track Users – Google Analytics

Ritwik B

Leave a Reply Cancel reply