Need help to run my google sheets formula automatically onEdit

Question

I have made a formula (I am in no way a programmer) to work for my Google Sheets needs. But I have to run the code manually once more cells are updated from a google form. I then take certain cells to another sheet to present a report from this information, so I need this formula of mine to run every time there is new information. I also have a column ("O:O") that loads from a previous date, which I would not like it to load to the next day until there is a timestamp from a form entry because it makes the sheet load varios days in advance.

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


}

My formula is working but like I said I would like the O column to not load ahead of entries and rather wait for a new timestamp to work and I would like my formula to calculate as soon as there is new information. Thanks in advance and sorry if my question is poorly made. (My first time on Stack Exchange)

Don't know what you mean when you say I would like the O column to not load ahead of entries What are entries? You also say ` I would like my formula to calculate as soon as there is new information` How is your sheet getting new information. I would probably be helpful to see your spreadsheet and give us examples of what you want to change. — Cooper
Sorry for my lack of better explanaition, But my sheet gets information from a form it is basically calculating money in a register and the O(Money left in the register) is carried to the next day where it is later subtracted from the money counted to know how much was total sales for the day. Sorry if I couldnt use exact terms. — EscuelaEsports

Cooper Cooper · Accepted Answer · 2019-07-04T00:49:10

This will run when any sheet in your spreadsheet is edited. The onEdit() guarantees that there is a real user editing the sheet so it can use activeSpreadsheet() and 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)); 
}

This function will run whenever there is a new form submission. But you can't use activeSpreadsheet() or activeSheet(). You must provide a spreadsheet id and a sheetname.

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

Need help to run my google sheets formula automatically onEdit

1 Answers