I am new to GAS. Need to write a script to execute the following calculation:
There are 87 employees who should be distributed for the week with a limit of 18 employees per day I have percentage allocation (col B).
Total available staff of 87 (cell B1)
allocation number
Mon 0.21 18
Tue 0.08 7
Wed 0.22 19
Thu 0.12 10
Fri 0.25 22
Sat 0.07 6
Sun 0.05 4
All 87 employees should be allocated according to these percentages. Now comes a tricky part. 1. Total number of allocated staff after rounding is 86 (1 is missing) 2. If on some day it turns to be more than 18 employees (e.g. on Wed it is 22%*87=19 employees), only 18 people can be assignes to this day and remaining should be moved to the next "busiest" day (meaning day with highest % that is Fri)
I wrote this code to have not more than 18 per day, but have no idea how to proceed
Would be very grateful for any tips![enter image description here][1]
function allocate() {
var ss=SpreadsheetApp.getActiveSpreadsheet();
var staff=ss.getSheetByName('staff');
var max = ss.getRange('B1').getValue();
for (var r=4; r<=10; r++)
{ var days=staff.getRange(r,2).getValue();
staff.getRange(r,3).setValue(days*max);
}
}
function round()
{
var ss=SpreadsheetApp.getActiveSpreadsheet();
var staff=ss.getSheetByName('staff');
for (var r=4; r<=10; r++)
{ var num=staff.getRange(r,3).getValue();
staff.getRange(r,3).setValue(Math.round(num));
}
}
Aucun commentaire:
Enregistrer un commentaire