0

I need to calculate the time remaining in google sheets app script. Below is my work sheet cells.

enter image description here

What I need:

I need to sum up estimated working days which have 100%. In the picture, I need to get 0.3.

What I've tried:

var mainWsName = "master";

function myFunction() {  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mainWsName);
  var values = sheet.getRange('D4:D48').getValues();
  
  var a = values.join().split(',');
 
  Logger.log(a); // [, 1, 1, 1, , ]
}

My status column starts from D4 and my estimated working days starts from C4.

Htet Phyo Naing
  • 464
  • 7
  • 20

1 Answers1

1

Here is the solution you are looking for:

function myFunction() {
  
 const ss = SpreadsheetApp.getActive();
 const sh = ss.getSheetByName('Sheet1');
 const est_works = sh.getRange('C4:C48').getValues().flat([1]); 
 const status = sh.getRange('D4:D48').getDisplayValues().flat([1]);
  
  var sm = 0;
  status.forEach( (element,index) => {
  if(status[index]=== '100%'){sm+=est_works[index]}             
});

sh.getRange('D2').setValue(sm.toFixed(2))
  
}

Explanation:

I iterate through the status column and for every cell that contains the value '100%' I sum it up to the variable sm. Then I paste the resulting value to cell D2 as your screenshot illustrates. Feel free to change the sheet name, in my case Sheet1.

Marios
  • 26,333
  • 8
  • 32
  • 52
  • It needs to be explicitly run inside the script editor. How can I update that **D2** column whenever I edit that *status* range which is *D4:D48*. – Htet Phyo Naing Aug 14 '20 at 11:33
  • @HtetPhyoNaing what do you mean? You can just edit it to the cell of your preference. E.g. `getRange('D3')` for example – Marios Aug 14 '20 at 11:35
  • In my screen shot, let' say I update *D4* to *100%*. I need to get update *D3* to **2.3** automatically. In the current code, it does not. – Htet Phyo Naing Aug 14 '20 at 11:43
  • I see. If you want a value to be changed whenever you edit a particular cell you should use onEdit() triggers. Please post another question to ask particularly for an onEdit trigger of your function and I will be able to answer it. @HtetPhyoNaing – Marios Aug 14 '20 at 11:48
  • Ok. Thanks. I updated `myFunction` to `onEdit` and it works now. – Htet Phyo Naing Aug 14 '20 at 12:12