Google-sheets – How to apply scripting only to rows with data in them

formulasgoogle sheetsgoogle-apps-script

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)");
}

Best Answer

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.

Related Topic