Is there a way to automatically download files from Gmail or Google Drive

Question

Disclaimer: Ouside of a little VBA knowledge I am not a programmer.

I have several Web-Intelligence reports that are scheduled to automatically run for my company. Ideally, we would have the reports go directly to our server; unfortunately, we don't have the rights to connect a new server (long story).

So, as a halfway workaround, I currently have the report scheduled to send the report to my corporate Gmail account in excel format. When I receive the emails, I download the files into a folder on my computer. Then a separate program reaches out, grabs them from the folder and appends them to an oracle database.

While it's not a huge deal to download the files, I am often stuck in meetings and entire business process gets delayed by a day or more, so I am looking for ways to automate this process.

Is there any way to automate this without the google drive desktop app, or an extension/app (both of which are disabled in my company)? Given my limited abilities, I'm thinking along the lines of writing a VBA script that executes once excel is loaded to download the file from the google drive link.. and using task scheduler to automatically start excel at a certain time each day (or on start-up)... but honestly have no idea where to start.

Answer 1

If I understand you, you have a third-party reporting service that --in an ideal world-- would write data straight into your Oracle database, but due to some policy you are having to inject the data yourself. So the question really comes down to whether using email as the middleware between reporter and Oracle is the best way, and if it is (life can be hard) whether it can be automated.

On the first, can the reporter be asked to write the results to something like a secure FTP or web download site that they host? Automating FTP/SFTP is easier than automating email: email is another wrapper around your data that you have to peel away before you can get to your report, whereas an FTP or website just has the files.

On the second, there is a Google API to GMail, and you can drive it with VBA, but it is not for the faint of heart. See here to get started. Even here you will have top figure out how to identify the messages of interest in your inbox and then extract the attachment from the email. Good luck.