RSS Feed Social Share Counter for Google Spreadsheets http://mashe.hawksey.info/2012/06/rss-feed-social-share-counting/
// This code excluding Google(c) Code tab: | |
/* | |
Copyright 2011 Martin Hawksey | |
Licensed under the Apache License, Version 2.0 (the "License"); | |
you may not use this file except in compliance with the License. | |
You may obtain a copy of the License at | |
http://www.apache.org/licenses/LICENSE-2.0 | |
Unless required by applicable law or agreed to in writing, software | |
distributed under the License is distributed on an "AS IS" BASIS, | |
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. | |
See the License for the specific language governing permissions and | |
limitations under the License. | |
*/ | |
function authenticateGoogleAnalytics(){ | |
ScriptProperties.setProperty('haveGAoAuth',true); | |
authorizeGA(); | |
} | |
function onOpen() { | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
var menuEntries = []; | |
menuEntries.push({name: "Google Analytics Configuration", functionName: "renderGAConfigurationDialog"}); | |
ss.addMenu("Social Share Count", menuEntries); | |
} | |
function getSharedCount(sourceLink){ | |
//var url = "http://mashe.hawksey.info/2012/02/oer-visualisation-project-fin-day-40-5/" | |
var url = extractLink(sourceLink); | |
var cache = CacheService.getPublicCache(); // using Cache service to prevent too many urlfetch | |
var cached = cache.get("C"+url); | |
if (cached != null) { // if value in cache return it | |
//var test = cached.split(",") | |
return cached.split(","); | |
} | |
try { | |
var options = | |
{ | |
"method" : "get", | |
"contentType" : "application/json" | |
}; | |
var response = UrlFetchApp.fetch("http://api.sharedcount.com/?url="+encodeURI(url), options); | |
var data = Utilities.jsonParse(response.getContentText()); | |
var output = []; | |
for (i in data){ | |
if (i == "Facebook"){ | |
output.push(data[i].total_count) | |
} else { | |
output.push(data[i]); | |
} | |
} | |
cache.put("C"+url, output, 86400); // cache set for 1 day | |
return output; | |
} catch(e){ | |
Logger.log(e); | |
} | |
} | |
// ------------------------------------------------------------ | |
// interaction with Analytics API | |
var GOOGLE_ANALYTICS_PROFILE_ID = "gaProfileId"; | |
function getGAPageViews(startDate, endDate, filter, optMaxResult){ | |
//var startDate = new Date("2011/12/05"); | |
//var endDate = new Date("2012/06/09"); | |
//var filter = "http://mashe.hawksey.info/2012/01/how-is-oer-being-shared/" | |
filter = extractLink(filter); | |
filter = filter.substring(filter.indexOf("/",8)); | |
try { | |
var maxResult = optMaxResult || 10; | |
var baseUrl = "https://www.googleapis.com/analytics/v3/data/ga?"; | |
var ids = getGAProfileID(); | |
var params = ""; | |
params += "ids=ga%3A"+ids; | |
params += "&metrics=ga%3Apageviews&filters=ga%3ApagePath%3D%3D"+encodeURI(filter)+"&sort=-ga%3Apageviews"; | |
params += "&start-date="+twDate(startDate); | |
params += "&end-date="+twDate(endDate); | |
params += "&max-results="+maxResult; | |
var id = filter+twDate(startDate)+twDate(endDate); | |
var cache = CacheService.getPublicCache(); // using Cache service to prevent too many urlfetch | |
var cached = cache.get(id); | |
if (cached != null) { // if value in cache return it | |
return cached; | |
} | |
var URL = baseUrl + params; | |
var oauthConfig = UrlFetchApp.addOAuthService("google"); | |
oauthConfig.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken"); | |
oauthConfig.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?" + | |
"scope=https://www.googleapis.com/auth/analytics.readonly"); | |
oauthConfig.setAuthorizationUrl("https://www.google.com/accounts/OAuthAuthorizeToken"); | |
oauthConfig.setConsumerKey("anonymous"); | |
oauthConfig.setConsumerSecret("anonymous"); | |
var requestData = { | |
"method" : "GET", | |
"oAuthServiceName" : "google", | |
"oAuthUseToken" : "always", | |
}; | |
Logger.log("doing fetch: " + URL); | |
var result = UrlFetchApp.fetch(URL, requestData); | |
result = Utilities.jsonParse(result.getContentText()); | |
var pageView = result.totalsForAllResults["ga:pageviews"]; | |
cache.put(id, pageView, 3600); | |
return pageView; | |
}catch (e) { | |
Logger.log(e+" "+URL); | |
return e; | |
} | |
} | |
function extractLink(text){ | |
// create a url pattern | |
var urlPattern = /(\b(https?|ftp|file):\/\/[-A-Z0-9+&@#\/%?=~_|!:,.;]*[-A-Z0-9+&@#\/%=~_|])/ig; | |
var feedproxyPattern = /(\b(http:\/\/feedproxy.google.com))/i; | |
// extract link from email msg | |
var url = text.match(urlPattern)[0]; | |
if (feedproxyPattern.test(url)){ | |
// if feedproxy url see if cached (or resolve end url) | |
var cache = CacheService.getPublicCache(); // using Cache service to prevent too many urlfetch | |
var cached = cache.get(url); | |
if (cached != null) { // if value in cache return it | |
return cached; | |
} | |
var requestData = { | |
method : "get", | |
headers: { "User-Agent":"GmailProductivitySheet - Google Apps Script"} | |
}; | |
try { | |
// try and get link endpoint using http://expandurl.appspot.com/ | |
var result = UrlFetchApp.fetch("http://expandurl.appspot.com/expand?url="+encodeURIComponent(url), requestData); | |
var j = Utilities.jsonParse(result.getContentText()); | |
var link = (result.getResponseCode()===200)? Utilities.jsonParse(result.getContentText()).end_url:url; | |
var pattern = new RegExp("\\b(utm_source|utm_medium|utm_campaign)=[^&;]+[&;]?", "gi" ); | |
link = link.replace(pattern ,"").replace("?",""); | |
} catch(e) { | |
// if http://expandurl.appspot.com/ doesn't work just return extracted url | |
var link = url; | |
} | |
cache.put(url, link, 3600); | |
return link; | |
} | |
return url; | |
} | |
function getGAProfileID() { | |
return ScriptProperties.getProperty(GOOGLE_ANALYTICS_PROFILE_ID); | |
} | |
function setGAProfileID(profileID) { | |
ScriptProperties.setProperty(GOOGLE_ANALYTICS_PROFILE_ID, profileID); | |
} | |
function authorizeGA() { | |
var oauthConfig = UrlFetchApp.addOAuthService("google"); | |
oauthConfig.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken"); | |
oauthConfig.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?" + | |
"scope=https://www.googleapis.com/auth/analytics.readonly"); | |
oauthConfig.setAuthorizationUrl("https://www.google.com/accounts/OAuthAuthorizeToken"); | |
oauthConfig.setConsumerKey("anonymous"); | |
oauthConfig.setConsumerSecret("anonymous"); | |
var requestData = { | |
"method" : "GET", | |
"oAuthServiceName" : "google", | |
"oAuthUseToken" : "always", | |
}; | |
var URL = "https://www.googleapis.com/analytics/v3/management/accounts/~all/webproperties/~all/profiles"; | |
try { | |
var result = UrlFetchApp.fetch(URL, requestData); | |
result = Utilities.jsonParse(result.getContentText()); | |
} catch (e) { | |
if (e.message) { | |
Logger.log(e.message); | |
result = false; | |
} | |
} | |
Logger.log(result); | |
return result; | |
} | |
function renderGAConfigurationDialog() { | |
var doc = SpreadsheetApp.getActiveSpreadsheet(); | |
var app = UiApp.createApplication().setTitle( | |
"Google Analytics Configuration").setHeight(100).setWidth(420); | |
app.setStyleAttribute("padding", "10px"); | |
var dialogPanel = app.createFlowPanel().setWidth("400px"); | |
if (!ScriptProperties.getProperty('haveGAoAuth')){ // if no profile prompt to run authenticate from script editor | |
var label1 = app.createLabel("Before you can select a Google Analytics profile open Tools > Script Editor and Run > authenticateGoogleAnalytics ").setStyleAttribute("paddingBottom", "10px"); | |
dialogPanel.add(label1); | |
} else { | |
var label1 = app.createLabel("Select a Google Analytics account to analyse referrer data").setStyleAttribute("paddingLeft", "5px").setStyleAttribute("paddingBottom", "5px"); | |
dialogPanel.add(label1); | |
var currentProfile = getGAProfileID(); | |
var profiles = authorizeGA(); | |
var profileList = app.createListBox().setName("profileID"); | |
var idx = 0; | |
for (i in profiles.items){ | |
profileList.addItem(profiles.items[i].name,profiles.items[i].id); | |
if(profiles.items[i].id == currentProfile){ | |
profileList.setSelectedIndex(idx); | |
} | |
idx++; | |
} | |
var listPanel = app.createGrid(1, 2); | |
listPanel.setWidget(0, 0, app.createLabel("Google Analytics account:")); | |
listPanel.setWidget(0, 1, profileList); | |
var saveHandler = app.createServerClickHandler("saveGAConfiguration"); | |
var saveButton = app.createButton("Save Configuration", saveHandler); | |
// Ensure that all form fields get sent along to the handler | |
saveHandler.addCallbackElement(profileList); | |
dialogPanel.add(listPanel); | |
dialogPanel.add(saveButton); | |
} | |
app.add(dialogPanel); | |
doc.show(app); | |
} | |
function twDate(aDate){ | |
var dateString = Utilities.formatDate(aDate, "GMT", "yyyy-MM-dd"); | |
return dateString; | |
} | |
/** Retrieve config params from the UI and store them. */ | |
function saveGAConfiguration(e) { | |
setGAProfileID(e.parameter.profileID) | |
var app = UiApp.getActiveApplication(); | |
app.close(); | |
return app; | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment