google-apps-scriptgoogle-sheetsrostering

Automatic rostering in Google Sheets


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. Before Table

After Table

I want the same to be implemented in google sheets enter image description here


Solution

  • 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