App Script to Pull data From Excel File to Google

Question

Does anyone know of an existing app script that can be used to import all data in an excel worksheet over to a google sheet?

I have an excel file that is automatically updated daily from a database with all the data I need for my reports. The problem is that I'm required to use Google Sheets.

I'm currently having to manually copy all the data from the "Database Dump" worksheet in my excel file and paste the data to my google sheet "Excel Import" tab every day.

My reports are located in other tabs of the Google Sheet and pull their data from the "Excel Import" tab in the same google sheet.

I would rather have an app script that kicks off whenever I open the google sheet and automates the manual function above. Any ideas from you brilliant Google Sheet users?

Manual Process EVERY FIVE MIN DATABASE AUTO UPDATES EXCEL FILE: WORKSHEET - "Database Dump" >>>>>> Manually Copied Each Day to...>>>>>>>>>> GOOGLE SHEET: TAB - "Excel Import"

Proposed Automated Process DATABASE AUTO UPDATES EXCEL FILE: WORKSHEET - "Database Dump" >>>>>> Auto Updated Upon Google Sheet Open >>>>>>>>>> GOOGLE SHEET: TAB - "Excel Import"

Answer 1

Here's one way to do it:

1. Install Google Drive on your PC - you'll use it to sync a file to somewhere a Google Apps Script can access it. (Can't access your PC directly.)
2. Use a VBA script in your Excel spreadsheet that runs daily to generate a CSV of the data you're interested in. Same script (or a separate batch file, as a scheduled task) should copy the csv to your Google Drive.
3. Use a time-based trigger function in your Google Spreadsheet to import the CSV file, which has synced to Google Drive.

Set the timing for the various events, scheduled tasks and triggers so that you have a reliable cascade, and you're set.