Importing CCB (Church Community Builder) groups into Google Sheets

gistfile1.js

// Based on the script found here
// https://village.ccbchurch.com/message_comment_list.php?message_id=2530&view_increment=1&search_term=groups

// Get Sheet and CCB Data
function getgroups() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var unamepass ="username:password";
var CCBLink = "https://churchsubdomain.ccbchurch.com/api.php?";
var CCBapi = "srv=group_profiles";
var ReportTitle = "Group Report";


var creds = Utilities.base64Encode(unamepass);
var HEADERS = {"Authorization":"Basic " + creds}; 

var parameters = {
headers:HEADERS,
method :'post'
};

var xmltext = UrlFetchApp.fetch(CCBLink+CCBapi, parameters).getContentText();
var xdata = XmlService.parse(xmltext);
var groups = xdata.getRootElement().getChild('response').getChild('groups').getChildren();

// Clear Sheet
sheet.getDataRange().breakApart();
sheet.clear();
var cell = sheet.getRange("a1");
var rowindex = 3;
var colindex = 0;

// Write Column Headers
cell.setValue(ReportTitle).setFontSize(18);
sheet.getRange("a1:b1").mergeAcross();
cell.offset(0,7).setValue("Run on: " + Utilities.formatDate(new Date(), "CST", "MM-dd-yyyy' at 'hh:mm aa")).setFontStyle("italic").setHorizontalAlignment("right");
cell.offset(1,colindex).setValue("Coach").setFontWeight("bold"); colindex++;
cell.offset(1,colindex).setValue("Group Name").setFontWeight("bold"); colindex++;
cell.offset(1,colindex).setValue("Leader Name").setFontWeight("bold"); colindex++;
cell.offset(1,colindex).setValue("Area of Town").setFontWeight("bold"); colindex++;
cell.offset(1,colindex).setValue("Type").setFontWeight("bold"); colindex++;
cell.offset(1,colindex).setValue("Description").setFontWeight("bold"); colindex++;

cell.offset(1,colindex).setValue("Meet Day").setFontWeight("bold"); colindex++;
cell.offset(1,colindex).setValue("Meet Time").setFontWeight("bold"); colindex++;

// Write Report Body
for (var i in groups ) {
row = groups[i];
// Filter to Select Groups 
if (row.getChild("department").getText().substring(0,5) == "Adult" && row.getChild("group_type").getText().substring(0,11) == "Small Group") {
colindex = 0; 
if(row.getChild('coach')) {cell.offset(rowindex,colindex).setValue(row.getChild('coach').getChildText("full_name"));} colindex++;
cell.offset(rowindex,colindex).setValue(row.getChild("name").getText()); colindex++;
if(row.getChild('main_leader').getChild("full_name")) {cell.offset(rowindex,colindex).setValue(row.getChild('main_leader').getChildText("full_name"));} colindex++;
cell.offset(rowindex,colindex).setValue(row.getChildText("area")); colindex++;
cell.offset(rowindex,colindex).setValue(row.getChildText("group_type")); colindex++;
cell.offset(rowindex,colindex).setValue(row.getChildText("description")); colindex++;
if(row.getChild("meeting_day")) {cell.offset(rowindex,colindex).setValue(row.getChild("meeting_day").getAttribute('id').getValue());} colindex++;
if(row.getChild("meeting_time")) {cell.offset(rowindex,colindex).setValue(row.getChildText("meeting_time"));} colindex++;
rowindex++;
}
}

// Sort report
sheet.getRange(3,1,rowindex,8).sort([7,8,2]);

// Convert Meeting Days to Text
var meetingday = sheet.getRange(3,7,rowindex).getValues();
var meetingdaycell = sheet.getRange("g3");
var meetingdayrow = 0;

for (i=0; i row = meetingday[i]){

if(row == "0") {meetingdaycell.offset(meetingdayrow,0).setValue("n/a")}
if(row == "4") {meetingdaycell.offset(meetingdayrow,0).setValue("Sunday")}
if(row == "5") {meetingdaycell.offset(meetingdayrow,0).setValue("Monday")}
if(row == "6") {meetingdaycell.offset(meetingdayrow,0).setValue("Tuesday")}
if(row == "7") {meetingdaycell.offset(meetingdayrow,0).setValue("Wednesday")}
if(row == "8") {meetingdaycell.offset(meetingdayrow,0).setValue("Thursday")}
if(row == "9") {meetingdaycell.offset(meetingdayrow,0).setValue("Friday")}
if(row == "10") {meetingdaycell.offset(meetingdayrow,0).setValue("Saturday")}

meetingdayrow++;
}

// Find Change in Days, Write to Array 
var meetingdays = sheet.getRange(3,7,rowindex).getValues();
var meetingdaycompare = meetingdays[0][0];
var rowarray = [[0]];
var j = 1;

for (i=0; i if (meetingdays[i][0]!=meetingdaycompare) {
rowarray [j] = [i];
j++;
meetingdaycompare = meetingdays[i][0];
}
}

// Insert Formatted Header at Change in Days 
for (i=0; i var cell = sheet.getRange(rowarray[i][0]+(2+i),7);
var text = sheet.getRange(cell.getRow()+1,7).getValue();
sheet.insertRowAfter(cell.getRow());
sheet.getRange(cell.getRow()+1,1,1,8).mergeAcross().setValue(text).setFontWeight("bold").setHorizontalAlignment("center").setBackground("#EEEEEE");
}

sheet.setColumnWidth(6,225);

}

Reformatted, commented and cleaned up version of this script can be found here, https://gist.github.com/John-Dennert/140ffcaee81d0b422bca