I'm trying to add data from a source sheet with the data in three columns.
The first column contains data I'm using as the file names for a template I'm copying to another folder. I've accomplished this task.
However, the second and third columns contain data which I want to copy to the newly created files from the data in column one, into a sheet named 'Data' and put the values into F2
and F3
respectively.
The issue is that I've tested the script with two sets of data but only the data in the first spreadsheet is correct. The second spreadsheet gets the correct filename but the data going in F2
and F3
in the Data sheet remains the same as the data in the first spreadsheet.
I can't get the script to pull the next set of data from Row 2 in Columns 2 and 3
in the source sheet and apply it to F2
and F3
respectively.
I'm thinking I have to do another loop but don't know how to accomplish it.
Please help.
function createCopy()
{
let file = DriveApp.getFileById("1lui7ipNvqBuMOsQH4WX8sqFzbg9pJulFmcZGt1v8EXo"); // ID of the file you want to copy
let sheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1sbJUzI8hCB5tyK7gKuL4YPB2CCRBgctwNOWFib6ptlQ/edit?usp=drive_link').getSheetByName("Sheet1"); // URL and sheet name of the spreadsheet where the roster is.
let range = sheet.getRange('A1:A' + sheet.getLastRow()); // Range where the roster data is located
let rangeb = sheet.getRange('B1:B' + sheet.getLastRow()); // Range where the Data F2 entries are located
let rangec = sheet.getRange('C1:C' + sheet.getLastRow()); // Range where the Data F3 entries are located
let values = range.getValues();
let valuesb = rangeb.getValue();
let valuesc = rangec.getValue();
let folder = DriveApp.getFolderById("1r8RkK7dpmY_6qakFBAhG3aAso_TUPlRi"); // ID of the destination folder
for(let i=0; i<values.length; i++)
{
var fileid = file.makeCopy(values[i].toString(), folder).getId();
// id of the spreadsheet to add permission to import
var ssId = fileid;
var spreadsheet = SpreadsheetApp.openById(ssId).getSheetByName("Data");
spreadsheet.getRange('F2').setValue(valuesb);
spreadsheet.getRange('F3').setValue(valuesc);
}
};
I tried adding for loops to get the valuesb and valuesc to change but to no avail.