I have a master list with dozens of names in row 2 spread across a bunch of columns (A2:Z2). Under each name is a list of values and data.

Each name should be created into a sheet.

Here is the script used to create a sheet for each name in row 2:

function generateSheetByName() {
  const ss = SpreadsheetApp.getActive();
  var mainSheet = ss.getSheetByName('Master List');
  const sheetNames = mainSheet.getRange(2, 1, mainSheet.getLastRow(), 1).getValues().flat();
  sheetNames.forEach(n => ss.insertSheet(n));
}

I want this script to not only create a sheet for each name but also carry over all values under each name all the way down to the last row of the respective column.

e.g. John is in A2 and A3:A are the values that should be carried over to the sheet created. Sally is B2 and B3:B are the values that should carry over.

In John's sheet - "John" is the header in A1 and the column values sit in A2:A

For every sheet that is made I also want to add other values manually. Like for example, if "John" sheet is created, and 20 values are added in A2:A22, I want the script to add checkbox in B2:B22. Or to always add a formula in B1 like "=counta(a2:a)" or something.

How can I do this with an efficient loop? Note this will likely create 50 sheets and carry over 10-50 values per sheet

Example images:

Master List:

Each name will have a sheet created that will look like this

John's sheet

CodePudding user response：

I believe your goal is as follows.

• You want to achieve from the 1st image to the 2nd image using Google Apps Script.
• When the values are put to the created sheet, you want to insert the checkboxes to the column "B" and want to put a formula to the cell "B1".
• You want to reduce the process cost of the script.

In this case, how about the following sample script?

Sample script:

In this sample script, in order to reduce the process cost of the script, I used Sheets API. When Sheets API is used, the process cost will be able to be reduced a little. So, before you use this script, please enable Sheets API at Advanced Google services.

function generateSheetByName() {
  // 1. Retrieve values from "Master List" sheet.
  const ss = SpreadsheetApp.getActive();
  const mainSheet = ss.getSheetByName('Master List');
  const values = mainSheet.getRange(2, 1, mainSheet.getLastRow(), mainSheet.getLastColumn()).getValues();

  // 2. Transpose the values without the empty cells.
  const t = values[0].map((_, c) => values.reduce((a, r) => {
    if (r[c]) a.push(r[c]);
    return a;
  }, []));

  // 3. Create a request body for using Sheets API.
  const requests = t.flatMap((v, i) => {
    const sheetId = 123456   i;
    const ar = [{ addSheet: { properties: { sheetId, title: v[0] } } }];
    const temp = {
      updateCells: {
        range: { sheetId, startRowIndex: 0, startColumnIndex: 0 },
        fields: "userEnteredValue,dataValidation"
      },
    };
    temp.updateCells.rows = v.map((e, j) => {
      if (j == 0) {
        return { values: [{ userEnteredValue: { stringValue: e } }, { userEnteredValue: { formulaValue: "=counta(a2:a)" } }] }
      }
      const obj = typeof (e) == "string" || e instanceof String ? { stringValue: e } : { numberValue: e }
      return { values: [{ userEnteredValue: obj }, { dataValidation: { condition: { type: "BOOLEAN" } } }] }
    });
    return ar.concat(temp);
  });

  // 4. Request to the Sheets API using the created request body.
  Sheets.Spreadsheets.batchUpdate({requests}, ss.getId());
}

Note:

• In this sample script, I used your sample input and output situations. So when these structures are different from your actual situation, the script might not be able to be used. Please be careful about this.

Reference:

• Method: spreadsheets.batchUpdate