CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
LAD Service Desk
LAD Service Desk

Reputation: 289

How to Insert Result of SUM from Two Date in the spreadsheet

How to Insert Result of SUM from Two Date in the spreadsheet

Data:

Action:

  1. Should be executed only when some insert a value in the column F starting 3rd row.
  2. Only should be executed for the row where was modify.
  3. Should be insert the result in column I, the sum of H+F

Here I have the starting script for the 1 & 2.

function CreationDate(event){
  //Script Sume Date

  var actSht = event.source.getActiveSheet();
  if (actSht.getName() == "sheet1"){
  var activeCell = actSht.getActiveCell(); //Detec the ActiveCell

  var column = activeCell.getColumn(); // Detect the Column of the ActiveCell
  var colNums  = [6]; //Coulmns, whose edit is considered
  if(colNums.indexOf(column) == -1) return; //If column other than considered then return

  var row = activeCell.getRow(); //Detect the ActiveRow
  if(row < 3)   return; //If header row then return

TEST:

I try to formatting this script Clic Here to sum the data and back the result in dd/mm/yyyy hh:mm:ss but I didn't have lucky.

Why is needed?:

Is very important have this formula run asap because I use is to scheduling a critical call to many ISP around the country.

I try to use =arrayformula(sum(h3+f3)) but didn't work. I needs a script because I add new rows all the time.

I will appreciate your help.

Best Regards,

Upvotes: 0

Views: 283

Answers (1)

Mogsdad
Mogsdad

Reputation: 45750

The single-row version of Adam's formula, in row 3 for example, is:

=IF(ISNUMBER(H3)*ISNUMBER(F3);H3+F3;IFERROR(1/0))

Since you're worried that users may damage the formula, you can use an onEdit() trigger function to ensure the formula is updated in Column I anytime the data in Column F is edited.

// When a value is entered in column F, set I = H + F, for rows >= 3.
function onEdit(e) {
  if (!e) {  // This block is for testing in debugger; always uses row 3
    e = {};
    e.range = SpreadsheetApp.getActiveSheet().getRange('F3');
    e.value = e.range.getValue();
  }
  var row = e.range.getRow();
  var col = e.range.getColumn();

  if (col == 6 && row >= 3) {
    // Insert single-cell version of Adam's formula into I
    e.range.getSheet().getRange(row,9)
     .setFormula('=IF(ISNUMBER(H'+row+')*ISNUMBER(F'+row+');H'+row+'+F'+row+';IFERROR(1/0))');
  }
}

An alternative way to insert the correct row number into the formula is to use Regular Expression replacement:

...
// Insert single-cell version of Adam's formula into I
var rowTag = new RegExp('%ROW%','g');
var formula = '=IF(ISNUMBER(H%ROW%)*ISNUMBER(F%ROW%);H%ROW%+F%ROW%;IFERROR(1/0))'
            .replace(rowTag,row);
e.range.getSheet().getRange(row,9).setFormula(formula);
...

Upvotes: 2

Related Questions