1

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.

row 2 John Sally James
row 3 Value Value Value
row 4 Value Value Value
row 5 Value Value
row 6 Value Value

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:

enter image description here

enter image description here

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

John's sheet

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • I have to apologize for my poor English skill. Unfortunately, I cannot understand `I want to also be able to dictate several functions simultaneously upon creation of each sheet. For example if it moves over values from A2:A50 for John's sheet, I want to be able to insert a range of checkboxes in B2:B50. Or Sally's is A2:A13, it inserts checkboxes in B2:B13. Or I want to auto insert a formula upon creation of each sheet in cell B1 like "=counta(a2:a)" or something`. In order to correctly understand your question, can you provide the sample input and output situations as the image? – Tanaike Nov 05 '21 at 06:02
  • I added the script and it works perfect. It creates a sheet for each name in Row 2. At the same time it creates each sheet, I want to also add features or values to each sheet. Like in John and Sally sheets I want it to automatically add checkbox in B2:B50 or to add =counta(a2:a) formula in a specific cell like B1 in John and Sally and all other sheets that are created. (I updated my post to make it easier to understand) – usernametaken Nov 05 '21 at 06:11
  • @Tanaike I added photos – usernametaken Nov 05 '21 at 06:21
  • Thank you for repling and adding more information. From your additional information, I proposed a sample script as an answer. Could you please confirm it? If I misunderstood your question and that was not useful, I apologize. – Tanaike Nov 05 '21 at 06:38
  • A sheet for every is a **very bad idea**. You'll run into optimization, scaling and efficiency issues later. – TheMaster Nov 06 '21 at 20:00
  • @TheMaster I agree but in this case it's necessary, unless you can think of a smarter way. I have a main list, each column with a person's name and below their name a list of data. I needed to split every column (aka person) to their own sheet with their own data so they can manually mark up their own sheet instead of a big shared sheet. I can't run formulas and can't scale the data appropriately to each person without splitting them their own sheet, especially when dealing with 50-100 people. Thoughts on a better approach? Tanaike's solution was perfect, it split that data to different sheets – usernametaken Nov 06 '21 at 20:58
  • What about filters or `=query()` with a dropdown on sheet2? User selects his name and the formula or script retrieves all relevant data from the master data sheet. User can then modify his data as necessary. Script adds back the medications to the master sheet. – TheMaster Nov 06 '21 at 21:53

1 Answers1

4

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:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • This is looking perfect and exactly what I want! One issue however is the last line produces this error: ReferenceError: Sheets is not defined – usernametaken Nov 05 '21 at 06:44
  • @usernametaken Thank you for replying. I apologize for my poor English skill. About `ReferenceError: Sheets is not defined`, I'm worried that you might not be enabling Sheets API at Advanced Google services. How about confirming this again? – Tanaike Nov 05 '21 at 06:47