data.world: import data to worksheet

code.js

//data.world
async function auto_exec(){
  // set the tool css
  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
  jade_modules.jet_engine.restore_html_values()
}

function save_inputs(){
  jade_modules.jet_engine.save_html_values(["token","owner","dataset","sheet-name","sql"])
}

function dw_credentials(){
  window.open("https://data.world/settings/advanced")
}

async function import_data(){
  if(!tag("sql").value && !tag("sheet-name").value){
    alert("You must specify either a Data Sheet or an SQL statement.")
    return
  }

  let sql=tag("sql").value
  if(!sql){
    sql = "SELECT * FROM " + tag("sheet-name").value
  }

  save_inputs()
  
  const url=`https://api.data.world/v0/sql/${tag("owner").value}/${tag("dataset").value}`
  const options={ 
    headers: { 
      'Content-Type' : 'application/json',  
      'Authorization': 'Bearer ' + tag("token").value,
    },
    'method' : 'POST',
    'body'   : JSON.stringify({
      "query": sql,
      "includeTableSchema": true,
      "queryRunToken": uuid()
    })
  }
    jade.hide_element("sql-error")
    const response = await fetch(url,options)
    if(response.status===400){
      const data=await response.text()
      tag("sql-error").innerHTML=data
      jade.show_element("sql-error")
      return
    }

    console.log("response",response) 
    const data = await response.json()
    
    console.log(data) 
    
    const columns=[]
    const rows=[]
    for(let x=0; x<data[0].fields.length; x++){
      columns.push(data[0].fields[x].name)
    }
    rows.push(columns)
    for(let x=1; x<data.length; x++){
      const row=[]
      for(const column of columns){
        row.push(data[x][column])
      }    
      rows.push(row)
    }
    
    //console.log(rows)
    await array_to_table(tag("sheet-name").value,rows,1,1)
}


async function array_to_table(sheet_name, range_data, start_row, start_col){
  await Excel.run(async function(excel){
    var sheet = excel.workbook.worksheets.getItemOrNullObject(sheet_name); 
    await excel.sync()
    
    if (sheet.isNullObject) { 
      sheet = excel.workbook.worksheets.add(sheet_name)
    }else{
      sheet.getUsedRange().clear()
    }
    await excel.sync()
    
    sheet.activate()
    const range=sheet.getRangeByIndexes(start_row-1,start_col-1,range_data.length, range_data[0].length)
    range.values=range_data
    range.format.autofitColumns()
    sheet.tables.add(range, true /*hasHeaders*/);
    await excel.sync()
  })
  
}

function uuid() {
  var u='',m='xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx',i=0,rb=Math.random()*0xffffffff|0;
  while(i++<36) {
      var c=m[i-1],r=rb&0xf,v=c=='x'?r:(r&0x3|0x8);
      u+=(c=='-'||c=='4')?c:v.toString(16);rb=i%8==0?Math.random()*0xffffffff|0:rb>>4
  }
  return u
}

style.css

/**/

tool.html

<div class="hidden" id="jet-refresh" style="margin-left: .5rem;"><i title="Re-load this tool." onclick="jade_modules.jet_engine.select_page()" class="fa-solid fa-arrows-rotate"  cursor:pointer"></i></div>    
<div style="margin:1rem; text-align: center;"><div style="display:inline-block; text-align: left;">
<table style="width:100%" align="center" cellspacing="0" cellpadding="4">
    <tbody>
        <tr>
            <td onclick="jade_modules.jet_engine.toggle_next_row(this)" colspan="2" class="section">&nbsp;1. Configure Connection</td>
            <td class="section"><i  title="Hide or show this step." onclick="jade_modules.jet_engine.toggle_table(this)" class="fa-solid fa-chevron-up"></i></td>
        </tr>            
        <tr class="hidden note">
            <td colspan="3">Here you will identify yourself to data.world by supplying your read/write token a as well as specify the data set to work with.</td>
        </tr>            
        <tr>
            <td onclick="jade_modules.jet_engine.toggle_next_row(this)"  class="label" >Read/Write Token:</td>
            <td><input size="6" type="text" id="token"/></td>
            <td><i title="Open data.world." onclick="jade_modules.code.dw_credentials()" class="fa-solid fa-globe" style="color:#777; cursor:pointer"></i></td>
        </tr>            
        <tr class="hidden note">
            <td colspan="3">Open the <a href="https://data.world/settings/advanced">data.world advanced settings page</a> (you may need to log in). Copy the Read/Write token and enter it here. </td>
        </tr>            
        <tr>
            <td onclick="jade_modules.jet_engine.toggle_next_row(this)"  class="label" >Data Set Owner:</td>
            <td><input size="6" type="text" id="owner" value="atlas-query"/></td>
            <td></td>
        </tr>            
        <tr class="hidden note">
            <td colspan="3">This is the name of the owner of the data set with which you want to work.  If the URL to the dataset you want to work with is <a href="https://data.world/atlas-query/crime-chicago-2018/workspace">https://data.world/atlas-query/redcat-shoes/workspace</a>, then enter "atlas-query" here.</td>
        </tr>            
        <tr>
            <td onclick="jade_modules.jet_engine.toggle_next_row(this)"  class="label" >Data Set Name:</td>
            <td><input size="6" type="text" id="dataset" value="redcat-shoes"/></td>
            <td></td>
        </tr>            
        <tr class="hidden note">
            <td colspan="3">This is the name of the data set with which you want to work.  If the URL to the dataset you want to work with is <a href="https://data.world/atlas-query/crime-chicago-2018/workspace">https://data.world/atlas-query/redcat-shoes/workspace</a>, then enter "redcat-shoes" here.</td>
        </tr>            
    </tbody>
</table>
<div style="margin-top: 1rem;" />
<table  style="width:100%" align="center" cellspacing="0" cellpadding="4">
    <tbody>
        <tr>
            <td onclick="jade_modules.jet_engine.toggle_next_row(this)" colspan="2" class="section">&nbsp;2. Import Data</td>
            <td class="section"><i  title="Hide or show this step." onclick="jade_modules.jet_engine.toggle_table(this)" class="fa-solid fa-chevron-up"></i></td>
        </tr>            
        <tr class="hidden note">
            <td colspan="3">Specify data desired and import.</td>
        </tr>            
        <tr>
            <td onclick="jade_modules.jet_engine.toggle_next_row(this)"  class="label" >Data Sheet:</td>
            <td><input size="6" type="text" id="sheet-name" /></td>
            <td><i title="Get the name of the active sheet." onclick="jade_modules.jet_engine.get_sheet_name('sheet-name')" class="fa-solid fa-file" style="color:#777; cursor:pointer"></i></td>
        </tr>            
        <tr class="hidden note">
            <td colspan="3">This is the name of the worksheet that you want to create when you import the data.  Be careful; all data will be erased from this sheet when the data are imported.  Leave this blank to create a sheet with an arbitrary name.</td>
        </tr>            
        <tr>
            <td colspan="3" onclick="jade_modules.jet_engine.toggle_next_row(this)"  class="label" >SQL:</td>
        </tr>            
        <tr class="hidden note">
            <td colspan="3">Enter a <a href="https://docs.data.world/documentation/sql/concepts/basic/intro.html">Structured Query Language</a> statement to specify the data you want to import.  Leave blank to select all records from a table with the name entered in the "Data Sheet" field above.</td>
        </tr>            
        <tr>
            <td colspan="3"> <textarea style="width:100%" id="sql">SELECT * FROM customer</textarea></td>
        </tr>            
        <tr>
            <td colspan="3" align="right"><button onclick="jade_modules.code.import_data()">Import Data</button></td>
        </tr>            
        <tr>
            <td id="sql-error" class="hidden" colspan="3" style="background-color: white; color:red ;"></td>
        </tr>            
    </tbody>
</table>




</div></div>