Is it possible to pull information from a html file saved to a google drive?

[David Oliphant]

'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 upload it to a drive folder so that the script can pull the appropriate data to the gsheet. 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 (so that even if the "code" is different between sheets, so long as they have the same name, then the data is pulled correctly).

I have no practical experience in coding unfortunately, and the code I've managed so far is only 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!

[Michael O'Shaughnessy]

Well this sounds like an interesting project.  Before we go too far, it would be great if you could share some of your work with us.  You mentioned you have some "working code", share that with us.

Also, if you could provide an example of what you want to end up with that would be great as well.  Just make up the "data" BUT match it to what would be found in the HTML page.  So you could share a sheet with us that has the headers that you want and in the second row an example of the data from the HTML page you want populated.

And everything is possible... it just comes down to how complicated the solution may be!!

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/82cb4292-790c-4b98-bb32-9c4d9c001bd7n%40googlegroups.com.

[David Oliphant]

Of course!

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 = "(can't share html file due to nda, apologies)"

  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")

}

This is the cobbled together code I had mentioned, though I can hardly take credit for it. It was from a very useful tutorial, though unfortunately wasn't quite what I was looking for. Unfortunately I'm unable to provide an example of the HTML I would like to pull from due to NDA. Though honestly, as long as it pulls data consistently in the same areas, I can pretty up the results with functions. I can definitely create an example spreadsheet with placeholder values, if you do believe it would help though! 

[Michael O'Shaughnessy]

Ok, yes it is a little "cobbled" together but it look likes it will get the html file so that's good!!

Need a little more info....  I understand you cannot share the file NDA and all BUT can you remove sensitive data and share the HTML file?  The reason I ask is because there are a lot of DOM reading functions/processes that we might be able to use here.  For instance you can take an HTML file and "query" it for all "<li>" elements then do something with them.  So it would help to see the structure of the file.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/ae94bddb-c72c-40c0-8d5d-db7cf276b26en%40googlegroups.com.

[David Oliphant]

Unfortunately I am no where near confident in my ability to purge all sensitive material from the HTML file, at least not confident enough to risk leaking anything to the internet as a whole. However, there is some good news! While I wasn't able to figure out my original inquiry, I found an alternative. I converted the HTML file to a CSV (where I can confidently purge any NDA info) and then imported the information into Jira, which I am able to use to pull directly into the google sheet. It's a bit tedious to upkeep a second database, but no more tedious than having to download the HTML file from the original data base every time I want to update the stats. 

I appreciate your assistance though, thank you very much! 

[Keith Andersen]

David - why not write an app script to import the csv file directly, not having to go through Jira?

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/4bd7c15e-bf6b-4a4a-b917-d30f3f7c3853n%40googlegroups.com.

[David Oliphant]

That's a good idea, and could absolutely work, but going through Jira seems like the lesser of two evils in my particular situation. I either download 5 HTML files for each filter I need to include in the report, and then convert/edit each of them into CSV files to then add to a drive to pull the info, or, when a bug is written I manually copy it to Jira so it can be pulled automatically. Neither are ideal, but just copying an issue feels slightly less tedious. In a different situation I can definitely see creating a script to read and import the CSV file being more efficient, but since I need to do it several times over, it's just not the best option for me. 

[Keith Andersen]

I recently wrote a script that pulls from Gmail and email that has a csv or zip file and logs it to a spreadsheet. Then you select the cell containing the desired csv and press a button and it opens it and writes it to a tab. The script differentiates between a zip and csv and opens and writes accordingly.

It would be easy to have it pull one at a time, each csv from a drive folder and write it to a spreadsheet.

But I totally get your point. Automated doesn't always make it easier.

Here's the spreadsheet if you're interested. It's view only. Make a copy to get editor rights.

https://docs.google.com/spreadsheets/d/1pDzkKYKilPEnDW57ezkhivzVtgp3BLYrSzYTzcUSvn0/edit?usp=drivesdk

Cheers
Keith

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/3436e581-b924-43ae-bb76-dde4d3dfb321n%40googlegroups.com.