CODEWITHSUNDEEP
google-apps-script
deL
deL

Reputation: 91

Copying Rows of Data to Different Sheets

I am new to coding/JavaScript, so I apologize in advance if this question is (unintentionally) poorly phrased. I've been searching around and found some similar questions but nothing to solve my issue.

I have a scenario where I have a Workbook with one sheet called "Data" containing records and a column header. There is another sheet called "Template" containing a fixed template. I need to duplicate the Template for the number of records in Data (i.e. 5 records of Data will create 4 copies of the original Template). Next, I need to add the first record in Data to a specific cell range in the original Template, the second record in Data to the same specific cell range in the first Template copy, etc.

I've gotten my script to where I can duplicate the Template sheet and pull all the records into an array, but I haven't been able to figure out how to set the first record to the original Template, the second record to the second Template, etc. Any help is greatly appreciated!

function duplicate() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');

var startRow = 2; //first row
var lastRow = sheet.getLastRow();

var StartCol = 1
var LastCol = sheet.getLastColumn()

var numCol = sheet.getLastColumn();
var numRows = sheet.getLastRow(); // Number of rows to process

var dataRange = sheet.getRange(startRow, StartCol, numRows, numCol);
var data = dataRange.getValues(); 

//Duplicate Sheet based on # of Records 
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName("Template"); 
for (i=1;i<numRows;++i) s.copyTo(ss).setName("Template "+i);

//A bad attempt at trying to add the records to each template copy
var nextSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Template "+i);
nextSheet.getRange(lastRow+1,1,numRows,numCol).setValues(data[0+1]);
}

'

Upvotes: 2

Views: 143

Answers (2)

random-parts
random-parts

Reputation: 2225

If I understand correctly, you would like each data record[row] to have its own sheet, based on a Template, with the single record inserted into the same cell range for each record's individual sheet?

function duplicate() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Data");
  // Get the Template sheet
  var template = ss.getSheetByName("Template"); 

  var startRow = 2; //first row
  var lastRow = sheet.getLastRow();

  var StartCol = 1;
  var LastCol = sheet.getLastColumn();

  var numCol = sheet.getLastColumn();
  var numRows = sheet.getLastRow(); // Number of rows to process

  var dataRange = sheet.getRange(startRow, StartCol, numRows, numCol);
  var data = dataRange.getValues(); 

  // The constant single column range to insert the data record into
  var data_insert_range = sheet.getRange("C14:C22").getA1Notation();

  // For each data record create a new sheet based on the template
  // and insert into a pre-set range
  data.forEach(function (e,i) {
    var nextSheet = ss.insertSheet("Template Copy " + i, {template: template});
    // Map each col of the record into it's own row
    var dataRowIntoCol = e.map(function (c) { return [c] });
    // Insert record into the desired column
    nextSheet.getRange(data_insert_range).setValues(dataRowIntoCol);
  });
}

Instead of using the template to both copy from and overwriting data, I would suggest keeping the Template pure and create new sheets for writing data from it. If not, just place your copyTo() back in place of the insertSheet() and it will work that way as well

edit: Updated code to reflect clarified range/data information

Upvotes: 1

Cooper
Cooper

Reputation: 64110

I wasn't sure exactly what you wanted but I think this will give you a boost in a positive direction.

function duplicate() {

var ss=SpreadsheetApp.getActiveSpreadsheet()
var sheet=ss.getSheetByName('Data');

var startRow = 2; //first row
var lastRow = sheet.getLastRow();

var StartCol = 1
var LastCol = sheet.getLastColumn()

var numCol = sheet.getLastColumn();
var numRows = sheet.getLastRow()-startRow+1; // Number of rows to process

var dataRange = sheet.getRange(startRow, StartCol, numRows, numCol);
var data = dataRange.getValues(); 

//Duplicate Sheet based on # of Records 

var s = ss.getSheetByName("Template"); 
for (i=0;i<numRows;++i) 
{
  s.copyTo(ss).setName("Template " + Number(i+1));
  s.getRange(startRow,startCol,numRows,numCol).setValues(data);
}
}

Upvotes: 1

Related Questions