JET Engine: processor for JET tool sets

jet_engine.js

const version=10
function auto_exec(){
 ;console.log("===========================",version,"===========================")
 //console.log("at jade_engine.autoexec -------------- 3 -------------",my_gist_id())
}


async function launch(gist_id, local_port_for_gist_load){

  // gist_id is the gist id of the tool set you are trying to launch
 //console.log("at launch")
 //console.log("files from tool gist",window.gist_files[gist_id])

  window.load_tools_from_local_port=local_port_for_gist_load

  for(const [file_name,contents] of Object.entries(window.gist_files[gist_id])){
    if(file_name.slice(-5)===".json"){
      // found the first json file, it must be the tools set
        window.tool_groups = JSON.parse(contents)
        window.tool_groups.gist_id=gist_id
      break
    }
  }
  Jade.open_canvas("tools",gist_files('jet_tools.html'),false)
  tag("tools-head").appendChild(get_selector())
  show_tool_group(0)
}

function get_selector(){
   //console.log("tool_groups",tool_groups)
    window.tool_groups.tool_list={}
    const sel = document.createElement("select")
    sel.className="panel-selector"
    sel.id="tool-selector"
  
    for (let g=0;g<tool_groups.groups.length;g++){
      const group=tool_groups.groups[g]
     //console.log("group",group.name)
      if(group.tools){
       //console.log("tool count", group.tools.length )
      }
      const option = document.createElement("option");
      option.value = g
      option.text = group.name
      if(group.tools){
        option.className="panel-selector-option tool-group"
      }else  {
        option.className="panel-selector-option"      
      }
      sel.appendChild(option);
      if (group.tools){
        for (let t=0;t<group.tools.length;t++){
          const tool=group.tools[t]
         //console.log("tool",tool)
          const option = document.createElement("option");
          option.value = g + "-" + t
          option.text =  tool.name
          option.className="panel-selector-option"
          sel.appendChild(option);    
          window.tool_groups.tool_list[tool.gist_id]=[g,t]
        }
      }
    }
  
  
  
    // for (let i=0; i<jade_panel_labels.length; i++) {
    //   var option = document.createElement("option");
    //   option.value = Jade.panel_label_to_panel_name(jade_panel_labels[i]) 
    //  //console.log("-->", option.value)
    //   option.text = jade_panel_labels[i];
    //   option.className="panel-selector-option"
    //   sel.appendChild(option);
    // }
    
    sel.style.height="40px"
    sel.onchange = jade_modules.jet_engine.select_page
    return sel
  }
  
  function select_page(){
   //console.log("selecting",tag("tool-selector"), )
    const tool_id=tag("tool-selector").value.split("-")
   //console.log(tool_id,)
    if(tool_id.length===1){
      //Tool Group
      show_tool_group(tool_id[0])
    }else{
      // tool
      show_tool(tool_id[0],tool_id[1])
    }
  }
  
  async function show_tool_group(group_index){
   //console.log("showing group",group_index)
    const html=["<p>" + window.tool_groups.groups[group_index].text + "</p>"]
    const tools=window.tool_groups.groups[group_index].tools
    if(tools){
      html.push("<p>Tools in this group include the following:</p><ol>")
      for(let tool_index=0; tool_index<tools.length; tool_index++){
        const tool = tools[tool_index]
        html.push('<li style="margin-bottom:1rem">')
        html.push(`<b style="cursor:pointer" onclick="jade_modules.jet_engine.show_tool(${group_index},${tool_index})">`+tool.name+":</b> ")
        html.push(tool.text)
        html.push("</li>")
      }
      html.push("</ol>")
    }
    tag("tools-body").innerHTML='<div style="margin:1rem; text-align: center;"><div style="display:inline-block; text-align: left;">' + html.join("") + '</div><div id="intro-body" style="display:inline-block; text-align: left;"></div></div><div id="tools-foot" style="margin:1rem;"></div>'
   //console.log("before if",group_index)
    if(group_index==0){// this is the introduction group
     //console.log("after if")
      const history=await get_jet_history()
     //console.log("jet_history",history)
      const html=[]
      if(history.length===0){
        html.push('Click "Introduction" above to select a tool.')
      }else{
        html.push("Recently used tools:<br><ol>")
        for(const entry of history){
         //console.log("entry",entry)
          if(window.tool_groups.tool_list[entry]){// the gist id might not be here if the tool as been removed from the master list
            const [group_index,tool_index]=window.tool_groups.tool_list[entry]
            const tool=window.tool_groups.groups[group_index].tools[tool_index]
            html.push('<li>')
            html.push(`<span style="cursor:pointer" onclick="jade_modules.jet_engine.show_tool(${group_index},${tool_index})">`+tool.name+"</span> ")
            html.push("</li>")
          }
        }
      }
      
      tag("intro-body").innerHTML=html.join("")
      if(jade_settings.workbook.load_gist_id === my_gist_id() ){
        tag("tools-foot").innerHTML='<p onclick="jade_modules.jet_engine.pin(this)"><i class="fa-solid fa-trash-can"></i> Un-pin "Excel Tools" from this workbook</p>'
      }else{  
        tag("tools-foot").innerHTML='<p onclick="jade_modules.jet_engine.pin(this)"><i class="fa-solid fa-thumbtack"></i> Pin "Excel Tools" to this workbook</p>'
      }
    }
  
  }
  
  
  function pin(elem){
    // this is the gist id of the Excel tools gist
   //console.log(elem.innerHTML)
    if(elem.innerHTML.includes('trash')){
      jade_settings.workbook.load_gist_id = ""
      Jade.write_settings_to_workbook()
      elem.innerHTML='<i class="fa-solid fa-thumbtack"></i> Pin "Excel Tools" to this workbook'
    }else{
      jade_settings.workbook.load_gist_id = window.tool_groups.gist_id
      Jade.write_settings_to_workbook()
      elem.innerHTML='<i class="fa-solid fa-trash-can"></i> Un-pin "Excel Tools" from this workbook'
    }
  }
  
  async function get_jet_history(){
    let history = await jade.read_object_from_workbook("jet_history")
    if(history.tools_used){
      history=history.tools_used
    }else{
      history=[]
    }
    return history
  }
  
  
  async function tool_used(gist_id){
    let history = await jade.read_object_from_workbook("jet_history")
    if(history.tools_used){
      history=history.tools_used
    }else{
      history=[]
    }
    //const jet_history = await jade.read_object_from_workbook("jet_history")
   //console.log("at tools used jet_history",history)
    // if(!jet_history.tools_used){
    //   jet_history.tools_used=[]
    // }
    if(!history.includes(gist_id)){
      history.push(gist_id)
    }
    await jade.save_object_to_workbook({tools_used:history}, "jet_history")
   //console.log("after save")
  
  }
  
  async function show_tool(group_index, tool_index){
    tag("tool-selector").value=`${group_index}-${tool_index}`
    const gist_id = window.tool_groups.groups[group_index].tools[tool_index].gist_id
    window.active_tool=gist_id
    if( window.load_tools_from_local_port){
      await jade.load_gist_from_local_server(window.active_tool,undefined,window.load_tools_from_local_port)
      jade.show_element("jet-refresh")
    }else{
      jade.load_gist(window.active_tool)
    }
    tool_used(gist_id)
    
  }

  function toggle_next_row(the_row){
    let elem=the_row.parentNode.nextSibling
    while(elem.tagName!=="TR"){
      elem=elem.nextSibling
    }
    jade.toggle_element(elem)
  }

  function toggle_table(the_icon){
    let elem=the_icon
    while(elem.tagName!=="TR"){
      elem=elem.parentNode
    }

    if(the_icon.className.includes("fa-chevron-down")){
      // showing the content
      while(elem.nextElementSibling){
        elem=elem.nextElementSibling
        if(!elem.className.includes("note")){
          jade.show_element(elem)
        }
      }
      the_icon.className="fa-solid fa-chevron-up"
    }else{
      //hiding the content
      while(elem.nextElementSibling){
        elem=elem.nextElementSibling
        jade.hide_element(elem)
      }
      the_icon.className="fa-solid fa-chevron-down"
    }

    
  }

  async function save_html_values(array_of_element_ids){
    //used to write state to workbook
    await jade.save_object_to_workbook(
      get_form_values(array_of_element_ids)     
      , window.active_tool)

      function get_form_values(tag_names){
        const obj={}
        for(const tag_name of tag_names){
          obj[tag_name]=tag(tag_name).value
        }
        return obj
      }

  }
  
  async function restore_html_values(){
    // analog to save_html_values.  It restores them to the html
    const tool_data = await jade.read_object_from_workbook(window.active_tool)
    //console.log("tool_data",tool_data)
    for(const[key,value] of Object.entries(tool_data)){
      //console.log(key, value)
      tag(key).value=value
    }

  }



  async function get_multicell_address(tag_name){
    const address=await selected_range_address()
    if(address.includes(":")){
      tag('datarange').value=address
    }else{
      tag('datarange').value=await get_current_region()
    }
    
  }
  
  async function get_current_region(){
    let address=""
    await Excel.run(async (excel) => {
      let rng = excel.workbook.getSelectedRange().getSurroundingRegion();
      rng.load("address");
      rng.select()
      await excel.sync();
      address=rng.address.split("!")[1]
    })
    return address
  }
  
  
  async function selected_range_address(){
    let address=""
    await Excel.run(async (excel) => {
      let rng = excel.workbook.getSelectedRange();
      rng.load("address");
      await excel.sync();
      address=rng.address.split("!")[1]
    })
    return address
  }

  function default_selected_range(input){
    // fills the input supplied with the selected range if the input is empty
    if(!input.value){
     //console.log("getting address of active cell")
      Excel.run(async (excel) => {
        let rng = excel.workbook.getSelectedRange();
        rng.load("address");
        await excel.sync();
        input.value = rng.address.split("!")[1]
      })
    }
  }
  
  function get_data_column_address(tag_name){
    // fills the input supplied with the selected range if the input is empty
   //console.log("at get_data_column_address")
      Excel.run(async (excel) => {
        let selection = excel.workbook.getSelectedRange()
        let rng = selection.getEntireColumn().getIntersectionOrNullObject(selection.getSurroundingRegion());
        rng.load("address");
        rng.select()
        await excel.sync();
        tag(tag_name).value=rng.address.split("!")[1]
    })
 
  }
  

  function default_selected_sheet(input){
    // fills the input supplied with the selected range if the input is empty
    if(!input.value){
     //console.log("getting name of selecte sheet")
      Excel.run(async (excel) => {
        let sht = excel.workbook.worksheets.getActiveWorksheet();
        sht.load("name");
        await excel.sync();
        input.value = sht.name
      })
    }
  }
  
  async function get_sheet_name(tag_name){
    Excel.run(async (excel) => {
      let sht = excel.workbook.worksheets.getActiveWorksheet();
      sht.load("name");
      await excel.sync();
      tag(tag_name).value = sht.name
    })
  }


  function range_format(sheet, range_array, attribute,  value){
    const header_color = "Maroon"

    if(value === undefined){
        // set the defaults for various attributes
        switch(attribute) {
            case"numberFormat":
                value="_(* #,##0.00_);_(* (#,##0.00);_(* \"-\"??_);_(@_)";
                break;
            case"format/horizontalAlignment":
                value="Center"
                break;
            case"format/verticalAlignment":
                value="Center"
                break;
            case"format/textOrientation":
                value=-90
                break;
            case"format/wrapText":
                value=true
                break;
            case"format/fill/color":
                value=header_color
                break;
            case"format/font/bold":
                value=true
                break;
            case"format/font/color":
                value="white"
                break;
            case"format/font/italic":
                value=true
                break;
            case"format/font/name":
                value="Arial Narrow"
                break;
            case"format/font/size":
                value=16
                break;
            case"format/font/underline":
                value="none"
                break;
            default:break;
        }
    }

    switch(attribute) { // handle special values
        case "format/fill/color":
            switch(value){  case"task_color": value=task_color;break;
                            case"header_color": value=header_color;break;
            }break;
        case "format/font/color":
            switch(value){  case"header_color": value=header_font_color;break;
            }break;
        case "numberFormat":
            switch(value){  case 0: value="0";break;
            }break;
        default:break;
      }

      var att_list=attribute.split("/");
      var att = att_list.pop()
      var pth = att_list.join("/")


    // now we are ready to set the value of the attribute
    //for(var i=0;i<range_array.length;i++){

    for(const rng of fix_array(range_array)){
      let the_range=rng
      if(typeof rng==='string'){the_range = sheet.getRange(rng)}
    
      if(pth.length==0){
          var attr=the_range[attribute]=value
      }else{
          let attr = get_child_object(the_range, pth)
          attr[att]=value;
      }

    }
  }

  function get_child_object(theObject, path) {
    //getProp({"format":{"font":{"bold":true,"italic":false}}}, "format/font/italic")
    //will return false
    //console.log(theObject, path)
    var parts = path.split("/"),
        idx = parts.shift(),
        newPath = parts.join("/"),
        obj = theObject[idx];

    if(parts.length === 0) {
        return obj;

    } else {
      return get_child_object(obj, newPath);
    }
  }


function fix_array(string_or_array){
    //if  string_or_array is a string, return an array based on it
    if(Array.isArray(string_or_array)){
      return string_or_array;
    }else{
      return [string_or_array];
    }
}

function border_around(sheet, range_array, border_weight, border_style, border_color){

  if( border_weight === undefined) border_weight='Thin';
  if( border_style === undefined) border_style='Continuous';
  if( border_color === undefined) border_color='black';

  range_array = fix_array(range_array);
 //console.log("range_array",range_array)
  for(const rng of range_array){
    let the_range=rng
    if(typeof rng==='string'){the_range = sheet.getRange(rng)}

    the_range.format.borders.getItem('EdgeBottom').style = border_style;
    the_range.format.borders.getItem('EdgeLeft').style = border_style;
    the_range.format.borders.getItem('EdgeRight').style = border_style;
    the_range.format.borders.getItem('EdgeTop').style = border_style;
    the_range.format.borders.getItem('EdgeBottom').weight = border_weight;
    the_range.format.borders.getItem('EdgeLeft').weight = border_weight;
    the_range.format.borders.getItem('EdgeRight').weight = border_weight;
    the_range.format.borders.getItem('EdgeTop').weight = border_weight;
    the_range.format.borders.getItem('EdgeBottom').color = border_color;
    the_range.format.borders.getItem('EdgeLeft').color = border_color;
    the_range.format.borders.getItem('EdgeRight').color = border_color;
    the_range.format.borders.getItem('EdgeTop').color = border_color;
  }
}

function border(sheet, range_array, border_element, border_weight, border_style, border_color){

  if(typeof border_weight == 'undefined') border_weight='Thin';
  if(typeof border_style == 'undefined') border_style='Continuous';
  if(typeof border_color == 'undefined') border_color='Black';

  range_array = fix_array(range_array);
  for(const rng of range_array){
    let the_range=rng
    if(typeof rng==='string'){the_range = sheet.getRange(rng)}
  
      switch (border_element){
          case "EdgeBottom":
              the_range.format.borders.getItem('EdgeBottom').style = border_style;
              the_range.format.borders.getItem('EdgeBottom').weight = border_weight;
              the_range.format.borders.getItem('EdgeBottom').color = border_color;
              break;
          case "EdgeLeft":
              the_range.format.borders.getItem('EdgeLeft').style = border_style;
              the_range.format.borders.getItem('EdgeLeft').weight = border_weight;
              the_range.format.borders.getItem('EdgeLeft').color = border_color;

              break;
          case "EdgeRight":
              the_range.format.borders.getItem('EdgeRight').style = border_style;
              the_range.format.borders.getItem('EdgeRight').weight = border_weight;
              the_range.format.borders.getItem('EdgeRight').color = border_color;

              break;
          case "EdgeTop":
              the_range.format.borders.getItem('EdgeTop').style = border_style;
              the_range.format.borders.getItem('EdgeTop').weight = border_weight;
              the_range.format.borders.getItem('EdgeTop').color = border_color;
              break;

          case "InsideHorizontal":
                  the_range.format.borders.getItem('InsideHorizontal').style = border_style;
                  the_range.format.borders.getItem('InsideHorizontal').weight = border_weight;
                  the_range.format.borders.getItem('InsideHorizontal').color = border_color;
                  break;

          case "InsideVertical":
                  the_range.format.borders.getItem('InsideVertical').style = border_style;
                  the_range.format.borders.getItem('InsideVertical').weight = border_weight;
                  the_range.format.borders.getItem('InsideVertical').color = border_color;
                  break;

      }
  }
}

jet_tools.html

<div id="tools-head"></div>
<div id="tools-body"></div>

jet_tools.json

{
    "groups":[
        {   "name":"Introduction",
            "text":"The tools available here extend the functionality of Excel."
        },
        {   "name":"Statistics",
            "text":"This is the introduction text for stats",
            "tools":[
            {
                "name":"Logistic Regression",
                "text":"Compute a logistic regression",
                "gist_id":"eb60bfa3375c631682282e1377fbb684"
            },
            {
                "name":"Naive Bayes Analysis",
                "text":"Compute a Naive Bayes Analysis",
                "gist_id":"e7c158b8544aaacf2e2dce2a6acfcd52"
            },
            {
                "name":"Cluster Analysis",
                "text":"Compute a Cluster Analysis",
                "gist_id":"0511e31d0a461f2c2b8d38b60bf81303"
            }
            ]
        },
        {   "name":"What-If Analysis",
            "text":"What-If analysis allows you to examine how a spreadsheet model appear under different conditions",
            "tools":[
                {
                    "name":"Goal Seek",
                    "text":"Adjust a cell to try to find a particular result for a formula.",
                    "gist_id":"e7ba294429166f2cf2aeaed622c0cc84"
                },
                {
                    "name":"Scenario Manager",
                    "text":"substitute input values for multiple cells",
                    "gist_id":"162df2908f70f21dd33ad560bb0ae055"
                }
            ]
        
        }
    ]
}