应用脚本将数据从Excel文件提取到Google表格 [英] App Script to Pull data From Excel File to Google Sheet

问题描述

有人知道现有的应用程序脚本可用于将excel工作表中的所有数据导入到Google工作表中吗?

我有一个excel文件，该文件每天从数据库自动更新，其中包含报告所需的所有数据.问题是我必须使用Google表格.

我目前必须每天手动从excel文件中的数据库转储"工作表中复制所有数据，并将数据粘贴到我的Google工作表的"Excel导入"标签中.

我的报告位于Google表格的其他标签中，并从同一Google表格中的"Excel导入"标签中提取其数据.

我宁愿有一个应用脚本，只要我打开Goog​​le工作表并自动执行上面的手动功能，该脚本便会启动.您精明的Google表格用户有何想法?

手动过程 每五分钟数据库自动更新EXCEL文件:工作表-数据库转储" >>>>>>每天手动复制到... >>>>>>>>>> GOOGLE SHEET:TAB-"Excel导入"

拟议的自动化流程 数据库自动更新EXCEL文件:WORKSHEET-数据库转储" >>>>>> Google表格打开时自动更新>>>>>>>>>> GOOGLE SHEET:TAB-"Excel导入"

解决方案

这里是一种方法:

1. 在PC上安装Google云端硬盘-您将使用它将文件同步到Google Apps脚本可以访问的位置. (无法直接访问您的PC.)
2. 在每天运行的Excel电子表格中使用VBA脚本来生成您感兴趣的数据的CSV.相同的脚本(或作为计划任务的单独批处理文件)应将csv复制到您的Google云端硬盘.
3. 在Google电子表格中使用基于时间的触发功能，以导入已同步到Google云端硬盘的CSV文件.

设置各种事件，计划的任务和触发器的时间，以便您有可靠的级联并进行设置.

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"

解决方案

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.

这篇关于应用脚本将数据从Excel文件提取到Google表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！