// You can monitor multiple accounts by creating new sheets and adding to the arrays | |
// var sheetNames = [ 'Sheet 1', 'Sheet 2', 'Sheet 3' ]; | |
// var instagramAccounts = [ 'Account 1', 'Account 2', 'Account 3' ]; | |
// Caution: The more data in your Google Spreadsheet, the longer it'll take to load | |
var sheetNames = [ 'Data' ]; | |
var instagramAccounts = [ 'iam.alanyang' ]; | |
// Data from last number of posts / Use value: 0-12 | |
var lastPosts = 12; | |
function insertFollowerCount() { | |
for(var index = 0; index < sheetNames.length;index++){ | |
Logger.log(this.sheetNames[index]); | |
Logger.log(this.instagramAccounts[index]); | |
insertStats(index); | |
}; | |
}; | |
function insertStats(index){ | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
var sheet = ss.getSheetByName(this.sheetNames[index]); | |
accountData = getInstagramData(this.instagramAccounts[index]); | |
var currentRow = getLastRow('A', index); | |
var currentCell = sheet.getRange('A1'); | |
// Adjust the time zone to be the same as the one used in your Google Sheet | |
// You can find the time zone by going to your Goole Sheet. File --> Spreadsheet settings | |
// Used setNumberFormat to format the Date and Engagement Ratio cells | |
currentCell.offset(currentRow, 0).setValue(Utilities.formatDate(new Date(), 'GMT-05:00', 'MMM dd, yyyy')); | |
currentCell.offset(currentRow, 1).setValue(Utilities.formatDate(new Date(), 'GMT-05:00', 'E')); | |
currentCell.offset(currentRow, 2).setValue(accountData.followerCount); | |
currentCell.offset(currentRow, 3).setValue('=IFERROR(($C'+(currentRow+1)+'-$C'+currentRow+'),0)'); | |
currentCell.offset(currentRow, 4).setValue(accountData.followCount); | |
currentCell.offset(currentRow, 5).setValue('=IFERROR(($E'+(currentRow+1)+'-$E'+currentRow+'),0)'); | |
currentCell.offset(currentRow, 6).setValue(accountData.mediaCount); | |
currentCell.offset(currentRow, 7).setValue(accountData.totalLikes); | |
currentCell.offset(currentRow, 8).setValue(accountData.totalComments); | |
currentCell.offset(currentRow, 9).setValue(accountData.engagementRatio); | |
currentCell.offset(currentRow, 9).setNumberFormat('0.00%'); | |
}; | |
function getInstagramData(username) { | |
var r = new RegExp('<script type="text\/javascript">' + | |
'([^{]+?({.*profile_pic_url.*})[^}]+?)' + | |
'<\/script>'); | |
var instagramUrl = 'https://www.instagram.com/' + username, totalComments = 0, totalLikes = 0; | |
var source = UrlFetchApp.fetch(instagramUrl).getContentText(); | |
var jsonStr = source.match(r)[2]; | |
var jsonData = JSON.parse(jsonStr); | |
console.log('data', jsonData); | |
var oldVariantOfData = jsonData['entry_data']['ProfilePage'][0]; | |
console.log('oldVariantOfData', oldVariantOfData); | |
for(var i = 0; i < lastPosts; i++) { | |
var eottm = oldVariantOfData.graphql.user.edge_owner_to_timeline_media.edges[i]; | |
if(eottm != undefined){ | |
totalComments += parseInt(eottm.node.edge_media_to_comment.count); | |
totalLikes += parseInt(eottm.node.edge_liked_by.count); | |
}; | |
}; | |
return { | |
followerCount : oldVariantOfData.graphql.user.edge_followed_by.count, | |
followCount : oldVariantOfData.graphql.user.edge_follow.count, | |
mediaCount : oldVariantOfData.graphql.user.edge_owner_to_timeline_media.count, | |
totalComments : totalComments, | |
totalLikes : totalLikes, | |
engagementRatio : (((totalLikes+totalComments))/oldVariantOfData.graphql.user.edge_followed_by.count)/lastPosts, | |
}; | |
}; | |
function getLastRow(column, index) { | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
var sheet = ss.getSheetByName(this.sheetNames[index]); | |
var range = sheet.getRange(column + '1:' + column); | |
var values = range.getValues(); | |
var newArr = []; | |
var count = 0; | |
for (var i = 0; i < range.getLastRow(); i++) { | |
if (values[i][0]) { | |
newArr.push(values[i][0]); | |
}; | |
}; | |
return newArr.length; | |
}; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment