0

I have a Google Sheet that adds multiples rows per day as forms get entered.

What's the best, most hands-off way to apply formulae to rows as they get added?

I've built a script, but it applies the formulae to every row in the column... I'd like to limit it to only rows that have data, for example only if Col A has a timestamp... I can't seem to get IF statements to work properly here...

Here's my script so far...

function AutofillC() {

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[0];

    var cell = sheet.getRange("C2:C");
    cell.setFormula("=CONCATENATE(AG2:AX2)");


    var cell = sheet.getRange("D2:D");
    cell.setFormula("=WEEKDAY(I2)");

    var cell = sheet.getRange("E2:E");
    cell.setFormula("=month(I2)");


    var cell = sheet.getRange("F2:F");
    cell.setFormula("=sum(I2:AF2)");


    var cell = sheet.getRange("G2:G");
    cell.setFormula("=$G$1&ROUNDDOWN((H2-'REFERENCE INFO'!$A$2)/14)");
}
  • Are the new rows added by responses submitted to a Google Form? – Rubén Jan 15 at 2:28
0

I assumed your talking about data coming in from Google Forms. If that's the case then you can do something like this to check to see if there is a value in the TimeStamp column.

In the following function I am assuming that you have setup an installable Form Submit Trigger.

function AutofillC(e) {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheets()[0];
  if(e.namedValues.TimeStamp!=null) {//This checks to see if there is a value in a column named TimeStamp
    var rg=sheet.getRange('C2:G2');
    var fA=[["=CONCATENATE(AG2:AX2)","=WEEKDAY(I2)","=month(I2)","=sum(I2:AF2)","=$G$1&ROUNDDOWN((H2-'REFERENCE INFO'!$A$2)/14)"]];
    rg.setFormulas(fA);
  }

}

I would not recommend doing calculations or adding formulas on spreadsheets connected to forms. I prefer just using them for data collection.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.