时间戳

首页 > 解决方案 > 使用应用程序脚本更新一系列单元格中的公式

google-apps-script - 使用应用程序脚本更新一系列单元格中的公式

问题描述

我是编码新手。谢谢你的帮助。

我有一个每天维护的预算/支出跟踪电子表格。每个月我都会创建一个新标签并跟踪我的开支。我还有一个“总计”选项卡，它汇总了所有月份选项卡中多个类别的所有费用。

我的目标是有一个菜单按钮，我可以在每月 1 日选择它来执行以下操作。

  1. 创建一个新选项卡并使用月份的三个字母缩写命名，即“Sep”[我下面的代码完成了其中的一部分，但我需要帮助]
  2. 清除新选项卡上某些范围内的内容[我下面的代码就是这样做的]

  3. 将“总计”选项卡上的 vlookup 公式复制到新月份的新列，并将公式从上个月更新到新月份（参见下面的示例）。我的“总计”标签每个月都有一列。这些行是我的支出类别。每个单元格都根据其所在的月份列从月份选项卡中提取支出。[我需要帮助]

    =iferror(VLOOKUP($A3, Apr !$H$14:$I$23,2,FALSE),0)

    至

    =iferror(VLOOKUP($A3, May !$H$14:$I$23,2,FALSE),0)

我当前的代码（部分完成 1，完全完成 2）如下。寻求帮助将我的月份标签重命名为三个字母的月份（例如，Dec）并更新“总计”标签（根据上面的第 3 项）。

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  var menu = ui.createMenu('New Monthly Tab');
  var item = menu.addItem('Create New Tab', 'newMonthTab');
  item.addToUi();
}

function newMonthTab () {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var tz = ss.getSpreadsheetTimeZone();
    var sheet = ss.getActiveSheet();
    var date = Utilities.formatDate(new Date(), tz, 'MM');

  //duplicates and renames the tab
    SpreadsheetApp.getActiveSpreadsheet().duplicateActiveSheet();
    ss.renameActiveSheet(date);

  //clears content
    ss.getRange('A15:D23').activate();
    ss.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
    ss.getRange('I8:J11').activate();
    ss.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
    ss.getRange('M2:O35').activate();
    ss.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
    ss.getRange('W1').activate();
    ss.getRange('X1:X15').copyTo(ss.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
    ss.getRange('X1:X15').activate();
    ss.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
}

更新 2020-05-18：以下是我手动更新到“总计”选项卡时创建的宏的代码。我希望在单击“新每月选项卡”菜单时发生这种情况，但我不确定如何获取脚本以使用新月份选项卡的名称更新 vlookup 公式。我确信有一种更优雅的方法可以做到这一点。

   function updatetotalstab() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('G3').activate();
  spreadsheet.getRange('F3').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  spreadsheet.getCurrentCell().setFormula('=iferror(VLOOKUP($A3,Jun!$H$14:$I$23,2,FALSE),0)');
  spreadsheet.getRange('G4:G12').activate();
  spreadsheet.getRange('G3').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  spreadsheet.getRange('G23').activate();
  spreadsheet.getRange('F23').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  spreadsheet.getCurrentCell().setFormula('=iferror((index(Jun!$R$2:$R$11,match($A23,Jun!$Q$2:$Q$11,0))),0)');
  spreadsheet.getRange('G24:G36').activate();
  spreadsheet.getRange('G23').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  spreadsheet.getRange('G43').activate();
  spreadsheet.getRange('F43').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  spreadsheet.getCurrentCell().setFormula('=iferror((index(Jun!$X$2:$X$13,match($A43,Jun!$V$2:$V$13,0))),0)');
  spreadsheet.getRange('G44:G50').activate();
  spreadsheet.getRange('G43').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  spreadsheet.getRange('G52:G56').activate();
  spreadsheet.getRange('G43').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
};

标签： google-apps-scriptgoogle-sheets

解决方案

对于你的第一个问题：

日期也可以根据Simple Date Format进行格式化

对于 3 个字母的月份，您可以使用： var date = Utilities.formatDate(new Date(), tz, 'MMM');

对于你的第二个问题：

要扩展函数newMonthTab()的功能并设置具有动态内容的公式，月份名称可以连接公式的静态部分（作为字符串）和动态变量 ( date)，只需将它们与+.

样本：

...
var date = Utilities.formatDate(new Date(), tz, 'MMM');
ss.renameActiveSheet(date);
// define here the cell in which you want to paste the formula:
var cell = sheet.getRange('G3');
var formula = '=iferror(VLOOKUP($A3,' + date + '!$H$14:$I$23,2,FALSE),0)';
cell.setFormula(formula);
...

我希望这个示例可以帮助您了解如何自己设置其他公式。

推荐阅读