Volunteers.js

function volunteerData() {
 var conn = Jdbc.getConnection(url, username, password);
 var stmt = conn.createStatement();

let cell = setupCell("Dashboard","B5")
let sheet = setupSheet("Volunteering")

 var results = stmt.executeQuery('select pd.`cc_outdoor_location` "Assigned Location",db.`first_name` "First Name",db.`nickname` "Facebook Name",pd.cc_volunteer "Selected Roles",vl.volunteer_outdoor_preevent_facebook_promo "FB promo", vl.volunteer_outdoor_crag_coordinator "Crg Coord", vl.volunteer_outdoor_event_reporter "Crg Rprtr", vl.volunteer_outdoor_wednesday_afternoon_admin "Wed PM 1 hr", vl.volunteer_outdoor_wednesday_evening_admin "Lift Sharing",db.competency_outdoor_trip_director "Event Dir",db.scores_volunteer_score_cached "Receptiveness",db.scores_volunteer_reliability_score_cached "V Reliability%",db.stats_attendance_outdoor_day_attended_cached "Attended",  db.`admin-outdoors-requests-notes` "Requests and notes", pd.order_id from wp_member_db db JOIN wp_order_product_customer_lookup pd on pd.user_id = db.id join wp_member_db_volunteering vl on pd.user_id = vl.id where product_id=' + cell + ' AND status in ("wc-processing", "wc-onhold", "wc-on-hold") AND db.stats_attendance_outdoor_day_attended_cached >= 1 AND db.cc_compliance_last_date_of_climbing BETWEEN DATE_SUB(NOW(), INTERVAL 2 MONTH) AND NOW() AND  pd.`cc_attendance`="pending" order by pd.`cc_outdoor_location`, FIELD(pd.cc_volunteer,"none",  "Crag Co-ordinator", "Assistant Crag Co-ordinator", "Crag Reporter", "Sunday Facebook Promo", "Monday Facebook Promo", "Group Maker","Message Maker","Week Director") asc, CAST(db.scores_volunteer_score_cached AS UNSIGNED INTEGER) asc,pd.cc_volunteer desc,vl.volunteer_outdoor_preevent_facebook_promo,vl.volunteer_outdoor_crag_coordinator,vl.volunteer_outdoor_event_reporter, CAST(db.scores_volunteer_score_cached AS UNSIGNED INTEGER) asc');
 appendToSheet(sheet, results);

setColoursFormat(sheet, "D2:D1000","none","#DAF7A6 ")
setColoursFormat(sheet, "D2:C1000","Selected","#FFFFFF")
setColoursFormat(sheet, "D2:D1000","","#e0ffff")
 setTextFormat(sheet,"E2:M1000","No","#a9a9a9")
 setColoursFormatLessThanOrEqualTo(sheet, "K2:K1000","10","#ff75d8")
setColoursFormatLessThanOrEqualTo(sheet, "K2:K1000","20","#ffd898")
setColoursFormatLessThanOrEqualTo(sheet, "K2:K1000","30","#fad02c")

setColoursFormatLessThanOrEqualTo(sheet, "L2:L1000","50","#fad02c")
setColoursFormatLessThanOrEqualTo(sheet, "L2:L1000","80","#ff75d8")
setColoursFormatLessThanOrEqualTo(sheet, "L2:L1000","90","#ffd898")

sheet.setColumnWidth(1, 90);
sheet.setColumnWidth(2, 100);
sheet.setColumnWidth(3, 160);
sheet.setColumnWidth(4, 100);
sheet.setColumnWidth(14, 300);


setWrapped(sheet,"m2:m1000");


}