时间戳

首页 > 解决方案 > 如何使用新工作表作为输入源来总结将它们排序为日期的值

google-apps-script - 如何使用新工作表作为输入源来总结将它们排序为日期的值

问题描述

我不确定问题标题是否很清楚，所以我会更好地解释它。

我有一个我正在处理的电子表格，它基本上是一个仅用于检查财务数据的模板。即使从原始 Excel 文件复制了完整信息，该脚本也只能使用 4 个单元格，即突出显示的单元格。

测试1

第一个 ( C3) 是客户的名称。第二个 ( C9) 是交货日期后的付款到期天数，如果是 2 次或更多付款，则用 分隔/。然后，我们的交货日期为F11，总价值为H25。

我需要做的一切都非常容易，甚至与 Google 日历集成以根据客户注册不同的付款。我在这里是=split()in C9，A26:26拆分单元格的总和，交货日期在下面，以及总金额除以付款次数。

分裂

然后我用来=transpose()创建一个新矩阵来正确地将这些数据发送到我的谷歌日历。

也就是说，很明显我主要使用公式和参考。我现在似乎无法做的是获得分开的付款价值并根据发薪日在第二张纸中订购它们。我不需要客户的名字或任何东西，只需将不同客户的付款值排序为列（或行，任何更容易完成的），这样我就可以获得当天的总数。

我需要的例子

结果

所以我需要以某种方式扫描日期，然后向下添加它们 - 就像我已经对日历所做的那样 - 但要总结当天的总价值，如果有新订单，它可能会改变，所以我想使用第二行作为总和并从第 3 行开始是最好的情况。（另外，请注意此处并非所有值都存在，因为我每天都在工作表中，因此对于此示例，6 月 5 日存在，只是屏幕截图中没有）。

预期产出

假设我有上述情况，有这四笔付款。如果我有一个新订单，它会在模板上看起来像这样：

条目 2

在另一张纸上，它将保留第一个示例中的旧数据并包含新数据，总结付款的价值，如下所示：

输出

这样，可以在前一个数据的下方输入新数据，或者通过创建新行在上方输入新数据。哪个都无所谓。

它可以以静态方式完成，可以这么说，永远不会删除已经过去的日子，动态地，总是根据=today()或 a更新第一天new Date()，我想这要复杂得多。

编辑

我忘了提到每个订单都有一个新的电子表格，我将把它复制到这个模板中。具有预期结果的新工作表就是每个条目的这个工作表，所以我也需要一个特殊的粘贴值。

这是带有脚本的电子表格的完整副本，日历 ID 除外。

代码

function onOpen(){ //creates custom menu tabs
  var ui = SpreadsheetApp.getUi();
      // Or DocumentApp or FormApp.
      ui.createMenu('Tratar Planilha')
          .addItem('Datas e Parcelas', 'main')
          .addItem('Adicionar à Agenda', 'criaEvento') //there's probably no need to use this
      .addToUi();
}

function main(){
  separaData();
  contaParcela();
  formatTexto();
  transpoeTabela();
};

function separaData() { //splits C9 ifnecessary, and prints the rest accordingly
  var spreadsheet = SpreadsheetApp.getActiveSheet(); 

  spreadsheet.getRange('A26').activate(); 
  spreadsheet.getCurrentCell().setFormula('=IF(C9="à vista";"0";SPLIT(C9;"/"))'); 
 
  spreadsheet.getRange('A27').activate(); 
  spreadsheet.getCurrentCell().setFormula('=IF(A26="";"";$F11+A26)'); 
  spreadsheet.getActiveRange().autoFill(spreadsheet.getRange("27:27"), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
};

function contaParcela(){ //counts number of payments and their values
  var spreadsheet = SpreadsheetApp.getActiveSheet(); 
  
  spreadsheet.getRange('A28').activate();
  spreadsheet.getCurrentCell().setFormula('=IF(A26="";"";$H25/COUNTA(26:26))'); 

  spreadsheet.getActiveRange().autoFill(spreadsheet.getRange("28:28"), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
 
  spreadsheet.getRange('A30').activate();
  spreadsheet.getCurrentCell().setFormula('=TEXTJOIN(""; TRUE; COUNTA(26:26); " parcelas de "; A28)');


};

function transpoeTabela(){ //transposes the matrix 

  var spreadsheet = SpreadsheetApp.getActiveSheet();
  spreadsheet.getRange('A29').activate().setFormula('=IF(A26="";"";$C3)');
  spreadsheet.getActiveRange().autoFill(spreadsheet.getRange("29:29"), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  spreadsheet.getRange('A32').setFormula('=TRANSPOSE(27:29)');
  spreadsheet.getRange('D32').setFormula('=IF(A32="";"";counta($26:$26)-counta($26:$26)+1)');
  spreadsheet.getRange('D33').activate();
  spreadsheet.getCurrentCell().setFormula('=IF(A33="";"";D32+1)');
  spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('D33:D'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
};


function criaEvento(){ //sends transposed info to Calendar with description.
  var spreadsheet = SpreadsheetApp.getActiveSheet();
  var calendarID = CalendarApp.getCalendarById('<MY_ID>');
  var valTotal = spreadsheet.getRange('H25').getValue();
  var valParcela = spreadsheet.getRange('A30').getValue();
  var registros = spreadsheet.getRange('A32:D52').getValues();
  Logger.log(registros);
  
  for (x=0; x <= registros.length; x++){
    var shift = registros[x];
    var data = shift[0];
    Logger.log(data);
    var empresa = shift[2];
    Logger.log(empresa);
    var nParcela = shift[3];
    var descricao = 'Total: R$' + valTotal + '\nValor da parcela: R$' + valParcela + '\nNº da parcela: ' + nParcela;
    Logger.log(descricao);
    if (data != ""){
      calendarID.createAllDayEvent(empresa,data).setDescription(descricao);
    }
    else{
      break;
    }
  }
}

function formatTexto() { //just sets cell formatting
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A31:C31').activate();
  spreadsheet.getCurrentCell().setValue('DADOS ENVIADOS AO CALENDARIO');
  spreadsheet.getActiveRangeList().setBackground('#ffff00');
};

标签： google-apps-scriptgoogle-sheetsgoogle-sheets-formula

解决方案

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

function addSumFormula() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Summary'); 

  spreadsheet.getRange('A2').activate();
  spreadsheet.getCurrentCell().setFormula('SUM(A3:A)'); 
  spreadsheet.getActiveRange().autoFill(spreadsheet.getRange("2:2"), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
};

function submitData(){
  var sourceS = SpreadsheetApp.getActiveSheet();
  var destinationS = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Summary'); 
  var amount = sourceS.getRange('A28').getValue();
  var dates = sourceS.getRange('A32:A').getDisplayValues().flat().filter(String);
  Logger.log(amount);
  Logger.log(dates);

  var searchRange = destinationS.getRange('1:1');
  dates.forEach(date => {
    Logger.log(date);
    var textFinder = searchRange.createTextFinder(date);
    var match = textFinder.findNext();
    if(match){
      matchCol = match.getColumn();
      //get row 3 cell of the matched column date and create a range string
      var a1Notation = match.offset(2,0).getA1Notation();
      var rangeStr = a1Notation+":"+a1Notation.replace(/[0-9]/g, '');
      Logger.log(rangeStr);
      //Get the count of data available under the matched column
      var colDataCnt = destinationS.getRange(rangeStr).getDisplayValues().flat().filter(String).length;
      Logger.log(colDataCnt);

      //Write data on the next blank range starting from row 3
      match.offset(2+colDataCnt,0).setValue(amount);
    }
  });
}

带输出的样本表数据：

在此处输入图像描述 在此处输入图像描述

  • 在此示例中，我在工作表“Plan1”中运行 submitData()。因此，在“摘要”表的第 3 行和第 4 行中添加了 3086,25

它能做什么？

  1. 我创建了一张“摘要”表，其中包含第 1 行中的日期。
  2. 创建 addSumFormula() 将在工作表“摘要”的第 2 行中添加总和公式（我手动将第 2 行的数字格式更改为货币
  3. 创建 submitData()，它将根据活动表中列出的日期将“DADOS ENVIADOS AO CALENDARIO”下的数据添加到“摘要”表。
  4. 我从这个范围 A32:A 中得到日期列表。我使用 array.flat() 将二维数组转换为一维数组。使用 array.filter() 删除数组中的空值
  5. 我使用此 a1notation ('1:1') 在“摘要”表中获取日期范围
  6. 我循环了在步骤 4 中获得的所有日期列表。为每个日期创建一个文本查找器，以在“摘要”表中查找匹配的列范围。
  7. 如果找到匹配的日期，我将从第 3 行开始计算该特定日期下可用的数据数量。并在下一个空白行添加新数据。

推荐阅读