Populating imported row with formulas in columns

Question

I have this dataflow:

1. Multiple Google Forms responses feeding Google Sheets tabs' rows
2. Those Google Sheets tabs' rows feeding into one "Summary" tab, mapped properly

On this "Summary" tab, I have some helpful scripts running, including:

• One that populates certain columns in each row with formulas (so that those newly imported mapped rows have fully blank rows to feed into)

Currently, that's working well as an onEdit function

• If I edit one row's column V, for example, it'll show up in column AE, as you see below, which is great.

However, I keep running into trouble trying to get this to AUTOMATICALLY run when a new row is auto-imported into that "Summary" tab.

I tried to use the same script with a "change" trigger, and that didn't do the job.

function PrepopulateCellsOnEdit(e) {
    var range = e.range;
  if (!range) return; // Check if range is null and exit early if it is

  var column = range.getColumn();
  var row = range.getRow();
  var sheet = e.source.getActiveSheet();
  var sheetName = sheet.getName();
  var lastRow = sheet.getLastRow();

  // Check if the edited sheet is "Summary"
  if (sheetName !== "Summary" || row === 1) return;


// Apply formula in column AE to reference column V for the edited row
if (column == 22 && row > 1) { // Column V = 22
  var formulaAE = "=V" + row;
  sheet.getRange(row, 31).setFormula(formulaAE); // Column AE = Column 31
}

  // Apply formula in column CD to reference column F for the edited row
  if (column == 6) {
    var formulaCD = "=F" + row;
    sheet.getRange(row, 82).setFormula(formulaCD);
  }

  // Evaluate the formula in column E and concatenate values from columns V and AX for the edited row
  var valueV = sheet.getRange(row, 22).getValue();
  var valueAX = sheet.getRange(row, 50).getValue();
  if (valueV && valueAX) {
    var formattedValueV = Utilities.formatDate(valueV, Session.getScriptTimeZone(), "M/d/yyyy");
    var formattedValueAX = Utilities.formatDate(valueAX, Session.getScriptTimeZone(), "M/d/yyyy");
    var concatenatedValue = formattedValueV + " - " + formattedValueAX;
    sheet.getRange(row, 5).setValue(concatenatedValue);
  } else {
    sheet.getRange(row, 5).setValue("");
  }

    // Apply today's date in column U for every row with data
  var today = new Date();
  var formattedDate = Utilities.formatDate(today, Session.getScriptTimeZone(), "M/d/yyyy");
  for (var row = 2; row <= lastRow; row++) {
    sheet.getRange(row, 21).setValue(formattedDate);
  }
}

Answer 1

Form responses do not trigger the onEdit function. Try modifying your code to use the onFormSubmit trigger.

If that is not the right direction, I would suggest you create a minimum reproducible demo sheet and script that you can share publicly here.