I have a list of employees and my goal is to create custom sheets for each employee that pulls certain data specific to the employees from another sheet. Everything works out except for the fact that all of the custom sheets are only pulling one employee data. The name and links of the sheets are all correct where it's employee A, B, C but employee B and C are only showing employee A's data. This is what I have so far:
function createNewGoogleDocs() {
const QuotaFile = SpreadsheetApp.openById('AAA');
const googleSheetTemplate = DriveApp.getFileById('BBB');
const destinationFolder = DriveApp.getFolderById('CCC');
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('employeedata');
const rows = sheet.getDataRange().getValues();
rows.forEach(function(row, index) {
if (index === 0) return;
if (row [3] ) return;
const copy = googleSheetTemplate.makeCopy(`${row[1]}, ${row[0]} EmployeeSummary` , destinationFolder);
const doc = SpreadsheetApp.openById(copy.getId());
const quota = quotaFile.getRange("Employees!B1:D10" );
doc.getRangeByName('ID').setValue(quota.getCell(4,1).getValue())
I am very new to scripts, definitely know i'm doing something wrong.
I tried changing names, range, etc and it would just break everything. I know that I'm definitely not pulling the data correctly so hoping it's a simple fix! Thank you!
I modified your current script and I replicate the Sheet names to create my own data. This approach returns a generated spreadsheet for each employee where i added a dynamic range on your script and add data mapping to fetch the value into specific cell
function createNewGoogleDocs() {
const quotaFile = SpreadsheetApp.openById('AAA');
const googleSheetTemplate = DriveApp.getFileById('BBB');
const destinationFolder = DriveApp.getFolderById('CCC');
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('employeedata');
const rows = sheet.getDataRange().getValues();
rows.forEach(function(row, index) {
if (index === 0) return;
if (row[3]) return;
const employeeFirstName = row[0];
const employeeLastName = row[1];
const department = row[2];
const copy = googleSheetTemplate.makeCopy(`${employeeFirstName} ${employeeLastName} / EmployeeSummary`, destinationFolder);
const doc = SpreadsheetApp.open(copy);
const targetSheet = doc.getSheets()[0];
const employeeDataRange = quotaFile.getSheetByName('Employees').getRange(index, 3, 1, 3);
const employeeData = employeeDataRange.getValues();
targetSheet.getRange('B2').setValue(`${employeeFirstName} ${employeeLastName}`);
targetSheet.getRange('B3').setValue(department);
targetSheet.getRange('B4').setValue(employeeData[0][0]);
targetSheet.getRange('B5').setValue(employeeData[0][1]);
targetSheet.getRange('B6').setValue(employeeData[0][2]);
});
}
Generated custom spreadsheet
Field | Value |
---|---|
Name | Alice Johnson |
Department | Sales |
Data 1 | Data 1 |
Data 2 | Data 2 |
Data 3 | Data 3 |
Employees sheet
Employee ID | Employee Name | Data 1 | Data 2 | Data 3 |
---|---|---|---|---|
101 | Alice | 10 | 20 | 30 |
102 | Bob | 15 | 25 | 35 |
103 | Charlie | 20 | 30 | 40 |
Employeedata Sheet
First Name | Last Name | Department |
---|---|---|
Alice | Johnson | Sales |
Bob | Smith | Marketing |
Charlie | Brown | HR |