google-apps-script - 谷歌表格应用脚本根据日期添加值单元格

Question

我有以下样本表，它使用了以下公式，有些工作。我说的有点是因为在对选项卡进行排序过滤后或有时在添加新行时，经常打开工作表时过滤器公式会出现错误。设置的值一旦最初设置就不会改变，所以我正在寻找一些帮助，让我开始使用一个应用程序脚本，如果相应单元格中的值为空白，该脚本将设置打开的值。

由于上述错误，我想使用应用程序脚本之一，并且也有兴趣学习一下。我在其他平台上做了一些 js 工作，并且相信我可以在以下一项或一项的帮助下拨入这个

交易标签

    FILTER(text(B2:B,"MMMM"),Not(ISBLANK(B2:B)))
    FILTER(text(B2:B,"YYYY"),Not(ISBLANK(B2:B)))
    filter(if(E2:E < 0,"debit","credit"),not(ISBLANK(E2:E)))
    filter(VLOOKUP(D2:D,Lists!A:B,2,false),not(ISBLANK(B2:B)))

余额历史

   FILTER(text(B2:B,"MMMM"),Not(ISBLANK(B2:B)))
   FILTER(text(B2:B,"YYYY"),Not(ISBLANK(B2:B)))
   ARRAYFORMULA({"Date/Time"; B2:B+C2:C})

https://docs.google.com/spreadsheets/d/17SId7mIzO3hVOC36Nq40O0bjPS5YfGOX4wsMU1NlbCU/edit?usp=sharing

标签： google-apps-scriptgoogle-sheets

Answer 1

你可以参考这个示例代码：

function onOpen() {
  updateTransactionsSheet();
  updateBalanceHistorySheet();
}

function updateTransactionsSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var transactionSheet = ss.getSheetByName("Transactions");
  var transactionData = transactionSheet.getDataRange().getValues();

  var listsSheet = ss.getSheetByName("Lists");
  var listsData = listsSheet.getDataRange().getValues();
  var listsMap = {};
  for (var i = 1; i<listsData.length; i++) {
    var category = listsData[i][0];
    var group = listsData[i][1];
    if (!(category in listsMap)) {
      // Add category in the dictionary/javascript object
      listsMap[category] = group;
    }
  }
  Logger.log("Lists Map: "+JSON.stringify(listsMap));

  // Check each row data in the transaction sheet. Ignore header row
  var transactionResult = [];
  for (var i = 1; i < transactionData.length; i++) {
    var row = transactionData[i];
    var date = row[1]; // Get date value in column B (zero-based)
    var category = row[3] // Get category value in column D (zero-based)
    var amount = row[4] // Get amount value in column E (zero-based)

    var dateObj = new Date(date);
    var month = dateObj.toLocaleString('default', { month: 'long' });
    var year = dateObj.getFullYear().toString();
    var amountStr = amount < 0 ? "Debit" : "Credit";
    
    transactionResult.push([month, year, listsMap[category], amountStr])
  }
  Logger.log(transactionResult)
  // Write result in transaction sheet to column P (index 16 one-based) starting from row 2
  transactionSheet.getRange(2,16,transactionResult.length,transactionResult[0].length).setValues(transactionResult);
}

function updateBalanceHistorySheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var balanceSheet = ss.getSheetByName("BalanceHistory");
  var balanceData = balanceSheet.getDataRange().getValues();
  // Check each row data in the balance sheet. Ignore header row
  var balanceResult = [];
  for (var i = 1; i < balanceData.length; i++) {
    var row = balanceData[i];
    var date = row[1]; // Get date value in column B (zero-based)
    var time = row[2]; // Get date value in column C (zero-based)
    
    var dateObj = new Date(date);
    dateObj.setHours(time.getHours(),time.getMinutes(),0);
    var dateTimeStr = Utilities.formatDate(dateObj, Session.getScriptTimeZone(), "MM/dd/yyyy' 'HH:mm:ss");
    var month = dateObj.toLocaleString('default', { month: 'long' });
    var year = dateObj.getFullYear().toString();

    
    balanceResult.push([month, year, dateTimeStr])
  }
  Logger.log(balanceResult)
  //Write result in balance sheet to column M (index 13 one-based) starting from row 2
  balanceSheet.getRange(2,13,balanceResult.length,balanceResult[0].length).setValues(balanceResult);
}

它能做什么？

• 创建一个onOpen() 简单触发器并调用updateTransactionsSheet()并updateBalanceHistorySheet()更新它们各自工作表的值
• 在updateTransactionsSheet(). 使用getActiveSpreadsheet()获取当前活动的 srpeadsheet 对象。使用getSheetByName(name)获取交易表对象。使用getDataRange()获取事务表中的所有数据范围对象，然后使用getValues()获取范围的值。它将返回一个二维值数组。
• 循环每一行（跳过索引为 0 的标题行）并分别获取indate value和amount valuein 。column Bcolumn E
• 使用你的创建一个 JavaScript日期对象date value，使用toLocalString()来获取你的日期对象的月份字符串。并使用getFullYear()获取日期对象的年份
• 在当前行中获得必要的值后，使用array.push()在结果数组中添加数据。请注意，推送的值是一个数组，[month, year, "", amountStr]这是要为 4 列（列 P 到 S）设置的当前行值。
• 将值写入工作表。使用getRange(row, column, numRows, numColumns)获取要写入数据的范围。由于我们想从 P2:Q 开始对它进行修正，因此行开始将为 2，列开始将为 16，行数和列数将基于结果数组（这是一个二维数组）的大小。使用setValues(值)
• 进行了类似的过程updateBalanceHistorySheet()

输出：

（更新）

• 我更新了代码以在工作表中包含Group列值。Transactions我刚刚阅读了工作Lists表中的值并创建了一个JavaScript 对象，其中 A 列（类别）作为key.B 列（组）作为value. 请参阅listsMap变量以了解它是如何创建的。
• 然后我在工作表中使用当前行的类别Transactions来获取其在 JavaScript 对象中的等效组值。