google-apps-scriptgoogle-sheetsgoogle-sheets-apigoogle-apps-script-api

Apps script that shares a google sheet to a specific group of people

I have an apps script that creates a new Google worksheet each time a new entry is made in Column A of sheet1 of a Master Sheet and renames that new worksheet to the last entered data in column A of sheet1 of the Master Sheet. It also pastes the URL of the newly created sheets in Column J of sheet1 of the master sheet. Below is the code

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const nss = SpreadsheetApp.create(sh.getRange(sh.getLastRow(), 1).getDisplayValue())
  sh.getRange(sh.getLastRow(), 10).setValue(nss.getUrl());
}

The apps script code is run by an onEdit trigger I set manually. Each time a new worksheet is created, I want the last row that was edited to be copied (from A to J) to the newly created worksheet I also want every worksheet both the master sheet and the newly created sheet to be shared (as editors) with a list of emails in Column B of Sheet 2 of the master sheet. I appreciate your help.

Solution

  • I believe your goal is as follows.

    1. You want to copy the last row (columns "A" to "J") to the 1st tab of the created Spreadsheet.
    2. You want to share the active Spreadsheet and the created Spreadsheet with the emails retrieved from column "B" of "Sheet2" in the active Spreadsheet.
    3. You want to run the script by both the installable OnEdit trigger and the manual execution.

    Modification points:

    When these points are reflected in your script, it becomes as follows.

    Modified script:

    From a list of emails in Column B of Sheet 2 of the master sheet, this modified script retrieves the emails from column "B" of "Sheet2" of the active Spreadsheet. If your sheet name is not "Sheet2", please modify it.

    function myFunction() {
  // Creating new Spreadsheet.
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const lastRow = sh.getLastRow();
  const values = sh.getRange(`A${lastRow}:J${lastRow}`).getValues()[0];
  const nss = SpreadsheetApp.create(values[0]);
  const url = nss.getUrl();
  sh.getRange(lastRow, 10).setValue(url);
  values[9] = url;
  nss.appendRow(values);

  // Sharing Spreadsheets.
  const sheet2 = ss.getSheetByName("Sheet2");
  const emails = [...new Set(sheet2.getRange("B1:B" + sheet2.getLastRow()).getValues().reduce((ar, [b]) => {
    if (b && b.includes("@")) ar.push(b);
    return ar;
  }, []))];
  ss.getEditors().forEach(e => ss.removeEditor(e));
  ss.addEditors(emails);
  nss.addEditors(emails);
}

    References: