I have an apps script that creates a new Google workbook each time a new entry is made in Column A of sheet1 of a Master workbook and renames that new workbook to the last entered data in column A of sheet1 of the Master workbook. It also pastes the URL of the newly created workbooks in Column J of sheet1 of the master workbook. The apps script code is run by an onEdit trigger I set manually. Each time a new workbook is created, the last row that was edited gets copied (from A to J) to the newly created workbook. Every workbook both the master workbook and the newly created workbooks gets shared with a list of emails typed in Column B of Sheet 2 of the master workbook (as editors). Below is the code.
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);
}
I have been trying to make the master workbook copy a template sheet (by ID), but I haven't done so successfully. I need your help on this. Your help is appreciated.