google-apps-script - 需要帮助来自动运行我的 google 表格公式 onEdit

Question

我已经制定了一个公式（我绝不是程序员）来满足我的 Google 表格需求。但是一旦从谷歌表单更新了更多单元格，我必须手动运行代码。然后，我将某些单元格带到另一张表中以根据该信息呈现报告，因此每次有新信息时我都需要运行我的这个公式。我还有一个从前一个日期加载的列（“O：O”），我不希望它加载到第二天，直到表单条目有时间戳，因为它使工作表加载提前几天.

function myFunction() {
 var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  ss.getRange("N2").setFormula("=(I2*0.01)+(H2*0.05)+(G2*0.1)+(F2*0.25)+(E2*0.5)+(D2)+(C2)");

  var lr = ss.getLastRow();
  var fillDownRange = ss.getRange(2, 14, lr-1);
  ss.getRange("N2").copyTo(fillDownRange);


  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  ss.getRange("M2").setFormula("=N2-L2-J2");

  var lr = ss.getLastRow();
  var fillDownRange = ss.getRange(2, 13, lr-1);
  ss.getRange("M2").copyTo(fillDownRange);


     var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  ss.getRange("P3").setFormula("=N3-O3");

  var lr = ss.getLastRow();
  var fillDownRange = ss.getRange(2, 16, lr-1);
  ss.getRange("P3").copyTo(fillDownRange);


       var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  ss.getRange("O3").setFormula("=M2");

    var lr = ss.getLastRow();
  var fillDownRange = ss.getRange(3, 15, lr+1);
  ss.getRange("O3").copyTo(fillDownRange);

         var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  ss.getRange("Q2").setFormula("=A2");

           var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  ss.getRange("Q2").setNumberFormat('dd MMM yyyy');

    var lr = ss.getLastRow();
  var fillDownRange = ss.getRange(2, 17, lr-1);
  ss.getRange("Q2").copyTo(fillDownRange);


}

我的公式正在工作，但就像我说的那样，我希望 O 列不要在条目之前加载，而是等待新的时间戳起作用，我希望我的公式在有新信息时立即计算。如果我的问题做得不好，请提前致谢。（我第一次在 Stack Exchange 上）

标签： google-apps-scriptgoogle-sheetstriggers

Answer 1

这将在您的电子表格中的任何工作表被编辑时运行。onEdit() 保证有一个真实的用户在编辑工作表，因此它可以使用 activeSpreadsheet() 和 activeSheet()。

function onEdit() {
  var ss = SpreadsheetApp.getActiveSheet();
  ss.getRange("N2").setFormula("=(I2*0.01)+(H2*0.05)+(G2*0.1)+(F2*0.25)+(E2*0.5)+(D2)+(C2)");
  ss.getRange("N2").copyTo(ss.getRange(2,14,ss.getLastRow()-1));
  ss.getRange("M2").setFormula("=N2-L2-J2");
  ss.getRange("M2").copyTo(ss.getRange(2, 13, ss.getLastRow()-1));
  ss.getRange("P3").setFormula("=N3-O3");
  ss.getRange("P3").copyTo(ss.getRange(2, 16, ss.getLastRow()-1));
  ss.getRange("O3").setFormula("=M2");
  ss.getRange("O3").copyTo(ss.getRange(3, 15, ss.getLastRow()-2));
  ss.getRange("Q2").setFormula("=A2");
  ss.getRange("Q2").setNumberFormat('dd MMM yyyy');
  ss.getRange("Q2").copyTo(ss.getRange(2, 17, ss.getLastRow()-1)); 
}

只要有新的表单提交，这个函数就会运行。但是您不能使用 activeSpreadsheet() 或 activeSheet()。您必须提供电子表格 ID 和工作表名称。

function onFormSubmitFunc() {
  var ss = SpreadsheetApp.openById('id').getSheetByName('somesheetname');
  ss.getRange("N2").setFormula("=(I2*0.01)+(H2*0.05)+(G2*0.1)+(F2*0.25)+(E2*0.5)+(D2)+(C2)");
  ss.getRange("N2").copyTo(ss.getRange(2,14,ss.getLastRow()-1));
  ss.getRange("M2").setFormula("=N2-L2-J2");
  ss.getRange("M2").copyTo(ss.getRange(2, 13, ss.getLastRow()-1));
  ss.getRange("P3").setFormula("=N3-O3");
  ss.getRange("P3").copyTo(ss.getRange(2, 16, ss.getLastRow()-1));
  ss.getRange("O3").setFormula("=M2");
  ss.getRange("O3").copyTo(ss.getRange(3, 15, ss.getLastRow()-2));
  ss.getRange("Q2").setFormula("=A2");
  ss.getRange("Q2").setNumberFormat('dd MMM yyyy');
  ss.getRange("Q2").copyTo(ss.getRange(2, 17, ss.getLastRow()-1)); 
}