Pull Data From Multiple Ranges on One Spreadsheet to Another Spreadsheet

121 views Asked by At

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.

0

There are 0 answers