google-apps-script

Creating new sheet by ID but new sheet is stuck on pulling 1 row


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!


Solution

  • Generate custom sheets for each employee

    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

    Modified sheet

    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]);
      });
    }
    

    Sample output

    Sample

    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

    Reference

    getRange