我如何自动执行Excel工作? (使用Google Apps脚本)

如果您的工作包含将excel文件相互转换,该怎么办?

像每个人一样,我在工作中也参与了excel报告。 每个星期的开始,我们都会从带有excel附件的报告系统中收到一封自动邮件。 因此,我开始了我的周末项目,以自动化这些excel文件。

我执行自动化项目的步骤分为4个步骤。 在执行自动化步骤之前,请先查看下面的传入数据

传入数据的示例(1)

1.准备Excel的数据收集(查询)表

如果我可以在任何Excel中找到信息,这意味着Excel公式也可以通过vlookuphlookupindirect等公式获取相同的信息。因此,第一步是从中心点收集数据作为工作表。

查询表由vlookup () hlookup() indirect() concatenate() 公式 (适用 于正确的单元格)以及 sum() sumifs() averageifs () 等公式组成,这些公式可根据原始数据计算出所需的结果。

我将此工作表称为查询工作表,只需插入一个单独的预定义excel工作表,即可轻松获取重复数据。 该工作表需要作为另一个文件分开以用于重复的excel文件。 (我将再次将此表用于每个传入的数据。)

右键单击查询页面,我将创建一个新的Excel文件。 在此文件中,单元格将显示为空。 (因为使用了iferror()公式)如果excel看起来像“ N / A”,则没有问题。 它可以按原样保存。

然后,我将此“ query.xlsx”文件导入Google表格。 导入后,需要保留电子表格ID “ query.gsheet”文件。 (导入后,可以免费将gsheet文件的目录更改为Google驱动器。)

2.使用Google Apps脚本处理Excel文件

该项目的主要部分是Google Apps脚本,它可以处理excel文件,转换为适当的格式,存储数据然后报告我们的数据。

Google Apps是运行基于Google的产品(例如Gmail,Google云端硬盘,Google文档,Bigquery等)的良好环境。

Google Apps上数据处理脚本的步骤

脚本如何工作?

Google Apps脚本基于JavaScript,即Google脚本。

我们处理数据的需求包括不同的步骤,这些步骤在左图可见。

2.1。 从Get方法获取Excel文件

Google Apps脚本支持处理发布和获取请求(2),并且此功能使我们可以将Google脚本用作小型后端。 通过这种方式,我们像API调用一样触发了脚本。

主脚本以doGet()方法开始,该方法处理请求URL末尾的链接参数。 例如,https ://gs.com/script_id/exec?file = filelink.com / file.xlsx链接将由此链接和下面的相关代码块处理。

 函数doGet(e){ 
  // e.parameter [“ file”] //返回?file = filelink.com / file.xlsx 

file = UrlFetchApp.fetch(e.parameter [“ file”])。getBlob();
}

2.2。 将Excel文件放入驱动器文件夹

获取文件后,脚本会将文件插入指定的(3)驱动器文件夹中。 但是我们需要启用文件转换(以下加粗显示),才能通过Google Spreadsheets API获取数据。 (4)

  var fileInfo = { 
标题:文件名,
mimeType:“ MICROSOFT_EXCEL”,
“父母”:[{'id':DataFolderId}],
};
 文件= Drive.Files.insert(fileInfo,excelfile, {convert:true} ); 
  //file.id //设置文件后,可以通过.id方法获取文件ID 

到目前为止,我刚刚处理了将Excel文件作为新的Google Spreadsheet放入云端硬盘的方案。

2.3。 与查询表合并

我将查询页面( query.gsheet,似乎是源)添加到带有以下代码块的插入数据(似乎是目标)中。

  var source = SpreadsheetApp.openById( idOfQuerySheet ); 
  var sheet = source.getSheets()[0]; 
  var destination = SpreadsheetApp.openById( insertDataID ); 
  sheet.copyTo(destination); 

2.4。 将提取的数据添加到主数据表

然后,我应该将提取的数据插入主数据表,该数据表将包含所有每周共享数据中的提取数据。

  //定义主数据文件和每周数据文件 
var master = SpreadsheetApp.openById(mSS).getSheetByName(mSheet);
var data_sheet = SpreadsheetApp.openById(file.id);
  //获取最后一页,因为查询页面已添加到末尾 
var sheetNumber =电子表格.getNumSheets();
var lastSheet = data_sheet.getSheets()[sheetNumber-1];
  //从单元格范围获取数据 
var data = lastSheet.getRange(“ G3:X3”)。getValues()[0];
  //添加到主表 
master.appendRow(data);

您可以在此处datAutomation存储库中的源代码(此处)中找到我的脚本详细信息。

2.5。 将脚本发布为Web服务

Google脚本面板允许将脚本作为网络服务分发。 从发布->部署为Web应用程序窗格,它将生成一个复杂的请求URL。

在部署之前,检查执行和访问首选项很重要。 尤其是“将应用程序执行为”是最重要的偏好。 Apps脚本需要对脚本中使用的每个API(驱动器API,电子邮件应用程序API,电子表格应用程序API)进行身份验证。 所有这些API均已通过我的帐户的身份验证。 因此,我也将“执行为”偏好设置为我。 (这可能会导致泄漏,请小心执行步骤。)

将邮件附件发送到Apps脚本的IFTTT Applet

3.使用IFTTT自动转发附件

我的Apps脚本将需要自动获取附件。 我使用IFTTT发送包含数据附件的邮件。

在左侧,我的IFTTT食谱如下

如果是这样:来自(我的电子邮件地址)主题中带有标签的邮件。

然后:发出带有附件URL的Web请求

我使用在分发屏幕上方生成的请求URL。 我的请求URL从URL参数获取附件为(?file = file_address) 。 该文件地址已作为IFTTT提供的AttachmentTemporaryUrl

4.准备摘要邮件,然后自动发送

4.1。 邮件准备

如果excel劳动力可以以相同的方式重复,则其报告也将以相同的方式重复。

因此,让我们创建一个注释表 ,该在步骤1汇总数据,如查询表

报告基本上是新数据与先前数据的比较。 根据这种方法,我的自动报告将比较最后两个数据日志。

在下面的示例注释表中,有一个迷你表,该表可以获取最后和之前的数据。 它使用间接公式来寻址包含数据的单元格。

在下面,有一些公式可以确定要写的内容。

  = IF(G6 / H6> 1; 
CONCATENATE(
ROUND((G6-H6)/ H6 * 100; 0); “增长百分比”);
CONCATENATE(
ROUND((H6-G6)/ H6 * 100; 0); “减少百分比”))

此公式可检测到更改并定义口头陈述。

另一个单元正在连接所有上述口头陈述。 请检查下面的表。

样本注释表
样本数据,获取最后两个数据条目,准备有条件的消息,连接邮件 docs.google.com

4.2。 发送邮件

Apps Script还支持MailApp,后者允许代表用户发送电子邮件。

  var SS = SpreadsheetApp.openById(SampleCommentSheet); 
  var sheet = SS.getSheetByName(Comment); 
  var message = sheet.getRange(“ J3”)。getValue(); 
  MailApp.sendEmail(地址,主题,消息); 

让我们测试

三二一去

我将以下这封邮件转发(5)至IFTTT邮件地址。

带有自动化项目附件的邮件

6秒后,这是我的摘要邮件,如下。

报告自动化项目的摘要邮件

数据也已添加到主电子表格中。 =)

笔记

1,所有数据都是随机生成的,本文中的所有样式都已删除。

2.非常有用的指南:http://googleappscripting.com/doget-dopost-tutorial-examples/

3.特定的文件夹和文件ID可以从地址栏中获取,其中ID部分突出显示了一个文件夹示例。

4.所有API都需要单独的权限,Apps脚本会按顺序要求权限。

5,我更喜欢邮件转发而不是自动转发电子邮件来调试脚本,但是正如我在IFTTT部分所提到的,还可以定义自动转发规则,该规则将数据附件发送到IFTTT的地址。

另外,请检查IFTTT和Google Apps脚本是否可以方便地存储或传输数据。