Tools for integrating JADE with data.world

dw_tools.js

jade.use("excel_tools")
class dw_tools{
  static async query(query, id, owner, token, sheet_name, address){

    const url=`https://api.data.world/v0/sql/${owner}/${id}`
    const options={ 
      headers: { 
        'Content-Type' : 'application/json',  
        'Authorization': 'Bearer ' + token,
      },
      'method' : 'POST',
      'body'   : JSON.stringify({
        "query": query,
        "includeTableSchema": true,
        "queryRunToken": dw_tools.uuid()
      })
    }
    const response = await fetch(url,options)
    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)
    if(sheet_name){
      if(!address){address="a1"}
      // caller did specify a sheet name and an address, place the data
      await excel_tools.array_to_table(sheet_name,address,rows)
    }
    return rows
  }


  static async 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()
    })
    
  }

  static 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
}
}