Been trying to find information on loops but not really sure where to start to figure out this logic. I want to understand the logic of a loop that achieves the following:

I have a spreadsheet with two columns, column 1 has Work Order #s and Column 2 has Employee Count. I have to assign one break relief employee for every three employees (minimum 3:1 ratio), so I will need to loop over the Employee Count column and add a row once three employees are reached. Here is what I would start with for instance:

Work Order #    Employee Count
123                 2
456                 1
101                 3
112                 2
131                 1
161                 5

Desired result:

Work Order #    Employee Count
[Blank row - Break Relief 1]
123                 2
456                 1
[Blank row - Break Relief 2]
101                 3
[Blank row - Break Relief 3]
112                 2
131                 1
[Blank row - Break Relief 4]
[Blank row - Break Relief 5]
161                 5

Any help would be greatly appreciated

CodePudding user response：

In your situation, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet and save the script. When you use this script, please put a custom function of =SAMPLE(A2:B) to a cell.

function SAMPLE(v) {
  const limit = 3; // This is from your question.
  v = v.filter(r => r.join(""));
  const { values } = v.reduce((o, [a, b], i, ar) => {
    o.c  = b;
    if (o.c <= limit) {
      o.temp.push([a, b]);
    }
    const empty = Math.ceil(o.c / limit);
    if (o.c >= limit || i == ar.length - 1) {
      o.temp = [...Array(empty).fill([null, null]), ...(o.temp.length > 0 ? o.temp : [[a, b]])];
      o.values = [...o.values, ...o.temp];
      o.c = 0;
      o.temp = [];
    }
    return o;
  }, { values: [], temp: [], c: 0 });
  return values;
}

Testing:

When this script is run, the following result is obtained.

Note:

• If you don't want to use the script as a custom function, you can also use the following sample script.

    function myFunction() {
      const sheetName = "Sheet1"; // Please set the source sheet name.
      const limit = 3; // This is from your question.
      const ss = SpreadsheetApp.getActiveSpreadsheet();
      const sheet = ss.getSheetByName(sheetName);
      const [h, ...v] = sheet.getRange("A1:B"   sheet.getLastRow()).getValues().filter(r => r.join(""));
      const { values } = v.reduce((o, [a, b], i, ar) => {
        o.c  = b;
        if (o.c <= limit) {
          o.temp.push([a, b]);
        }
        const empty = Math.ceil(o.c / limit);
        if (o.c >= limit || i == ar.length - 1) {
          o.temp = [...Array(empty).fill([null, null]), ...(o.temp.length > 0 ? o.temp : [[a, b]])];
          o.values = [...o.values, ...o.temp];
          o.c = 0;
          o.temp = [];
        }
        return o;
      }, { values: [], temp: [], c: 0 });
  
      const res = [h, ...values];
      const dstSheet = ss.getSheetByName("Sheet2"); // Please set the destination sheet name.
      dstSheet.getRange(1, 1, res.length, res[0].length).setValues(res);
    }
  

References:

• reduce()
• filter()