Automatic rostering in Google Sheets

Question

I need help in rostering employees. I did it in MS. Excel but I don't know how to do it in google Sheets scripts.

I want the same to be implemented in google sheets

When posting code, post text editable code and post a few examples and not all of your work or assignment. Make it less cumbersome so that you can be easily helped, and you can use the help sample to complete your project. Also explain specifically where things were working fine for you on Google Sheets until where you got stuck. Posing questions that let SO Users do all your work may attract little attention and answers. My Advice — ColinWa, Aug 31 '21 at 14:50
Please provide enough code so others can better understand or reproduce the problem. — Community, Sep 02 '21 at 08:13

Yuri Khristich · Accepted Answer · 2021-08-28T12:54:29.997

Please, post text data. Not image.

Probably you need something like this:

function rostering() {
  var ss = SpreadsheetApp.getActiveSheet();
  var emps = [];

  emps[0] = 'OOXXXXX';
  emps[1] = 'XOOXXXX';
  emps[2] = 'XXOOXXX';
  emps[3] = 'XXXOOXX';
  emps[4] = 'XXXXOOX';
  emps[5] = 'XXXXXOO';
  emps[6] = 'OOXXXXX';
  emps[7] = 'XOOXXXX';
  emps[8] = 'XXOOXXX';
  emps[9] = 'XXXOOXX';

  emps = emps.map(e => new Array(5).fill(e).join('').slice(0,31).split(''));

  ss.getRange(2,3,emps.length,31).setValues(emps);
}

The crazy one-liner, just for fun:

function rostering_one_line() {
  SpreadsheetApp.getActiveSheet().getRange(2,3,10,31).setValues (
    ['OOXXXXX','XOOXXXX','XXOOXXX','XXXOOXX','XXXXOOX','XXXXXOO','OOXXXXX','XOOXXXX','XXOOXXX','XXXOOXX']
    .map(e => new Array(5).fill(e).join('').slice(0,31).split('')) );
}

xoxoxo

Output:

	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
Employer1	O	O	X	X	X	X	X	O	O	X	X	X	X	X	O	O	X	X	X	X	X	O	O	X	X	X	X	X	O	O	X
Employer2	X	O	O	X	X	X	X	X	O	O	X	X	X	X	X	O	O	X	X	X	X	X	O	O	X	X	X	X	X	O	O
Employer3	X	X	O	O	X	X	X	X	X	O	O	X	X	X	X	X	O	O	X	X	X	X	X	O	O	X	X	X	X	X	O
Employer4	X	X	X	O	O	X	X	X	X	X	O	O	X	X	X	X	X	O	O	X	X	X	X	X	O	O	X	X	X	X	X
Employer5	X	X	X	X	O	O	X	X	X	X	X	O	O	X	X	X	X	X	O	O	X	X	X	X	X	O	O	X	X	X	X
Employer6	X	X	X	X	X	O	O	X	X	X	X	X	O	O	X	X	X	X	X	O	O	X	X	X	X	X	O	O	X	X	X
Employer7	O	O	X	X	X	X	X	O	O	X	X	X	X	X	O	O	X	X	X	X	X	O	O	X	X	X	X	X	O	O	X
Employer8	X	O	O	X	X	X	X	X	O	O	X	X	X	X	X	O	O	X	X	X	X	X	O	O	X	X	X	X	X	O	O
Employer9	X	X	O	O	X	X	X	X	X	O	O	X	X	X	X	X	O	O	X	X	X	X	X	O	O	X	X	X	X	X	O
Employer10	X	X	X	O	O	X	X	X	X	X	O	O	X	X	X	X	X	O	O	X	X	X	X	X	O	O	X	X	X	X	X

Hi, Thank you so much for answering. Is there a way to generate a random sequence for the same. Like for example - I am rostering for 140 employee and everyday I want minimum 100 employees present. — 5aadat, Aug 29 '21 at 00:43
Hi! I believe it can be done. It sounds like a pretty common task. But there is need more info. It would be much easier if you post it as a new question focused on this problem and describe your goal in more detail: 1) what do you have (this table and this code, for example), 2) how the desired result should look like (you can change the txt table manually, nothing fancy). This way any of local guys (pro coders, not like me) could to participate and suggest the best solution. — Yuri Khristich, Aug 29 '21 at 07:24
And it makes sense to try 14 and 10 employees, or even 5 and 3 ones first. 100 from 140 is too much as an example. But the algorithm will be pretty much the same for any number or employees. — Yuri Khristich, Aug 29 '21 at 07:36

Automatic rostering in Google Sheets

1 Answers1