Wednesday, May 15, 2013

form data averages Google Apps Script

When a Google Form is submitted, it adds a row to your spreadsheet. This changes your formulas, which is a problem if you are trying to do live calculations on submitted data, so you need a script to copy in the correct formulas after each form submission.

Here's an example of how I did that. The script is set to trigger whenever a form is submitted.

function insertAverage() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  var formulas = [
   ["=AVERAGE(C3:C100)", "=AVERAGE(D3:D100)", "=AVERAGE(E3:E100)"]
  var destination = sheet.getRange("C2:E2");

No comments: