Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
function getDetails(userId) {
//get spreadsheet data from Global variable in 'spreadsheetID.gs'
var ss = SpreadsheetApp.openById(ssId);
var dataSheet = ss.getSheetByName('data');
var list = dataSheet.getDataRange().getValues();
//reset variables for loop
var folderId = '';
var forename='';
var surname='';
var detailsError = 0;
//convert data array to string so can use 'indexOf'
var listString = list.toString();
//Logger.log(string);
//look for userId string in data array - to determine if the below 'loop' should run
var username = listString.indexOf(userId);
Logger.log('Username indexOf is: ' + username);
//check userId exists on spreadsheet, from indexOf, before proceeding
if (username > -1) {
Logger.log('Username exists in spreadsheet, proceeding to get Folder ID + Name');
//length of array to loop through below
var l = list.length;
//loop through to lookup username match and then get Folder ID + Name *****************************************
for (var i=1; i<l; i++) {
if (list[i][0] == userId) {
//from Col B/C
if (list[i][1] != '') {
forename = list[i][1];
surname = list[i][2];
Logger.log(forename + ' ' + surname + ' matches ' + userId);
}
else {
//error increase by 2 for missing name
detailsError+=2;
}
//from Col D
if (list[i][3] != '') {
folderId = list[i][3];
Logger.log('Folder ID is: ' + folderId);
}
else {
//error increase by 3 for missing folder ID
detailsError+=3;
}
}
}//end of loop through to lookup username match and then get Folder ID + Name *********************************
}//end of check userId exists on spreadsheet, from indexOf, before proceeding
else {
//else userId not exist on spreadsheet. Error increase by 1 for missing userId
detailsError++;
}
//return as array so can extract each part as required
return [[forename], [surname], [folderId], [detailsError]];
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment