I currently have two spreadsheets in my possession. The first spreadsheet consists of raw data that includes unique employee numbers and the names of the employees. The second spreadsheet is the one I aim to duplicate to a designated Google Drive folder. My goal is to update specific fields within the second spreadsheet based on the corresponding employee number and name provided in the first spreadsheet. Each time these changes are made, a new copy of the updated second spreadsheet will be generated in the Google Drive folder.
However, there seems to be an issue with only one value being set in the replicated spreadsheets. It does not iterate through the list of employee names and numbers present in the first spreadsheet as intended.
While my current code successfully replicates the second spreadsheet, it fails to properly update the values within the duplicated sheets.
function replicateCards() {
var ss = SpreadsheetApp.openById('xxxxxxxx');
var copyCard = SpreadsheetApp.openById('zzzzzzzzz');
var getID = DriveApp.getFileById(copyCard.getId())
var card = copyCard.getSheetByName("Card");
var mastersheet = ss.getSheetByName("Mastersheet");
var getLastRow = mastersheet.getLastRow();
var destinationFolder = DriveApp.getFolderById('yyyyyyyyyy');
;
var changeColorToGrayList = card.getRangeList(['C7', 'E7', 'G7', 'I7', 'K7', 'M7', 'O7', 'Q7',
'C9', 'E9', 'G9', 'I9', 'K9', 'M9', 'O9', 'Q9',
'C11', 'E11', 'G11', 'I11', 'K11', 'M11', 'O11', 'Q11']);
var setValueToZero = card.getRangeList(['C8', 'E8', 'G8', 'I8', 'K8', 'M8', 'O8', 'Q8',
'C10', 'E10', 'G10', 'I10', 'K10', 'M10', 'O10', 'Q10',
'C12', 'E12', 'G12', 'I12', 'K12', 'M12', 'O12', 'Q12']);
for (i = 1; i < getLastRow; i++) {
var employeeNumber = mastersheet.getRange(i + 1, 1).getValue();
var employeeName = mastersheet.getRange(i + 1, 2).getValue();
card.getRange("C3").setValue(employeeName);
card.getRange("H3").setValue(employeeNumber);
card.setActiveRangeList(changeColorToGrayList).setBackground("gray");
card.setActiveRangeList(setValueToZero).setValue(0);
// var getID = DriveApp.getFileById(card).getId();
getID.makeCopy(employeeNumber + " High Flyer Card", destinationFolder);
}
}
My expectation is that the output of
getID.makeCopy(employeeNumber + " High Flyer Card", destinationFolder);