Skip to content

Embed URL

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
App Script for fetching Google+ Profile data into the Google Docs spreadsheet
/**
* @desc This is an App Script for fetching Google+ Profile data (e.g. name and profile image) to
* the Google Docs spreadsheet
* @author Misha M.-Kupriyanov https://plus.google.com/104512463398531242371/
* @link https://gist.github.com/1919613
*
* 1) Get your Google+ API KEY and paste it instead of %YOUR_API_KEY%
* https://developers.google.com/+/api/oauth#apikey
* https://code.google.com/apis/console#access
* 2) Create and name your spreadsheet "profiles"
* 3) Paste this script as new script via "Format" -> "Script editor"
* 4) Add trigger for "onSpreadsheetEdited" on edited
See "Using Other Installable Event Handlers
https://code.google.com/googleapps/appsscript/guide_events.html#Installable
* 5) Paste your profile id (e.g. my id 104512463398531242371) to first column (A2)
* 6) Enjoy your output
*/
var YOUR_API_KEY = '%YOUR_API_KEY%';
var spreadsheetNameIncoming = 'profiles'; //#2 name your spreadsheet "profiles"
var structure = {id: '', status: '', name: '', gender: '', work: '', image: ''};
var columnProfileId = 1; //paste profile id's in to first column
var columnStatusDone = 'done';
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [ {name: "refreshAll (remove 'done' to refresh data)", functionName: "refreshAll"}
];
ss.addMenu("Actions", menuEntries);
}
/*
* add trigger for "onSpreadsheetEdited" on edited
*/
function onSpreadsheetEdited(e) {
Logger.log('onSpreadsheetEdited:' + Utilities.jsonStringify(e));
Logger.log('onSpreadsheetEdited:' + e.range.rowStart + ':' + e.range.columnEnd);
if (YOUR_API_KEY == '%YOUR_API_KEY%') {
Browser.msgBox("Please set the your Google+ API Key (%YOUR_API_KEY%)");
return;
}
if (e.range.rowStart < 2) {
return;
}
if (e.range.columnEnd != 1) {
return;
}
populateGooglePlusProfileData(e.range.getSheet(), e.range.rowStart , e.range.getValue());
}
/*
* Use this to refresh data rows
*/
function refreshAll() {
batchGetData(true);
}
function batchGetData(refresh) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(spreadsheetNameIncoming);
var range = sheet.getRange(2, columnProfileId, sheet.getLastRow(), 2).getValues();
for (var i = 0; i <= range.length; i++) {
if (range[i][0] == '') {
continue;
}
if (refresh) {
populateGooglePlusProfileData(sheet, i + 2 , range[i][0]);
continue;
}
/*
* work with undone profiles
*/
if(range[i][1] != columnStatusDone) {
populateGooglePlusProfileData(sheet, i + 2 , range[i][0]);
return;
}
}
}
function populateGooglePlusProfileData(sheet, rowNr, id) {
Logger.log('populateGooglePlusProfileData:' + sheet + ',' + rowNr + ',' + id);
if (!id) {
throw 'Missed parameter: profile id';
}
var profile = fetchGoogleProfile(id);
if (!profile.id) {
throw 'Unable to fetch profile with id: ' + id;
}
sheet.getRange(rowNr, 3, 1, 1).setValue(profile.displayName);
sheet.getRange(rowNr, 4, 1, 1).setValue(profile.gender);
if (profile.organizations) {
var work = ''
work += profile.organizations[0].type != undefined ? profile.organizations[0].type + ':' : '';
work += profile.organizations[0].name != undefined ? ' ' + profile.organizations[0].name : '';
work += profile.organizations[0].title != undefined ? ' ' + profile.organizations[0].title : '';
sheet.getRange(rowNr, 5, 1, 1).setValue(work);
}
/*
* give me bigger image
*/
sheet.getRange(rowNr, 6, 1, 1).setValue(profile.image.url.replace('?sz=50','?sz=200'));
sheet.getRange(rowNr, 2, 1, 1).setValue('done');
}
function fetchGoogleProfile(id) {
if (!id) {
throw 'Missed parameter: profile id';
}
var response = UrlFetchApp.fetch('https://www.googleapis.com/plus/v1/people/' + id + '?pp=1&key=' + YOUR_API_KEY);
return Utilities.jsonParse(response.getContentText());
}
/*
* https://developers.google.com/+/api/latest/activities/list
*/
function fetchActivities(id, maxResults) {
if (!id) {
throw 'Missed parameter: profile id';
}
var maxResultsParam = (!maxResults) ? '' : 'maxResults=' + maxResults + '&';
var response = UrlFetchApp.fetch('https://www.googleapis.com/plus/v1/people/' + id + '/activities/public?' + maxResultsParam + 'key=' + YOUR_API_KEY);
return Utilities.jsonParse(response.getContentText());
}
@FKasa

Is this still working? Could you please update the code/instructions?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.