I’ve been trying to create a tracker for my team, which involves pulling data from a jira-like website. Unfortunately, unlike jira, there isn’t an easy add-on for pulling data directly to a google sheet. I’ve attempted to use =ImportHTML as a function, but have found that it does not work with sites with authentication requirements, even if you have the log in information.

So, my hypothetical work around is to download the page as an html file and try to read the information directly off of that. Either from browsing local files, or browsing the files in a google drive, what I would LIKE to do is just paste the file into it’s associated drive folder, so that the GScript will automatically read it and post it into a sheet within GSheets.

function doGet(){
  return getHtmlFile()
}

function onOpen(){
 const ui = SpreadsheetApp.getUi()

 ui.createMenu("EODR")
 .addItem("Modal dialog", "showModal")
 .addItem("Sidebar", "showSidebar")
 .addToUi()
}

function showModal(){
  const html = getHtmlFile()

  SpreadsheetApp.getUi()
   .showModalDialog(html, "Test")
}

function showSidebar(){
  const html = getHtmlFile()

  SpreadsheetApp.getUi()
   .showSidebar(html)
}

function getHtmlFile(){
  const htmlFileId = "//insert html field here"

  const blob = DriveApp 
   .getFileById(htmlFileId)
   .getBlob()
   .getDataAsString
 console.log(blob)
 return HtmlService.createHtmlOutput(blob)
  .addMetaTag("viewport", "width=device-width, initial-scale=1")
  .setWidth(500)
  .setHeight(500)
  .setTitle("Test")

}

I have no experience in coding unfortunately, and the code above is what I’ve been able to cobble together from a tutorial. It can read an html file from a drive, but doesn’t automatically fill in the information on the sheet. It is also limited in that is draws from the specific code of a html drive file, but it would be much more ideal if it could draw the information from any file placed in a folder, as the specific file would change daily.

If I can elaborate more, please don’t hesitate to ask. Thank you!