Jade to VBA

code.js

const version = 6

//https://gist.github.com/theGove/08ff996805ad4e0a01d1068b9720fd66

let data
const rules=[]
const rooms={}
let meeting_times

async function auto_exec(){
    // set the tool css
    console.log("===========================",version)
    Jade.set_css(gist_files('style.css'),"naive-bayes")
    // place the tool html 
    tag("tools-body").innerHTML=gist_files('tool.html')
    // fill in values from last tool use
    //const tool_data = await jade.read_object_from_workbook(window.active_tool)
    
    //cookies_to_sheet(tool_data.cookies)
    data=await load_data()
    data.courses={}
    data.professors={}
    data.rooms={}
    //console.log("data",data)
    rules.push({
        name:"meeting hours",
        fn:"location",
        level:"section",
        score:{
            "Non TNRB":0,
            "Case Room":6,
            "Flat Room":5,
            "Fixed-seat Room":2,
            "Tiered Room":3,
            "Hosting Room":1,
            "Auditorium":4,
            "Ends before 9:30":-1,
            "Starts after 3:15":-1,
            
        }
    })
    rooms.TNRB_110="Case Room"
    rooms.TNRB_120="Case Room"
    rooms.TNRB_130="Case Room"
    rooms.TNRB_151="Auditorium"
    rooms.TNRB_164="Fixed-seat Room"
    rooms.TNRB_170="Tiered Room"
    rooms.TNRB_174="Case Room"
    rooms.TNRB_180="Case Room"
    rooms.TNRB_184="Case Room"
    rooms.TNRB_210="Case Room"
    rooms.TNRB_220="Case Room"
    rooms.TNRB_230="Case Room"
    rooms.TNRB_240="Flat Room"
    rooms.TNRB_251="Auditorium"
    rooms.TNRB_260="Case Room"
    rooms.TNRB_264="Flat Room"
    rooms.TNRB_270="Tiered Room"
    rooms.TNRB_280="Fixed-seat Room"
    rooms.TNRB_284="Fixed-seat Room"
    rooms.TNRB_324="Flat Room"
    rooms.TNRB_374="Flat Room"
    rooms.TNRB_484="Flat Room"
    rooms.TNRB_710="Hosting Room"
    rooms.TNRB_W108="Case Room"
    rooms.TNRB_W110="Case Room"
    rooms.TNRB_W118="Case Room"
    rooms.TNRB_W122="Case Room"
    rooms.TNRB_W208="Case Room"
    rooms.TNRB_W210="Case Room"
    rooms.TNRB_W240="Case Room"
    rooms.TNRB_W242="Case Room"
    rooms.TNRB_W308="Case Room"
    rooms.TNRB_W310="Case Room"
    rooms.TNRB_W328="Flat Room"
    rooms.TNRB_W459="Flat Room"

  }
function fill_tt(){
    meeting_times={}
    for(const course of Object.values(data.courses)){
        for(section of Object.values(course.sections)){
            for(const mtg of section.times){
                //record meeting time
                //console.log(mtg)
                const slot_id = mtg.classroom + "_" + mtg.day.join("")  + "_" + mtg.start + "_"  + mtg.end

                if(meeting_times[slot_id]){
                    meeting_times[slot_id].push(section.id)
                }else{
                    meeting_times[slot_id]=[section.id]
                }

            }
        }
   }


}

function process_rules(){
    fill_tt()
    console.log("meeting_times=====", meeting_times)
    const rule_index={}
    let score_card={}
    for(const course of Object.values(data.courses)){
        console.log("course",course)
        const dept=get_dept(course)
        if(!score_card[dept]){
            score_card[dept]={credit_hours:0}
            for(const rule of rules){
                score_card[dept][rule.name]={}
                for(const score of Object.keys(rule.score)){
                    console.log("score_card[dept][rule.name]",score_card[dept][rule.name])
                    console.log("score",score)
                    score_card[dept][rule.name][score]=0
                    console.log("sc", score_card)
                }
                //console.log("score:JSON.stringify(rule.score)",rule.score)
            }
        }
        for(let r=0; r<rules.length; r++){
            console.log(rules)
            const rule = rules[r]
            console.log(rule)
            rule_index[rule.name]=r
            if(rule.level==="course"){
                jade_modules.code[rule.fn](rule,course, score_card[dept][rule.name])
            }else{
                for(section of Object.values(course.sections)){
                    jade_modules.code[rule.fn](rule,section, score_card[dept])
                    //break
                }
            }
            //break
        }
        //break
        score_card[dept].credit_hours+=((course.credits.maxCreditHours + course.credits.minCreditHours)/2)*Object.keys(course.sections).length
    }

    //build table header
    console.log("=========================================")
    const data_header=["Department","Index","Index","Credit Hours"]
    const final_data=[]
    const weights=[null,null,null,null]
    for(const [key,weight] of Object.entries(rules[rule_index["meeting hours"]].score)){
        data_header.push(key)
        weights.push(weight)
    }


    //apply weights

    for(const [dept,card] of Object.entries(score_card)){
        card.points=0
        const one_row=[]
        for(const[category, hours] of Object.entries(card["meeting hours"])){
            // accumulate the adjusted score
            card.points += hours*rules[rule_index["meeting hours"]].score[category]
            one_row.push(hours)
        }
        card.raw_score=card.points/card.credit_hours
        card.index = Math.round(card.raw_score*20)
        console.log(dept,card.index, card.credit_hours)
        if(isNaN(card.index)){card.index=0}
        one_row.unshift(card.credit_hours)
        one_row.unshift(card.index)
        // using a formula instead of the calcuated index
        one_row.unshift("=(SUMPRODUCT(R3C5:R3C13,RC[2]:RC[10])/RC[2])*20")
        
        one_row.unshift(dept)

        if(card.credit_hours>0){
        final_data.push(one_row)
        }

    }
    // sort final data

    final_data.sort(function(a, b) {return b[2] - a[2]})
    final_data.unshift(JSON.parse(JSON.stringify(data_header)))

    const weight_table=[]
    weight_table.push(data_header)
    weight_table.push(weights)
    weight_table[0][0]="Weights"
    weight_table[0][1]=null
    weight_table[0][2]=null
    weight_table[0][3]=null

    console.log("score_card",score_card)
    console.log("final_data",final_data)
    console.log("weight_table",weight_table)
    
    Excel.run(async (excel) => {
        let newSheet = excel.workbook.worksheets.add();
        newSheet.getRangeByIndexes(4,1,final_data.length,final_data[0].length).formulasR1C1=final_data
        newSheet.getRangeByIndexes(1,1,weight_table.length,weight_table[0].length).values=weight_table
        newSheet.getRange('D:D').columnHidden = true
        jade_modules.jet_engine.range_format(newSheet, newSheet.getRangeByIndexes(4,1,final_data.length,final_data[0].length), "numberFormat",'0')
        newSheet.activate()
        excel.sync()
    })
}

function place_formula(){
    Excel.run(async (excel) => {
        let sheet = excel.workbook.worksheets.getActiveWorksheet();
        sheet.getRange("C6").formulasR1C1=tag("formula").value
        excel.sync()
    })
}


function location(rule, section, score_card){
    for(const mtg of section.times){
        console.log("meeting",mtg)
        let weekday = false
        let start_hour
        let end_hour
        let hours=0
        let bldg
        let room
        const slot_id = mtg.classroom + "_" + mtg.day.join("")  + "_" + mtg.start + "_"  + mtg.end
        let tt_factor = 1 //taught together factor

        if(meeting_times[slot_id].includes(section.id)){
            console.log("TT",mtg, slot_id, meeting_times[slot_id])
            tt_factor = 1/meeting_times[slot_id].length            
            if(tt_factor !== 1){
              console.log("++++++++++++++++++++++++++++ttfctor", tt_factor)
            }
        }
        if(!mtg.tba){ //   TBA will be counted as not a week day
            bldg=mtg.classroom.split("_")
            room=bldg.pop()
            room=bldg.pop()
            bldg=bldg.join(" ")
            start_hour=parseInt(mtg.start.toString().slice(0,-2)) + (parseInt(mtg.start.toString().slice(-2))/60)
            end_hour=parseInt(mtg.end.toString().slice(0,-2)) + (parseInt(mtg.end.toString().slice(-2))/60)
            hours=(end_hour - start_hour) * mtg.day.length
            console.log("hours", hours)
            
            if(mtg.day && mtg.day.sort()[0]<4){weekday=true}
            thurs11=false
            if(mtg.day && mtg.day.length===1 && mtg.day[0]===3 && start_hour >=11 && end_hour < 12.5){
                weekday=false
            }
        }


        if(bldg==="SLC"){
            // SLC classes are ignored
        }else if(bldg!=="TNRB" || start_hour>=17 || !weekday || thurs11){
                // THESE CLASSES ARE FREE TO SCHEDULE
            score_card[rule.name]["Non TNRB"]+=hours    
        }else{
            // only adjust hours if in hot tanner
            hours=hours * tt_factor

            score_card[rule.name][rooms[bldg+"_"+room]]+=hours 
            // apply discounts   
            if(start_hour>=15){
                score_card[rule.name]["Starts after 3:15"]+=hours 
            }
            if(end_hour<9.5){
                score_card[rule.name]["Ends before 9:30"]+=hours 
            }

        }
    }
    console.log("at classroom", section, score_card)
}

function get_dept(course_or_section){
    const label =course_or_section.courseCode || course_or_section.code
    return label.substring(0,label.lastIndexOf(" "))
    //return label
}

function coursedog_login(){
window.open("http://coursedog.byu.edu");
}
function aim_login(){
window.open("http://aim.byu.edu");
}
function save_auth(){
const cookies={
    coursedog:get_cookie_from_headers(tag("coursedog-headers").value),
    aim:get_cookie_from_headers(tag("aim-headers").value)
}
const message=[]
if(cookies.coursedog==="Cookie not found"){message.push("Could not find Coursedog Cookie in headers provided.")}
if(cookies.aim==="Cookie not found"){message.push("Could not find AIM Cookie in headers provided.")}

if(message.length>0){
    alert(message.join("<br><br>"))
    return
}

//jade.save_object_to_workbook({cookies:cookies}, window.active_tool)
cookies_to_sheet(cookies)

}
function get_cookie_from_headers(header_string){
const headers = header_string.split("\n")
for(const header of headers){
    const data = header.split(": ")
    if(data[0]==="Cookie"){
        return data[1]
    }
}
return "Cookie not found"

}

function cookies_to_sheet(cookies){
    //console.log("cookies",cookies)
    Excel.run(async (excel) => {
      let sheet = excel.workbook.worksheets.getItem("data");
      sheet.getRange("b2:c3").values=[
        ["CourseDog Cookie", cookies.coursedog],
        ["Aim Cookie",cookies.aim]
      ]
      await excel.sync();
    })
}
async function load_data(){
    const data={}
    await Excel.run(async (excel) => {
        let range = excel.workbook.worksheets.getItem("data").getUsedRange(true);
        range.load("values")
        await excel.sync();
        for(const row of range.values){
            const values=[]
            for(let c=1;c<row.length;c++){
                if(row[c]===""){break}
                values.push(row[c])
            }
            switch(values.length){
                case 0:
                    data[row[0]] = null
                    break
                case 1:
                    data[row[0]] = values[0]
                    break
                default:
                    data[row[0]] = values
            }
            
        }
      })  
      return data
}

function download_data(){
    for(const dept of data.departments){
        get_sections(dept)
    }    
}

function reload_data(){
    for(const dept of data.departments){
        reload_sections(dept)
    }    
}

function local_data(){
    return data["CourseDog Cookie"]
}

function get_sections(department){
    const url="https://app.coursedog.com/api/v1/byu/courses/2022-23/1?limit=1000&skip=0&skipBy=courses&includeRelatedData=true&departments="+department
    const cookie="ajs_group_id=byu; mp_19427fd9b078aa58189b883ced5bdb46_mixpanel=%7B%22distinct_id%22%3A%20%22gove%40byu.edu%22%2C%22%24device_id%22%3A%20%2217cb835055b7ba-05186971d2dccc-b7a1438-7e9000-17cb835055c93f%22%2C%22mp_lib%22%3A%20%22Segment%3A%20web%22%2C%22%24initial_referrer%22%3A%20%22%24direct%22%2C%22%24initial_referring_domain%22%3A%20%22%24direct%22%2C%22%24user_id%22%3A%20%22gove%40byu.edu%22%2C%22mp_name_tag%22%3A%20%22gove%40byu.edu%22%2C%22school%22%3A%20%22byu%22%2C%22roles%22%3A%20%22departmentScheduler%22%2C%22demoUser%22%3A%20false%2C%22env%22%3A%20%22prod%22%2C%22id%22%3A%20%22gove%40byu.edu%22%2C%22%24email%22%3A%20%22gove%40byu.edu%22%2C%22%24first_name%22%3A%20%22Gove%22%2C%22%24last_name%22%3A%20%22Allen%22%2C%22%24name%22%3A%20%22Gove%20Allen%22%7D; wfx_unq=A2iCZ7tSUp6Cg5Za; userSelectedSchool_gove%40byu.edu=byu; fs_uid=rs.fullstory.com#D9HH9#6481333920325632:5144543913828352#f5e81b08#/1682430094; _gcl_au=1.1.675087391.1651063171; __ft_referrer=direct; __lt_referrer=direct; _ga=GA1.2.957780452.1651063171; _uetvid=15dcdb80c62711ecb8a6e9bcfaf02fad; _fbp=fb.1.1651063171457.1722879361; _mkto_trk=id:730-PTZ-885&token:_mch-coursedog.com-1651063171527-76663; _hjSessionUser_2395072=eyJpZCI6IjAwZTMxYjMxLWZhYjctNWJmYi1hMjMxLWMyZmRiYjA4ZTRhMCIsImNyZWF0ZWQiOjE2NTEwNjMxNzE3MTAsImV4aXN0aW5nIjpmYWxzZX0=; _biz_uid=6157fc1d22c44b5b919e18a67ca62340; _biz_nA=2; _biz_flagsA=%7B%22Version%22%3A1%2C%22ViewThrough%22%3A%221%22%2C%22XDomain%22%3A%221%22%7D; _biz_pendingA=%5B%5D; _clck=mly6zf|1|f0z|0; trwv.uid=coursedog-1651063171880-a7600d6e%3A1; intercom-id-qwdwrtb8=4b008aa9-2650-4ae3-822a-0eb74ffc33d4; intercom-session-qwdwrtb8=; ajs_anonymous_id=5b0174c7-829e-43a9-b888-9c11cc0ffd32; token=LSRrBKNKtHZwZgNlxatFcCuyj3SSOhCM6B3SrdnvQe2AVIn2zG; ajs_user_id=gove@byu.edu; AWSELB=7FC1E9130ABCB24C28CC74B1BD1626CF26EC9D82916B9561E23F866203002D2C0D1E02B25E64AA351F2D7F921802AC514A74F210958DAB21C71DB8DA01DECBABDA369F4B80; AWSELBCORS=7FC1E9130ABCB24C28CC74B1BD1626CF26EC9D82916B9561E23F866203002D2C0D1E02B25E64AA351F2D7F921802AC514A74F210958DAB21C71DB8DA01DECBABDA369F4B80; mp_6c65e69f06244199186d5fdb2aea8528_mixpanel=%7B%22distinct_id%22%3A%20%22gove%40byu.edu%22%2C%22%24device_id%22%3A%20%2217b8459ff28359-04bcf646527e0e-c343365-7e9000-17b8459ff29d7c%22%2C%22mp_lib%22%3A%20%22Segment%3A%20web%22%2C%22%24initial_referrer%22%3A%20%22%24direct%22%2C%22%24initial_referring_domain%22%3A%20%22%24direct%22%2C%22%24user_id%22%3A%20%22gove%40byu.edu%22%2C%22mp_name_tag%22%3A%20%22gove%40byu.edu%22%2C%22school%22%3A%20%22byu%22%2C%22roles%22%3A%20%22departmentScheduler%22%2C%22demoUser%22%3A%20false%2C%22env%22%3A%20%22prod%22%2C%22id%22%3A%20%22gove%40byu.edu%22%2C%22%24email%22%3A%20%22gove%40byu.edu%22%2C%22%24first_name%22%3A%20%22Gove%22%2C%22%24last_name%22%3A%20%22Allen%22%2C%22%24name%22%3A%20%22Gove%20Allen%22%7D" //data["CourseDog Cookie"]

    const sheet_name=department.toString()// uncomment this line to keep sheets around
    fetch_data_as_user(url, cookie, process_result,sheet_name)  // get the data from coursedog
    //fetch_data_from_excel_sheet(department.toString(), process_result) // get the data from inside local sheet

}

function reload_sections(department){
    const url="https://app.coursedog.com/api/v1/byu/courses/2022-23/1?limit=1000&skip=0&skipBy=courses&includeRelatedData=true&departments="+department
    const cookie=data["CourseDog Cookie"]

    //const sheet_name=department.toString()// uncomment this line to keep sheets around
    //fetch_data_as_user(url, cookie, process_result,sheet_name)  // get the data from coursedog
    fetch_data_from_excel_sheet(department.toString(), process_result) // get the data from inside local sheet

}



function process_result(result){// only used as a callback
    console.log("the result",result)
    data.courses = Object.assign(data.courses, result.courses)
    data.professors = Object.assign(data.professors, result.professors)
    data.rooms = Object.assign(data.rooms, result.rooms)
    console.log("the data",data)
}



function fetch_data_from_excel_sheet(sheet_name, data_callback){
    //reads data written to excel using fetch_data_as_user
    Excel.run(async (excel) => {
        const sheet=excel.workbook.worksheets.getItem(sheet_name)
        const range=sheet.getUsedRange(true).getIntersection(sheet.getRange("1:1"))
        range.load("values")
        await excel.sync()
        // if the response is longer than 32767 characters it will be in multiple cells due to excel cell limit
        data_callback(JSON.parse(range.values[0].slice(2).join("")))
    })
}

function fetch_data_as_user(url, cookie, data_callback, sheet_name){
    // invoke vba to make an end-user request
    // if sheet_name is provided, the sheed sticks around in Excel
    Excel.run(async (excel) => {
        let newSheet
        if(sheet_name){
            console.log("sheet_name", sheet_name)
            const sheet = excel.workbook.worksheets.getItem(sheet_name)
            sheet.visibility="hidden"
            sheet.delete()
            try{
                await excel.sync()
            }catch(e){
                // trying to delete sheet.  will fail if not exsts
            }
            newSheet = excel.workbook.worksheets.add(sheet_name);
        }else{
            newSheet = excel.workbook.worksheets.add();
        }
        let range = newSheet.getRange("a1:a3");
        range.values=[["get_data"],[url],[cookie]]
        // handler when sheet changes
        newSheet.onChanged.add(async (event)=>{
            console.log("event", event)
            if(event.address.substring(0,2)==="B1"){
                Excel.run(async (excel) => {
                    const sheet=excel.workbook.worksheets.getItem(event.worksheetId)
                    const range=sheet.getUsedRange(true).getIntersection(sheet.getRange("1:1"))
                    sheet.load("name")
                    range.load("values")
                    await excel.sync()
                    // if the response is longer than 32767 characters it will be in multiple cells due to excel cell limit
                    data_callback(JSON.parse(range.values[0].slice(2).join("")))
                    if(!sheet_name){//delete worksheet if name not supplied
                        sheet.visibility="hidden"
                        sheet.delete();
                        excel.sync();
                    }
                })        
            }            
        })
        newSheet.visibility = "veryHidden"
        await excel.sync();
    })

}


function display_data(){
    console.log("data",data)
}

function class_score(offering){
    console.log("offering", offering)
}

style.css

/**/

tool.html

<div style="margin:1rem; text-align: center;"><div style="display:inline-block; text-align: left;">
4
    <table>
        <tbody>
            <tr>
                <td onclick="jade_modules.jet_engine.toggle_next_row(this)" colspan="2" class="section">1. Authorization</td>
            </tr>            
            <tr class="hidden note">
                <td colspan="2"> This step requires you to authenticate at coursedog.com and get the authorization credentials.</td>
            </tr>            
            <tr>
                <td onclick="jade_modules.jet_engine.toggle_next_row(this)"  class="label" >Login to CourseDog:</td>
                <td><button onclick="jade_modules.code.coursedog_login()">
                    <i class="fa-solid fa-dog" style="color:#777; cursor:pointer"></i>
                    </button>
                </td>
            </tr>            
            <tr class="hidden note">
                <td colspan="2">This will open your default browser and attempt to show the coursedog home page. You may need to login.</td>
            </tr>            
            <tr>
                <td onclick="jade_modules.jet_engine.toggle_next_row(this)"  class="label" >Request Headers:</td>
                <td><textarea rows="1" cols="10" id="coursedog-headers"></textarea>
                </td>
            </tr>            
            <tr class="hidden note">
                <td colspan="2">After you are logged in, right-click anywhere on page and choose "inspect." Then go to the "Network" tab.  Then refresh the browser page. Near the top of the network list will be an entry similar to "app.coursedog.com".  Right click this entry, choose "Copy", then "Copy Request Headers". Paste what you just in the text-box above.</td>
            </tr>            
    
            <tr>
                <td onclick="jade_modules.jet_engine.toggle_next_row(this)"  class="label" >Login to AIM:</td>
                <td><button onclick="jade_modules.code.aim_login()">
                    <i class="fa-solid fa-crosshairs" style="color:#777; cursor:pointer"></i>
                    </button>
                </td>
            </tr>            
            <tr class="hidden note">
                <td colspan="2">This will open your default browser and attempt to show the BYU AIM home page. You may need to login.</td>
            </tr>            
            <tr>
                <td onclick="jade_modules.jet_engine.toggle_next_row(this)"  class="label" >Request Headers:</td>
                <td><textarea rows="1" cols="10" id="aim-headers"></textarea>
                </td>
            </tr>            
            <tr class="hidden note">
                <td colspan="2">After you are logged in, right-click anywhere on page and choose "inspect." Then go to the "Network" tab.  Then refresh the browser page. Near the top of the network list will be an entry similar to "mainMenu.cgi".  Right click this entry, choose "Copy", then "Copy Request Headers". Paste what you just in the text-box above.</td>
            </tr>            

            <tr>
                <td colspan="2" align="right"><button onclick="jade_modules.code.save_auth()">Save Authentication</button></td>
            </tr>    
            <tr>
                <td onclick="jade_modules.jet_engine.toggle_next_row(this)" colspan="2" class="section">2. Get Data</td>
            </tr>            
            <tr class="hidden note">
                <td colspan="2"> This step gets the necessary data from courseDog and AIM for all departments.</td>
            </tr>            

            <tr>
                <td align="right"><button onclick="jade_modules.code.download_data()">Download Data</button></td>
                <td align="right"><button onclick="jade_modules.code.reload_data()">Reload Data</button></td>
            </tr>    
            <tr>
                <td onclick="jade_modules.jet_engine.toggle_next_row(this)" colspan="2" class="section">3. Process Rules</td>
            </tr>            
            <tr class="hidden note">
                <td colspan="2">This step runs all the rules against the loaded data.</td>
            </tr>            

            <tr>
                <td colspan="2" align="right"><button onclick="jade_modules.code.process_rules()">Process</button></td>
            </tr>    
            <!--tr>
                <td><input id="formula" value="=ROUND((SUMPRODUCT($E$3:$M$3,E6:M6)/D6)*20,0)"></td>
                <td align="right"><button onclick="jade_modules.code.place_formula()">formula</button></td>
            </tr-->    
        </tbody>
    </table>